题目要求,一张表,两个字段,分别是城市,关键字,查找出每个城市关键词的前五。
分析:
1.首先要求的每个维度是城市+关键字 -> 根据城市和关键字分组求出数量
2.要找到前五输出,想到ROW_NUMBER() 开窗函数
实现如下:
select * from (select city,keyss,count(*),row_number() over(partition by city order by count(*) desc) ck from tab group by city,keyss) where ck<6 |
注意:如果直接将row_number函数写在最外边的条件中,别名标签会失效
错误示例:
select city,keyss,row_number() over(partition by city order by c desc) ck from (select city,keyss,count(*)as c from tab group by city,keyss) t where ck <3; FAILED: SemanticException [Error 10004]: Line 1:156 Invalid table alias or column reference 'ck': (possible column names are: city, keyss, c) |
(本答案不唯一,上述为一种简单的HQL实现)