一、窗口函数是什么
窗口函数不会进行聚合，将多条记录按照分组字段聚合成一条记录。而是通过移动窗口，对每一条记录进行窗口内的计算。
以下通过示例来理解不同类型的窗口函数。
二、示例表
假设我们拥有一张用户页面浏览统计表user_page_pv，表结构如下。
| 字段名 | 字段类型 | 字段说明 |
|---|---|---|
| imp_date | STRING | 日期 |
| page_id | STRING | 页面名/页面id |
| user_id | STRING | 用户id |
| pv | BIGINT | 浏览次数(页面曝光次数) |
三、常见窗口函数的使用方法及实例说明
1.序号函数:row_number/rank/dense_rank
区别
- ROW_NUMBER:排序字段的值相同时，按顺序+1分配序号
- RANK:排序字段的值相同时，分配相同的序号，并且下一批相同排序的值，按照首批内首行的行号分配序号。
- 与RANK 相似，相同排序字段分配相同的排序号。但排序序号是连续的：1,2,3...etc。
示例:计算20200707日各页面浏览pv排名，要求各页面序号不同。
--
SELECT
page_id
,ROW_NUMBER() OVER(PARTITION BY page_id ORDER BY pv DESC) pv_rank
FROM
(
SELECT page_id, SUM(pv) page_pv
FROM user_page_pv
WHERE imp_date = '20200707'
GROUP BY page_id
) t
2.聚合函数:SUM/AVG/MIN/MAX
与普通聚合函数不同，窗口函数每行都会返回值
2.1 frame 子句:用于定义窗口数据中的一个数据集，关键词如下。
rows between
preceding
following
current row
unbounded
如果不使用ROWS BETWEEN 关键词及其之后的起止行，那么默认从初始行(UNBOUNEDE PRECEDING)到目前行（CURRENT ROW)
2.2 实例：计算20200707当日全部page_id的人均访问次数,追加在各页面的pv后
SELECT
page_id
,pv
,AVG(pv) OVER(
PARTITION BY page_id ORDER BY reporttime --WINDOW自语句
ROWS BETWEEN --FRAME子语句
UNBOUNDED/n PRECEDING
AND
UNBOUNDED/n FOLLOWING /CURRENT ROW
) AS page_avg_pv --各页面的访问pv均值
FROM
(
SELECT page_id, SUM(pv) page_pv
FROM user_page_pv
WHERE imp_date = '20200707'
GROUP BY page_id
) t
3.前后函数：LEAD(N)/LAG(N)
--上一次有点击的日期和点击数量(pv)
SELECT user_id, imp_date, pv
,LEAD(imp_date, 1) OVER (PARTITION BY userid ORDER BY imp_date) last_impdate
,LEAD(pv, 1) OVER (PARTITION BY userid ORDER BY imp_date) last_pv
FROM table
4.头尾函数：first_value(expr)/last_value(expr)
--今年上半年的首次点击的时间以及首次点击时的点击次数PV
SELECT user_id
,imp_date --当日日期
,pv pv_cur_day --当日点击pv
,FIRST_VALUE(imp_date) OVER (PARTITION BY userid ORDER BY imp_date) first_date
,FIRST_VALUE(pv) OVER (PARTITION BY userid ORDER BY imp_date) first_pv
FROM table
WHERE imp_date BETWEEN 20200101 AND 20200631
5.指定序号值函数：nth_value(expr, n)
6.NTILE 切片函数类似PANDAS.CUT
7.分布函数
percent_rank/cume_dist
percent_rank:分组内当前行的RANK值-1/分组内总行数-1
cume_dist:小于等于当前值的行数/分组内总行数
