前言

百度式总结;普通的聚合函数聚合的行集是组，而开窗函数聚合的行集是窗口，因此，在实际的业务查询中，普通的聚合函数每组（group by）只有一个返回值，而开窗函数则可以为窗口中的每行都返回一个值。

开窗函数理论知识

基本语法

Function (arg1,……,argn) OVER ([partition by,<>] [order by <……>])
 [<window_expression>]
  • 说明

Function (arg1,…, argn) 可以是下面的函数：

  1. Aggregate Functions: 聚合函数,比如：sum(…)、 max(…)、min(…)、avg(…)等.
  2. Sort Functions: 数据排序函数, 比如 ：rank(…)、row_number(…)等.
  3. Analytics Functions: 统计和比较函数, 比如：lead(…)、lag(…)、
    first_value(…)等.

窗口排序函数

窗口排序可以提供排序信息 ，在业务中可以用到地方如最近的用户点击时间以计算相关的业务指标，其中常用的排序函数主要有：

  • ntile

语法：ntile(n) over([distribute/partition by …] sort/order by …)

具体使用可见Hive_Ntile

  • row_number

语法：row_number() over([distribute/partition by …] sort/order by …)
说明: 从1开始，按照顺序，生成分组内记录的序列

  • rank

语法：rank() over([distribute/partition by …] sort/order by …)
说明: 生成数据项在分组中的排名，排名相等会在名次中留下空位

  • dense_rank

语法：dense_rank() over([distribute/partition by …] sort/order by …)
说明: 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

  • percent_rank

语法：percent_rank() over([distribute/partition by …] sort/order by …)
说明：与 PERCENT 函数类似，PERCENT_RANK 函数为窗口的 ORDER BY 子句所指定列中的值返回秩，但以介于 0 和 1 之间的小数形式表示，
计算方法为(分组内当前行的RANK值-1)/(分组内总行数-1)

窗口分析函数

  • cunme_diast
  • lead(value_expt[_export])

如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。比如，统计小于等于当前工资的人数占总人数的比例 ，用于累计统计

  • first_value&last_value

取分组内排序后，截止到当前行，第一个&最后一个值。

具体练习实战可见：
Hive 开窗函数实战

业务运用实例子

如：业务方给出需求：希望要到近期某股热点的近期的订阅人数；

  • 需求拆分
    （1）需求表中利用date、time两个字段 按用户id取其记录的最近时间；
    （2）拆解清洗字段conent，得出想要的value。
  • 需求（1）部分的实现：
select * from（
 select id1
 ,id2
 ,row_number() over(partition by 用户id order by date desc ，time desc) rn
 from table
 ）
 where rn=1