hive-窗口函数入门到精通

• LEAD 分区的数据往前默认是往前一位

• LAG 分区后的数据往后移动 

• FIRST_VALUE 取第一个值the second (optional) parameter must be a boolean which is false by default. If set to true it skips null values.如果第一个值是null那么如果是true那么就是跳过空值。

• LAST_VALUE  最后的一个值the second (optional) parameter must be a boolean which is false by default. If set to true it skips null values.如果第二个数字是true那么就是，那么就是跳过空值

•  设置窗口的大小

• (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
  (ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
  (ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

• UNBOUNDED PRECEDING是窗口的上边界
• [num] PRECEDING 是窗口的前几条或者是后几条
• CURRENT ROW 是当前的行
• UNBOUNDED FOLLOWING 是下边界
• [num] FOLLOWING 是下面几条

• ORDER BY 如果没有指定窗口那么就是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

•  When both ORDER BY and WINDOW clauses are missing, the WINDOW specification defaults to ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

如果不指定order by 和窗口那么就是上边界和下边界

• 使用 RANK

• 使用ROW_NUMBER

• 使用 DENSE_RANK

• 使用 CUME_DIST 

 

• NTILE 
•  

 PERCENT_RANK 计算方法为 (RANK - 1)/(N- 1)