5-1:sum,avg,min,max窗口内聚合分析
over (partition by col1 order by col2 rows between unbounded[n] preceding and current row[n following])
PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:当前行
UNBOUNDED:起点,
UNBOUNDED PRECEDING 表示从前面的起点,
UNBOUNDED FOLLOWING:表示到后面的终点
select
id
,order_num
,sum(order_num) over (partition by id) `分组内所有行`
,sum(order_num) over (partition by id order by dt) `从起点到当前行`
,sum(order_num) over (partition by id order by dt rows between unbounded preceding and current row) `默认为从起点到当前行`
,sum(order_num) over (partition by id order by dt rows between 3 preceding and current row) `当前行+往前3行`
,sum(order_num) over (partition by id order by dt rows between current row and 3 following) `当前行+往后3行`
,sum(order_num) over (partition by id order by dt rows between 3 preceding and 1 following) `当前行+往前3行+往后1行`
,sum(order_num) over (partition by id order by dt rows between current row and unbounded following) `当前行+往后所有行`
,max(order_num) over (partition by id order by dt rows between 3 preceding and 1 following) `当前行+往前3行+往后1行里面取最大`
,count(1) over (partition by id order by dt rows between 3 preceding and 1 following) `当前行+往前3行+往后1行里面计数`
,avg(order_num) over (partition by id order by dt rows between 3 preceding and 1 following) `当前行+往前3行+往后1行里面求平均数`
,first_value(order_num) over (partition by id order by dt rows between 3 preceding and 1 following) `当前行+往前3行+往后1行第1个值`
,last_value(order_num) over (partition by id order by dt rows between 3 preceding and 1 following ) `当前行+往前3行+往后1行第最后1个值`
from tmp.tmp_clq_20190816
5-2:Lead、leg(适用于环比,周环比,月环比,连续登里等,无需多表关联)
select
id
,dt
,LAG(dt,1,'1970-01-01 00:00:00') over (partition by id order by dt) `向上取1行`
,LAG(dt,3,'1970-01-01 00:00:00') over (partition by id order by dt) `向上取3行`
,lead(dt,1,'1970-01-01 00:00:00') over (partition by id order by dt) `向下取1行`
,lead(dt,3,'1970-01-01 00:00:00') over (partition by id order by dt) `向下取1行`
from tmp.tmp_clq_20190816
5-3:–CUME_DIST 、PERCENT_RANK
–CUME_DIST 小于等于当前值的行数/分组内总行数
–PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
select
id
,order_num
,CUME_DIST()OVER(ORDER BY order_num) `取所有订单数的占比情况`
,CUME_DIST()OVER(PARTITION BY id ORDER BY order_num) `按照id分组内取订单数的占比情况`
,PERCENT_RANK() OVER(PARTITION BY id ORDER BY order_num) `分组内当前行的RANK值-1/分组内总行数-1`
,NTILE(2) OVER(PARTITION BY id ORDER BY order_num) `分组内将数据分成2片`
,NTILE(3) OVER(PARTITION BY id ORDER BY order_num) `分组内将数据分成3片`
,NTILE(4) OVER(PARTITION BY id ORDER BY order_num) `分组内将数据分成4片`
from tmp.tmp_clq_20190816
5-4:GROUPING SETS
在一个GROUP BY查询中,根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL
select
id,biz ,sum(order_num)
,GROUPING__ID
from tmp.tmp_clq_20190816
group by
id,biz
GROUPING SETS (
(id,biz),id,biz
) order by GROUPING__ID
5-5:CUBE
根据GROUP BY的维度的所有组合进行聚合
select
id ,biz ,sum(order_num)
,GROUPING__ID
from tmp.tmp_clq_20190816
group by
id,biz
WITH CUBE
order by GROUPING__ID
5-6:ROLLUP
是CUBE的子集,以最左侧的维度为主,从该维度进行层级聚合
select
id,biz
,sum(order_num)
,GROUPING__ID
from tmp.tmp_clq_20190816
group by
id,biz
WITH ROLLUP
order by GROUPING__ID