WITH t1 as 
(select * from browserdata join citydata on cityid=id),

t2 as 
(select uap.device as device, uap.os as os, uap.browser as browser, name as cityname 
from t1 
lateral view ParseUserAgentUDTF(UserAgent) uap as device, os, browser),

t3 as
  (SELECT t2.cityname as cityname, t2.device as device, t2.browser as browser, t2.os as os, COUNT(*) as count FROM t2 GROUP BY t2.cityname, t2.os, t2.device, t2.browser),

t4 as
    (select cityname, MAX(count) as maximum from t3 group by cityname)

select t4.cityname, t4.maximum, t3.device, t3.os, t3.browser
from t4 join t3 on t4.cityname=t3.cityname and t4.maximum=t3.count;

这是可行的，但我想知道是否有一种方法来优化它。。。

使用分析函数可以消除不必要的连接：

WITH 
t1 as 
(select * from browserdata join citydata on cityid=id),

t2 as 
(select uap.device as device, uap.os as os, uap.browser as browser, name as cityname 
from t1 
lateral view ParseUserAgentUDTF(UserAgent) uap as device, os, browser),

t3 as
(select t2.cityname as cityname, t2.device as device, t2.browser as browser, t2.os as os, count(*) as count from t2 group by t2.cityname, t2.os, t2.device, t2.browser)

select cityname, maximum,  device, os, browser
 from
     (select cityname, device, browser, os, 
             max(count) over(partition by cityname)                         as maximum,
             dense_rank() over (partition by cityname order by count desc ) as rnk      
      from t3
     ) s  where rnk =1 
;