HiveSQL题——排序函数(row_number/rank/dense_rank)

一、窗口函数的知识点

1.1 窗户函数的定义

        窗口函数可以拆分为【窗口+函数】。窗口函数官网指路：
LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundation

https://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics

• 窗口：限定函数的盘算范围（窗口函数：partition by 分组后，从逻辑角度指定组内盘算范围，并没有从物理上真正的切分，只有group by 是物理分组，真正意义上的分组）
  
• 函数：定义函数盘算逻辑
  
•  窗口函数的位置：跟sql里面聚合函数的位置一样，from -> join -> on -> where -> group by->select 后面的平凡字段，窗口函数 -> having -> order by  -> lmit 。 窗口函数不能跟聚合函数同时出现。聚合函数包括count、sum、 min、max、avg。
  
• sql 执行顺序：
  

1. from ->
2. join ->
3. on ->
4. where ->
5. group by->
6. with (可以在分组后面加上 with rollup,在分组之后对每个组进行全局汇总) ->
7. select 后面的普通字段，聚合函数->
8. having(having中可以使用select 字段别名) ->
9. distinct ->
10. order by ->
11. limit

复制代码

• 窗口函数执行顺序：窗口函数是作用于select后的结果集。select 的结果集作为窗口函数的输入，但是位于 distcint 之前。窗口函数的执行结果只是在原有的列中单独添加一列，形成新的列，它不会对已有的行或列做修改。
  

1.2 窗户函数的语法

       <窗口函数>window_name  over ( [partition by 字段...]  [order by 字段...]  [窗口子句] )

• window_name：给窗口指定一个别名。
  
• over：用来指定函数执行的窗口范围，如果后面括号中什么都不写，即over() ，意味着窗口包罗满意where 条件的所有行，窗口函数基于所有行进行盘算。
  
• 符号[] 代表：可选项；  | ： 代表二选一
  
•  partition by 子句： 窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行。分组间相互独立。
  
• order by 子句：每个partition内部按照哪些字段进行排序，如果没有partition ，那就直接按照最大的窗口排序，且默认是按照升序（asc）排列。
  
• 窗口子句：显示声明范围（不写窗口子句的话，会有默认值）。常用的窗口子句如下：
  

1.     rows between unbounded preceding and  unbounded following; -- 上无边界到下无边界（一般用于求 总和）
2.     rows between unbounded preceding and current row;  --上无边界到当前记录(累计值)
3.     rows between 1 preceding and current row; --从上一行到当前行
4.     rows between 1 preceding and 1 following; --从上一行到下一行
5.     rows between current row and 1 following; --从当前行到下一行

复制代码

    ps: over()里面有order by子句，但没有窗口子句时 ，即： <窗口函数> over ( partition by 字段... order by 字段... )，此时窗口子句是有默认值的---->   rows between unbounded preceding and current row (上无边界到当前行)。
      此时窗口函数语法：<窗口函数> over ( partition by 字段... order by 字段... ) 等价于
     <窗口函数> over ( partition by 字段... order by 字段... rows between unbounded preceding and current row)
      需要留意有个特殊情况：当order by 后面跟的某个字段是有重复行的时候， <窗口函数> over ( partition by 字段... order by 字段... )  不写窗口子句的情况下，窗口子句的默认值是：range between unbounded preceding and current row(上无边界到当前相同行的最后一行)。
    因此，碰到order by 后面跟的某个字段出现重复行，且需要盘算【上无边界到当前行】，那就需要手动指定窗口子句 rows between unbounded preceding and current row ，偷懒省略窗口子句会出问题~
 总结如下：

1. 1、窗口子句不能单独出现，必须有order by子句时才能出现。
2. 2、当省略窗口子句时:
3.    a) 如果存在order by则默认的窗口是unbounded preceding and current row  --当前组的第一行到当前行，即在当前组中，第一行到当前行
4.    b) 如果没有order by则默认的窗口是unbounded preceding and unbounded following  --整个组

复制代码

      ps: 窗口函数的执行顺序是在where之后，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行。
     【比方】求出登录记载出现间断的用户Id

1. select
2.     id
3. from (
4.          select
5.              id,
6.              login_date,
7.              lead(login_date, 1, '9999-12-31')
8.                   over (partition by id order by login_date) next_login_date
9.              --窗口函数 lead(向后取n行)
10.              --lead(column1,n,default)over(partition by column2 order by column3) 查询当前行的后边第n行数据，如果没有就为null
11.          from (--用户在同一天可能登录多次，需要去重
12.                   select
13.                       id,
14.                       date_format(`date`, 'yyyy-MM-dd') as login_date
15.                   from user_log
16.                   group by id, date_format(`date`, 'yyyy-MM-dd')
17.               ) tmp1
18.      ) tmp2
19. where  datediff(next_login_date, login_date) >=2
20. group by id;

复制代码

1.3 窗口函数分类

      哪些函数可以是窗口函数呢？(放在over关键字前面的)

• 聚合函数
  

1. sum(column) over (partition by .. order by .. 窗口子句);
2. count(column) over (partition by .. order by .. 窗口子句);
3. max(column) over  (partition by .. order by .. 窗口子句);
4. min(column) over (partition by .. order by .. 窗口子句);
5. avg(column) over (partition by .. order by .. 窗口子句);
6. collect_list (column) over (partition by .. order by .. 窗口子句);
7. collect_set (column) over (partition by .. order by .. 窗口子句);

