# hive row_number分组排序top

```select
*
,row_number() OVER(PARTITION BY t3.action ORDER BY pv desc) AS flag
from
(
select
action
,uuid
,count(1) as pv
from logtable t
group by t.action,uuid
)  t1
where t1.flag<=10```

rank,dense_rank,row_number区别

rank() over([partition by col1] order by col2)
dense_rank() over([partition by col1] order by col2)
row_number() over([partition by col1] order by col2)
其中[partition by col1]可省略。

三个分析函数都是按照col1分组内从1开始排序

row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页
dense_rank() 是连续排序，两个第二名仍然跟着第三名
rank()       是跳跃拍学，两个第二名下来就是第四名

理论就不多讲了，看了案例，一下就明白了

SQL> create table t(
2   name varchar2(10),
3   score number(3));

Table created

SQL> insert into t(name,score)
2   select '语文',60 from dual union all
3   select '语文',90 from dual union all
4   select '语文',80 from dual union all
5   select '语文',80 from dual union all
6   select '数学',67 from dual union all
7   select '数学',77 from dual union all
8   select '数学',78 from dual union all
9   select '数学',88 from dual union all
10   select '数学',99 from dual union all
11   select '语文',70 from dual
12  /

10 rows inserted

SQL> select * from t;

NAME       SCORE
---------- -----

10 rows selected

SQL> select name,score,rank() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------

10 rows selected

SQL> select name,score,dense_rank() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------

10 rows selected

SQL> select name,score,row_number() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------

10 rows selected

SQL> select name,score,rank() over(order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------

10 rows selected

select name,score from (select name,score,dense_rank() over(partition by name order by score desc) tt from t) x where x.tt<=3

NAME       SCORE
---------- -----

6 rows selected

select name,score,x.tt from (select name,score,rank() over(partition by name order by score desc) tt from t) x where x.name='语文' and x.score=70

NAME       SCORE         TT
---------- ----- ----------

select xx.* from (select t.*,row_number() over(order by score desc) rowno from t) xx where xx.rowno between 1 and 3;

NAME       SCORE      ROWNO
---------- ----- ----------

hive row_number分组排序top

• 0

开心

• 0

板砖

• 0

感动

• 0

有用

• 0

疑问

• 0

难过

• 0

无聊

• 0

震惊