Hive实现分组排序、分组求取topN或者分页的实现方法

浅浅的花香味﹌ 2022-03-15 04:18 501阅读 0赞

使用到的语法：**ROW\_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)**  
简单的说row\_number()从1开始，为每一条分组记录返回一个数字，这里的ROW\_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的每条xlh记录返回一个序号。  
表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)实例.

语法：row\_number() over (partition by 字段a order by 计算项b desc ) rank

\--这里rank是别名

partition by：类似hive的建表，分区的意思；

order by ：排序，默认是升序，加desc降序；

这里按字段a分区，对计算项b进行降序排序

## 实例一、 ##

## 要取top10品牌，各品牌的top10渠道，各品牌的top10渠道中各渠道的top10档期  ##

1、取top10品牌

> select 品牌,sum(income) as num  from **table\_name** group by 品牌 order by num desc limit 10;

2、 取top10品牌下各品牌的top10渠道

> select 
> 
>           a.\*
> 
> from
> 
>           (
> 
>                   select 品牌,渠道,sum(income) as num, row\_number() over (partition by 品牌 order by num desc ) rank  
> 
>                    from table\_name
> 
>                    where 品牌限制条件
> 
>                    group by 品牌,渠道
> 
>           )a
> 
> where 
> 
>           a.rank<=10

3、 取top10品牌下各品牌的top10渠道中各渠道的top10档期

> select 
> 
>           a.\*
> 
> from
> 
>           (
> 
>                   select 品牌,渠道,档期,sum(income) as num, row\_number() over (partition by 品牌,渠道 order by num desc ) rank  
> 
>                    from table\_name
> 
>                    where 品牌,渠道 限制条件
> 
>                    group by 品牌,渠道,档期
> 
>           )a
> 
> where 
> 
>           a.rank<=10

## 实例二： ##

## **初始化数据:** ##

> create table employee (empid int ,deptid int ,salary decimal(10,2));  
> insert into employee values(1,10,5500.00);  
> insert into employee values(2,10,4500.00);  
> insert into employee values(3,20,1900.00);  
> insert into employee values(4,20,4800.00);  
> insert into employee values(5,40,6500.00);  
> insert into employee values(6,40,14500.00);  
> insert into employee values(7,40,44500.00);  
> insert into employee values(8,50,6500.00);  
> insert into employee values(9,50,7500.00);

显示数据：

> hive> select \* from employee;  
> 1    10    5500.00  
> 2    10    4500.00  
> 3    20    1900.00  
> 4    20    4800.00  
> 5    40    6500.00  
> 6    40    14500.00  
> 7    40    44500.00  
> 8    50    6500.00  
> 9    50    7500.00  
> Time taken: 4.078 seconds, Fetched: 9 row(s)  
> hive> desc employee;  
> empid                   int  
> deptid                  int  
> salary                  decimal(10,2)  
> Time taken: 0.042 seconds, Fetched: 3 row(s)

### （1）需求：根据部门分组，显示每个部门的工资等级   ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70][] ###

### 实现的HSQL脚本： ###

> ### hive> SELECT \*, row\_number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee; ###
> 
> ### OK   1    10    5500.00    1   2    10    4500.00    2   4    20    4800.00    1   3    20    1900.00    2   7    40    44500.00    1   6    40    14500.00    2   5    40    6500.00    3   9    50    7500.00    1   8    50    6500.00    2   Time taken: 25.019 seconds, Fetched: 9 row(s) ###

### （2）根据部门分组，求出每个部门的工资等级排名top 2的数据 ###

> ### SELECT       \*   FROM       (           SELECT               \*, row\_number() OVER(PARTITION BY deptid                   ORDER BY salary DESC               ) rank           FROM               employee       ) a   WHERE       a.rank <= 2; ###

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 1][] ###

### （3）根据部门分组，求出每个部门的工资等级排第二名的那条数据 ###

> ### SELECT       \*   FROM       (           SELECT               \*, row\_number() OVER(PARTITION BY deptid                   ORDER BY salary DESC               ) rank           FROM               employee       ) a   WHERE       a.rank = 2; ###

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 2][] ###

### 按照empid降序排列，并按照排列顺序给出编号： ###

> ### SELECT       row\_number() over(ORDER BY empid DESC) AS rnum ,       employee.\*   FROM       employee ###

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 3][] ###

### （4）hive中一般取top n时，row\_number(),rank,dense\_ran()这三个函数就派上用场了， ###

先简单说下这三函数都是排名的，不过呢还有点细微的区别。

通过代码运行结果一看就明白了。

除Row\_number外还有rank,dense\_rank   
以下是语法：   
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)

功能差不多，但是有细微的差别

**row\_number的排序不允许并列，即使两条记录的值相等也不会出现相等的排序值   
rank排序时出现相等的值时会有并列，即值相等的两条数据会有相同的序列值   
dense\_rank排序的值允许并列，但会跳跃的排序，像这样：1,1,3,4,5,5,7.**

>   
> select empid,  
> deptid,  
> salary,  
> rank()over( order by deptid desc ) rank,  
> dense\_rank() over( order by deptid desc ) dense\_rank,  
> row\_number()over( order by deptid desc) row\_number  
> from employee

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 4][]

从结果看出

rank() 排序相同时会重复，总数不会变

dense\_rank()排序相同时会重复，总数会减少

row\_number() 会根据顺序计算

正好听到一个需求，求sal前50%的人

### （5）基于row\_number函数也很容易实现分页： ###

> ### SELECT       \*   FROM       (           SELECT               row\_number() over(ORDER BY empid DESC) AS rnum ,               employee.\*           FROM               employee       ) t   WHERE       rnum >= 1   AND rnum <= 5; ###

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 5][] ###

参考：[https://www.2cto.com/net/201803/733548.html][https_www.2cto.com_net_201803_733548.html]

[http://blog.sina.com.cn/s/blog\_6676d74d0102vm2c.html][http_blog.sina.com.cn_s_blog_6676d74d0102vm2c.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70]: /images/20220315/1deb9621932b4121bd7832109ebf6cfd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 1]: /images/20220315/4ec4cf2a2d394014b20198b25d7cae60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 2]: /images/20220315/63637a719a5a4afca60d20ed088066f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 3]: /images/20220315/d8c2ae44e2c549e9900e393a5c9ef4d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 4]: /images/20220315/6d91cb2db83649a49f9d466106a9e82e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 5]: /images/20220315/2d66e294db304bf595147116573aac17.png
[https_www.2cto.com_net_201803_733548.html]: https://www.2cto.com/net/201803/733548.html
[http_blog.sina.com.cn_s_blog_6676d74d0102vm2c.html]: http://blog.sina.com.cn/s/blog_6676d74d0102vm2c.html