hive 分组 top10 sql简化实现方法

SELECT cityname ,cnt 
FROM (
SELECT 
 cityname 
,COUNT(1) AS cnt 
,row_number() over(ORDER BY COUNT(1) DESC ) AS rn 
FROM tmp_thd_user_list 
GROUP BY cityname 
) t 
WHERE rn <= 10 

 

好多报表都要去top10展示，在写sql的时候发现需要一层套一层，一层套一层，至少需要套三层以上；然后就在想，这个sql能不能精简一下？看着更加清晰一些呢？
我们来看一个简单的例子，表数据如下：

需求是看城市用户数的top10
按正常的思路，是先按城市分组统计人数，然后用开窗函数做排名，再然后取排名前10。
通过这个思路写出的sql

SELECT 
     cityname 
    ,cnt 
FROM (
	SELECT 
		 cityname 
		,cnt 
		,row_number() over(ORDER BY cnt DESC ) AS rn 
	FROM (
		SELECT cityname ,COUNT(1) AS cnt 
		FROM tmp_thd_user_list 
		GROUP BY cityname 
	) t 
) tt 
WHERE rn <= 10 
;

运行结果：

这里的sql套了2层，如果实际工作中遇到去重、数据过滤等等之类的各种情况，至少要套3层以上，那这里考虑如何进行代码优化呢？
关键点是在分组的时候直接用开窗函数：

SELECT cityname ,cnt 
FROM (
SELECT 
 cityname 
,COUNT(1) AS cnt 
,row_number() over(ORDER BY COUNT(1) DESC ) AS rn 
FROM tmp_thd_user_list 
GROUP BY cityname 
) t 
WHERE rn <= 10 

这里的例子看上去很简单，其中的亮点是在分组的时候直接运用开窗函数。