hive窗口函数over()_hive over

hive窗口函数over()

over()，指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
根据尚硅谷大数据Hive教程，总结整理

（1）over() group by，给每一条数据都开全量窗口

原数据

需求：查询在 2017 年 4 月份购买过的顾客及总人数

• 1）查询在 2017 年 4 月份购买过的所有字段
  substring(string A, int start, int len)：从0开始，截取字符串
  
• 2）–查询在 2017 年 4 月份购买过的顾客
  
• 3）2017 年 4 月份购买过的总人数
  
  • 需求是，既要人名又要总数，
    （可以把两个sql查出的数据，做笛卡尔积，join，不靠谱）
    
    （该sql含义为，每个人在4月的购买次数，不是我们的需求人数，不符合。）
• 在上面的sql中，只加一个over()，就可以达到目的，over()在group by函数后生效。
  意思为，count(*)是在over()窗口中进行，over()窗口中没写东西，则 所有数据
  
• 又如：
  
  
  有点侧写表感觉，over给每一条数据都开窗，没写就是全量，第一行mart没写，14个；第二行neil写，14个；第三行mart没写，14个；groupby只有一个组，over就有14个组，只不过over没写参数每个组数据都是全量。

（2）over(partition by .)，分区分组窗口

需求:查询顾客的购买明细及月购买总额

需要在over中加参数，来限制窗口大小了。
加一个分组条件partition by name，(partition by name只能在over里用，用于分区分组）

• 1）查询顾客的购买明细及购买总额
• 
  
• 2）查询顾客的购买明细及月购买总额
• 
  
• 3）查询 月购买总额
• 
  

（3）over(partition by . order by . )，分区分组，开始行到当前行的窗口

需求：上述的场景, 将每个顾客的 cost 按照日期进行累加

• 使用 order by ，按照排序，每个窗口动态增加
• 也可以通过窗口指定命令，限定窗口大小；
  
  • 这些写在over()里面，是限定窗口大小的，用来限定当前分组的行数
    • CURRENT ROW：当前行
    • n PRECEDING：往前 n 行数据
    • n FOLLOWING：往后 n 行数据
    • UNBOUNDED：起点，
      • UNBOUNDED PRECEDING 表示从前面的起点，
      • UNBOUNDED FOLLOWING 表示到后面的终点
        
        order by 默认就是，开始行到当前行。
        
        
        与直接order by 效果一样
        又如：窗口限定，取前一行，后一行
        
        

（4）lag() over(partition by . order by . )，分区分组，开始行到当前行的窗口, 操作为上移下移几行

需求：查询每个顾客上次的购买时间

• 需要用到LAG、LEAD，这些是写在over前边的，（求用户的单跳转换率）
  • LAG(col,n,default_val)：往前第 n 行数据
  • LEAD(col,n, default_val)：往后第 n 行数据
    
    
• null时，给默认值：1970
  
  
• null时，给默认值：自己
  
  

（5）ntile(n) over(order by)，把有序窗口的行分发到指定数据的组中

需求：查询前 20%时间的订单信息

• ntile(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。
• ntile必须加上over，不能单独使用
  
  
  
  

（6）rank() over(partition by . order by . ) ，分组排序

如：3个人，两人并列第一

• RANK() 排序相同时会重复，总数不会变,1 、1 、 3
• DENSE_RANK() 排序相同时会重复，总数会减少，1、1、2
• ROW_NUMBER() 会根据顺序计算，1、2、3

原数据：

• 1） RANK() 排序相同时会重复，总数不会变,1 、1 、 3
  
• 2）DENSE_RANK() 排序相同时会重复，总数会减少，1、1、2
  
  
• 3）ROW_NUMBER() 会根据顺序计算，1、2、3
  
  
• 4）分组排序
  
  
• 5）每个学科的前三名