复制代码

   需要留意：

1. 1.count(*)操作时会统计null值，count(column)会过滤掉null值;
2. 2.事实上除了count(*)计算，剩余的聚合函数例如： max(column),min(column),avg(column),count(column) 函数会过滤掉null值

复制代码

ps : 高级聚合函数：
          collect_list 网络并形成list集合，结果不去重;
          collect_set 网络并形成set集合，结果去重; 
      举例：

1. --每个月的入职人数以及姓名
3. select
4. month(replace(hiredate,'/','-')),
5.     count(*) as cnt,
6.     collect_list(name) as name_list
7. from employee
8. group by month(replace(hiredate,'/','-'));
11. /*
12. 输出结果
13. month  cn  name_list
14. 4            2        ["宋青书","周芷若"]
15. 6            1        ["黄蓉"]
16. 7            1        ["郭靖"]
17. 8            2        ["张无忌","杨过"]
18. 9            2        ["赵敏","小龙女"]
19. */

复制代码

• 排序函数
  

      row_number() 、rank()、dense_rank() 函数不支持自定义窗口子句。

1. --  顺序排序——1、2、3
2. row_number() over(partition by .. order by .. )
4. --  并列排序，跳过重复序号——1、1、3（横向加）
5. rank() over(partition by .. order by .. )
7. -- 并列排序，不跳过重复序号——1、1、2（纵向加）
8. dense_rank()  over(partition by .. order by .. )

复制代码

• 前后函数 
  

       lag和lead函数不支持自定义窗口子句。

1. -- 取得column列的前n行，如果存在则返回，如果不存在，返回默认值default
2. lag(column,n,default) over(partition by.. order by...) as lag_test
3. -- 取得column列的后n行，如果存在则返回，如果不存在，返回默认值default
4. lead(column,n,default) over(partition by.. order by...) as lead_test

复制代码

• 头尾函数
  

1. first_value(column,true)  ---当前窗口column列的第一个数值，如果有null值，则跳过
2. first_value(column,false) ---当前窗口column列的第一个数值，如果有null值，不跳过
3. last_value(column,true)  --- 当前窗口column列的最后一个数值，如果有null值，则跳过
4. last_value(column,false) --- 当前窗口column列的最后一个数值，如果有null值,不跳过

复制代码

1.4 排序函数

        rank/dense_rank/row_number 函数，一样寻常用于求分组topN。

1. --  顺序排序——1、2、3
2. row_number() over(partition by .. order by .. )
4. --  并列排序，跳过重复序号——1、1、3（横向加）
5. rank() over(partition by .. order by .. )
7. -- 并列排序，不跳过重复序号——1、1、2（纵向加）
8. dense_rank()  over(partition by .. order by .. )

复制代码

二、实际案例

2.1 每个学生结果第二高的科目

0 问题描述

    根据学生结果表，求出每个学生结果第二高的科目。
1 数据准备

1. create table if not exists table5
2. (
3.     class     string comment '学科',
4.     student   string comment '学生姓名',
5.     score     int comment '成绩'
6. )
7.     comment '学生成绩表';
9. insert overwrite table table5 values
10. ('a','吱吱1',100),
11. ('a','吱吱2',60),
12. ('b','吱吱1',80),
13. ('b','吱吱2',70),
14. ('c','吱吱2',50),
15. ('c','吱吱3',90);

复制代码

2 数据分析

1. 3种排序函数的区别：
3.   row_number （行号）-- 1 2 3 ;
5.   rank （重复跳过）--1 1 3;
7.  dense_rank （重复不跳过） --1 1 2

复制代码

1. select
2.     class,
3.     student
4. from (
5.          select
6.              class,
7.              student,
8.              score,
9.              dense_rank()  over (partition by student order by score desc) rn
10.          from table5
11.      ) tmp1
12. where rn = 2;

复制代码

3 小结

    排序函数在分组tpoN场景应用非常广泛，需要留意的是在sql语句中，窗口函数的执行顺序是在where过滤条件之后，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行。
2.2 销售排名第二的所有商品

0 问题描述

    查询订单明细表（order_detail)中销量（下单件数)排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。

1 数据准备

1. create table if not exists  table19
2. (
3.     order_detail_id    string comment '订单明细id',
4.     order_id  string comment '订单id',
5.     sku_id  string comment '商品id',
6.     create_date  string comment '商品的下单日期',
7.     price  double comment '商品单价',
8.     sku_num  int comment '商品件数'
9. ) comment  '订单明细表';
11. insert overwrite table table19 values
12. ('1','1','1','2021-09-30',2000.00,2),
13. ('2','1','3','2021-09-30',5000.00,5),
14. ('22','10','4','2020-10-02',6000.00,1),
15. ('23','10','5','2020-10-02',500.00,24),
16. ('24','10','6','2020-10-02',2000.00,5);

复制代码

2 数据分析

1. with total_sku as (
2.     select
3.         sku_id,
4.         sum(sku_num) total_nums
5.     from table19
6.     group by sku_id
7. ),
8.      rank_order as (
9.          select
10.              sku_id,
11.              dense_rank() over (order by total_nums desc) dr
12.          from total_sku
13.      )
14. select
15.     sku_id
16. from rank_order
17. where dr = 2;

复制代码

3 小结

  在写hql的时候，可以通过多段的with as 语句，使得整体的代码块布局清晰，易理解。



免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。