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
