开窗——聚合函数与分析函数

开窗——聚合函数与分析函数

应用场景
（1）排序
（2）动态分组group by
（3）top n
（4）累计计算
（5）层次查询

窗口函数

first_value(col)：取分组内排序后，截止到当前行，第一个值
last_value(col)： 取分组内排序后，截止到当前行，最后一个值
lead(col,n,default) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为null时候，取默认值，如不指定，则为null）
lag(col,n,default) ：用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为null时候，取默认值，如不指定，则为null）

over从句

1、over前使用标准的聚合函数count、sum、min、max、avg，也可以使用其他分析函数row_number()、rank()、dense_rank()、cume_dist、percent_rank、ntile(n)
2、over后括号内使用partition by语句进行分组，可以是一列或多列
3、over后括号内使用order by语句进行排序，可以是一列或多列
4、综合使用partition by与order by语句先后组再排序，可以是一列或多列，数据为分组内排序后截止当前行之前计算内容

窗口规范

开窗——聚合函数

count、sum、min、max、avg、collect_set、collect_list

with tmp as
(
select 'd001' as order_id,123 as user_id,20 as amt,'2020-01-01 00:23:33' as pay_time
union all select 'd002' as order_id,123 as user_id,25 as amt,'2020-01-02 00:23:33' as pay_time
union all select 'd005' as order_id,123 as user_id,250 as amt,'2020-01-04 00:23:33' as pay_time
union all select 'd007' as order_id,123 as user_id,100 as amt,'2020-01-06 00:23:33' as pay_time
union all select 'd003' as order_id,125 as user_id,15 as amt,'2020-01-03 00:23:33' as pay_time
union all select 'd004' as order_id,125 as user_id,39 as amt,'2020-01-03 00:23:33' as pay_time
union all select 'd006' as order_id,125 as user_id,25 as amt,'2020-01-05 00:23:33' as pay_time
union all select 'd008' as order_id,125 as user_id,359 as amt,'2020-01-07 00:23:33' as pay_time
)
select user_id,order_id,amt,pay_time
,count(*)over(partition by user_id) as `当前用户订单数`
,count(distinct user_id)over(partition by 1) as `当前用户数`
,sum(amt)over(partition by user_id) as `当前用户总金额`
,sum(amt)over(order by 1) as `总金额`
,sum(amt)over(partition by user_id order by pay_time) as `当前用户截止当前支付时间累计总金额`
,max(amt)over(partition by user_id) as `当前用户最大金额`
,max(amt)over(order by pay_time) as `截止当前成单时间最大成交金额`
,min(amt)over(partition by user_id) as `当前用户最小金额`
,avg(amt)over(partition by user_id) as `当前用户订单均额`
from tmp

user_id	order_id	amt	pay_time	当前用户订单数	当前用户数	当前用户总金额	总金额	当前用户截止当前支付时间累计总金额	当前用户最大金额	截止当前成单时间最大成交金额	当前用户最小金额	当前用户订单均额
123	d001	20	2020-01-01 00:23:33	4	2	395	833	20	250	20	20	98.75
123	d002	25	2020-01-02 00:23:33	4	2	395	833	45	250	25	20	98.75
125	d004	39	2020-01-03 00:23:33	4	2	438	833	54	359	39	15	109.5
125	d003	15	2020-01-03 00:23:33	4	2	438	833	54	359	39	15	109.5
123	d005	250	2020-01-04 00:23:33	4	2	395	833	295	250	250	20	98.75
125	d006	25	2020-01-05 00:23:33	4	2	438	833	79	359	250	15	109.5
123	d007	100	2020-01-06 00:23:33	4	2	395	833	395	250	250	20	98.75
125	d008	359	2020-01-07 00:23:33	4	2	438	833	438	359	359	15	109.5

开窗——分析函数

row_number() 从1开始，顺序生成序列号，如按成绩排名，也可以用于获取某学科成绩第一名，或获取某学科成绩第一名的学员姓名或其他信息等。
row_number() over(partition  by col1 order by col2) as rowid
结果：1,2,3,4
rank() 并列排序，即下一个数字会跳过
rank() over(partition by col1 order by col2) as rowid
结果：1，2，2，4，5
dense_rank() 并列排序，下一个数据不会跳过
dense_rank() over(partition by col1 order by col2) as rowid
结果：1，2，2，3，4
ntile(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。(https://blog.csdn.net/weixin_36190755/article/details/109056534)
ntile不支持rows between，比如 ntile(2) over(partition by col1 order by col2 rows between 3 preceding and current row)。
first_value(col) 当前分组内排序后col第一个值
last_value(col) 当前分组内排序后col最后一个值
lag(col1,n,default) over(partition by col1 order by col2) as up 向前取第n行
lead(col1,n,default) over(partition by col1 order by col2) as down 向后取第n行
cume_dist() 小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例
percent_rank() 分组内当前行的rank值-1/分组内总行数-1