窗口函数详细用法

窗口函数与分析函数

应用场景：
（1）用于分区排序
（2）动态Group By
（3）Top N
（4）累计计算
（5）层次查询

窗口函数
FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
LAST_VALUE：取分组内排序后，截止到当前行，最后一个值
LEAD(col,n,DEFAULT) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG(col,n,DEFAULT) ：与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

OVER从句
1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
2、使用PARTITION BY语句，使用一个或者多个原始数据类型的列
3、使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者排序列
4、使用窗口规范，窗口规范支持以下格式：

(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

当ORDER BY后面缺少窗口从句条件，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

当ORDER BY和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

OVER从句支持以下函数，但是并不支持和窗口一起使用它们。
Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank.
Lead 和 Lag 函数。

分析函数
ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列,比如，按照pv降序排列，生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
CUME_DIST 小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例
PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。

Hive2.1.0及以后支持Distinct
在聚合函数（SUM, COUNT and AVG）中，支持distinct，但是在ORDER BY 或者窗口限制不支持。

COUNT(DISTINCT a) OVER (PARTITION BY c)

Hive 2.2.0中在使用ORDER BY和窗口限制时支持distinct

COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

Hive2.1.0及以后支持在OVER从句中支持聚合函数

SELECT rank() OVER (ORDER BY sum(b)) FROM T GROUP BY a;

测试数据集：

## COUNT、SUM、MIN、MAX、AVG select user_id,
 user_type,
 sales, --默认为从起点到当前行 sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc) AS sales_1, --从起点到当前行，结果与sales_1不同。 sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sales_2, --当前行+往前3行 sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS sales_3, --当前行+往前3行+往后1行 sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS sales_4, --当前行+往后所有行  sum(sales) OVER(PARTITION BY user_type ORDER BY sales asc ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sales_5, --分组内所有行 SUM(sales) OVER(PARTITION BY user_type) AS sales_6 from order_detail order by user_type,
 sales,
 user_id
 
+----------+------------+--------+----------+----------+----------+----------+----------+----------+--+ | user_id  | user_type  | sales  | sales_1  | sales_2  | sales_3  | sales_4  | sales_5  | sales_6  |
+----------+------------+--------+----------+----------+----------+----------+----------+----------+--+ | liiu     | new | 1 | 2 | 2 | 2 | 4 | 22 | 23 |
| qibaqiu  | new | 1 | 2 | 1 | 1 | 2 | 23 | 23 |
| zhangsa  | new | 2 | 4 | 4 | 4 | 7 | 21 | 23 |
| wanger   | new | 3 | 7 | 7 | 7 | 12 | 19 | 23 |
| lilisi   | new | 5 | 17 | 17 | 15 | 21 | 11 | 23 |
| qishili  | new | 5 | 17 | 12 | 11 | 16 | 16 | 23 |
| wutong   | new | 6 | 23 | 23 | 19 | 19 | 6 | 23 |
| lisi     | old | 1 | 1 | 1 | 1 | 3 | 6 | 6 |
| wangshi  | old | 2 | 3 | 3 | 3 | 6 | 5 | 6 |
| liwei    | old | 3 | 6 | 6 | 6 | 6 | 3 | 6 |
+----------+------------+--------+----------+----------+----------+----------+----------+----------+--+ 注意:
结果和ORDER BY相关,默认为升序
如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
 
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无界限（起点或终点）
UNBOUNDED PRECEDING：表示从前面的起点
UNBOUNDED FOLLOWING：表示到后面的终点
其他COUNT、AVG，MIN，MAX，和SUM用法一样。

## first_value与last_value select
 user_id,
 user_type,
 ROW_NUMBER() OVER(PARTITION BY user_type ORDER BY sales) AS row_num, 
 first_value(user_id) over (partition by user_type order by sales desc) as max_sales_user,
 first_value(user_id) over (partition by user_type order by sales asc) as min_sales_user,
 last_value(user_id) over (partition by user_type order by sales desc) as curr_last_min_user,
 last_value(user_id) over (partition by user_type order by sales asc) as curr_last_max_user
from
 order_detail;
 
+----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+ | user_id  | user_type | row_num  | max_sales_user | min_sales_user  | curr_last_min_user | curr_last_max_user  | +----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+ | wutong   | new | 7        | wutong | qibaqiu         | wutong | wutong              | | lilisi   | new | 6        | wutong | qibaqiu         | qishili | lilisi              | | qishili  | new | 5        | wutong | qibaqiu         | qishili | lilisi              | | wanger   | new | 4        | wutong | qibaqiu         | wanger | wanger              | | zhangsa  | new | 3        | wutong | qibaqiu         | zhangsa | zhangsa             | | liiu     | new | 2        | wutong | qibaqiu         | qibaqiu | liiu                | | qibaqiu  | new | 1        | wutong | qibaqiu         | qibaqiu | liiu                | | liwei    | old | 3        | liwei | lisi            | liwei | liwei               | | wangshi  | old | 2        | liwei | lisi            | wangshi | wangshi             | | lisi     | old | 1        | liwei | lisi            | lisi | lisi                | +----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+ ## lead与lag select
 user_id,device_id,
 lead(device_id) over (order by sales) as default_after_one_line,
 lag(device_id) over (order by sales) as default_before_one_line,
 lead(device_id,2) over (order by sales) as after_two_line,
 lag(device_id,2,'abc') over (order by sales) as before_two_line
from
 order_detail;
 
+----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+ | user_id  | device_id | default_after_one_line  | default_before_one_line | after_two_line  | before_two_line |
+----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+
| qibaqiu | fds         | fdsfagwe | NULL                     | 543gfd | abc              | | liiu     | fdsfagwe | 543gfd                  | fds | f332            | abc |
| lisi | 543gfd      | f332 | fdsfagwe                 | dfsadsa323 | fds              | | wangshi  | f332 | dfsadsa323              | 543gfd | hfd             | fdsfagwe |
| zhangsa | dfsadsa323  | hfd | f332                     | 65ghf | 543gfd           | | liwei    | hfd | 65ghf                   | dfsadsa323 | fds             | f332 |
| wanger | 65ghf       | fds | hfd                      | dsfgg | dfsadsa323       | | qishili  | fds | dsfgg                   | 65ghf | 543gdfsd        | hfd |
| lilisi | dsfgg       | 543gdfsd | fds                      | NULL | 65ghf            | | wutong   | 543gdfsd | NULL                    | dsfgg | NULL            | fds |
+----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+

## RANK、ROW_NUMBER、DENSE_RANK select
 user_id,user_type,sales,
 RANK() over (partition by user_type order by sales desc) as r,
 ROW_NUMBER() over (partition by user_type order by sales desc) as rn,
 DENSE_RANK() over (partition by user_type order by sales desc) as dr
from
 order_detail; 
 
 
+----------+------------+--------+----+-----+-----+--+ | user_id  | user_type | sales  | r | rn  | dr |
+----------+------------+--------+----+-----+-----+--+
| wutong | new        | 6 | 1  | 1 | 1   | | qishili  | new | 5      | 2 | 2   | 2 |
| lilisi | new        | 5 | 2  | 3 | 2   | | wanger   | new | 3      | 4 | 4   | 3 |
| zhangsa | new        | 2 | 5  | 5 | 4   | | qibaqiu  | new | 1      | 6 | 6   | 5 |
| liiu | new        | 1 | 6  | 7 | 5   | | liwei    | old | 3      | 1 | 1   | 1 |
| wangshi | old        | 2 | 2  | 2 | 2   | | lisi     | old | 1      | 3 | 3   | 3 |
+----------+------------+--------+----+-----+-----+--+ 
 
## NTILE
 
select
 user_type,sales,
 --分组内将数据分成2片
 NTILE(2) OVER(PARTITION BY user_type ORDER BY sales) AS nt2,
 --分组内将数据分成3片 
 NTILE(3) OVER(PARTITION BY user_type ORDER BY sales) AS nt3,
 --分组内将数据分成4片 
 NTILE(4) OVER(PARTITION BY user_type ORDER BY sales) AS nt4,
 --将所有数据分成4片
 NTILE(4) OVER(ORDER BY sales) AS all_nt4
from
 order_detail
order by
 user_type,
 sales
 
 
+------------+--------+------+------+------+----------+--+
| user_type | sales  | nt2 | nt3  | nt4 | all_nt4  | +------------+--------+------+------+------+----------+--+ | new        | 1 | 1    | 1 | 1    | 1 |
| new | 1      | 1 | 1    | 1 | 1        | | new        | 2 | 1    | 1 | 2    | 2 |
| new | 3      | 1 | 2    | 2 | 3        | | new        | 5 | 2    | 2 | 3    | 4 |
| new | 5      | 2 | 3    | 3 | 3        | | new        | 6 | 2    | 3 | 4    | 4 |
| old | 1      | 1 | 1    | 1 | 1        | | old        | 2 | 1    | 2 | 2    | 2 |
| old | 3      | 2 | 3    | 3 | 2        | +------------+--------+------+------+------+----------+--+
 
 
求取sale前20%的用户ID
 
select
 user_id
from
(
 select
 user_id,
 NTILE(5) OVER(ORDER BY sales desc) AS nt
 from
 order_detail
)A
where nt=1; ## CUME_DIST、PERCENT_RANK select
user_id,user_type,sales,
--没有partition,所有数据均为1组
CUME_DIST() OVER(ORDER BY sales) AS cd1,
--按照user_type进行分组
CUME_DIST() OVER(PARTITION BY user_type ORDER BY sales) AS cd2
from
order_detail; 
 
+----------+------------+--------+------+----------------------+--+ | user_id  | user_type | sales  | cd1 |         cd2          | +----------+------------+--------+------+----------------------+--+ | liiu     | new | 1      | 0.3 | 0.2857142857142857   | | qibaqiu  | new | 1      | 0.3 | 0.2857142857142857   | | zhangsa  | new | 2      | 0.5 | 0.42857142857142855  | | wanger   | new | 3      | 0.7 | 0.5714285714285714   | | lilisi   | new | 5      | 0.9 | 0.8571428571428571   | | qishili  | new | 5      | 0.9 | 0.8571428571428571   | | wutong   | new | 6      | 1.0 | 1.0                  | | lisi     | old | 1      | 0.3 | 0.3333333333333333   | | wangshi  | old | 2      | 0.5 | 0.6666666666666666   | | liwei    | old | 3      | 0.7 | 1.0                  | +----------+------------+--------+------+----------------------+--+
 
 
select
user_type,sales
--分组内总行数 
SUM(1) OVER(PARTITION BY user_type) AS s,
--RANK值 
RANK() OVER(ORDER BY sales) AS r, 
PERCENT_RANK() OVER(ORDER BY sales) AS pr,
--分组内 
PERCENT_RANK() OVER(PARTITION BY user_type ORDER BY sales) AS prg
from
order_detail; 
 
+----+-----+---------------------+---------------------+--+ | s  | r |         pr          | prg |
+----+-----+---------------------+---------------------+--+
| 7 | 1   | 0.0 | 0.0                 | | 7  | 1 | 0.0                 | 0.0 |
| 7 | 4   | 0.3333333333333333 | 0.3333333333333333  | | 7  | 6 | 0.5555555555555556  | 0.5 |
| 7 | 8   | 0.7777777777777778 | 0.6666666666666666  | | 7  | 8 | 0.7777777777777778  | 0.6666666666666666 |
| 7 | 10  | 1.0 | 1.0                 | | 3  | 1 | 0.0                 | 0.0 |
| 3 | 4   | 0.3333333333333333 | 0.5                 | | 3  | 6 | 0.5555555555555556  | 1.0 |
+----+-----+---------------------+---------------------+--+

扫码二维码 获取免费视频学习资料

Python编程学习

查看2022高级编程视频教程免费获取