over开窗函数 和group by的区别:
OVER子句用于为行为定义一个窗口(windows),以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作的一个行的集合。
例如,聚合函数和排名函数都是可以支持OVER子句的运算类型。由于OVER子句为这些函数提供了一个行的窗口,所以这些函数也称之为开窗函数。
聚合函数的要点就是要对一组值进行聚合,聚合函数传统上一直以GROUP BY查询作为操作的上下文。
用group by对数据进行分组以后,查询为每个组只返回一行; 因此,也就是要限制所有的表达式为每个组只能返回一个值。
1、窗口函数的基本结构
分析函数(如:sum(),max(),row_number()...) + 窗口子句(over函数)
2、over函数写法:
over(partition by year order by id) 或者 over(distribute by year sort by id)
先根据year字段分区,相同的year分为一区,每个分区内根据id字段排序(默认升序)
需要注意:over(partition by year order by id)与 over(partition by year)的区别
order by子句会让输入的数据强制排序(窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。
Order By子句对于诸如Row_Number(),Lead(),LAG()等函数是必须的,如果数据无序,这些函数的结果就没有任何意义。
因此如果有了Order By子句,则Count(),Min()等计算出来的结果就没有任何意义。
前者:代表(默认)分区内从起始行到当前行范围内排序后的计算或取值
后者:代表(默认)整个分区范围内计算或取值
1)AVG,MIN,MAX,SUM 举例:
SELECT cookieid,createtime,pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行,结果同pv1
SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 --当前行+往后所有行
FROM cookie;
如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行;
如果不指定ORDER BY,则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句:
PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:当前行
UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点
–其他AVG,MIN,MAX,和SUM用法一样。
2)NTILE
NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值
NTILE不支持ROWS BETWEEN,
如果切片不均匀,默认增加第一个切片的分布
用于求分区内的前几片(如前1/3天)
3)ROW_NUMBER
ROW_NUMBER() –从1开始,按照顺序,生成分组内记录的序列
ROW_NUMBER() 主要应用于求topN的场景。
语法:row_number()over(partition by h order by age desc) as rownum
row_number、rank、dense_rank区别:
row_number() 按顺序编号,不留空位
rank() 按顺序编号,相同的值编相同号,留空位
dense_rank() 按顺序编号,相同的值编相同的号,不留空位
4)CUME_DIST、PERCENT_RANK
–CUME_DIST() 小于等于当前值的行数/分组内总行数
--------可以用于统计截止当前行数据所占总数据的比率。
–PERCENT_RANK() 分组内当前行的RANK值-1/分组内总行数-1
5)LAG 与 LEAD
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名,
第二个参数为往上第n行(可选,默认为1),
第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
LEAD与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
6)FIRST_VALUE(col) 与 LAST_VALUE(col)
FIRST_VALUE(col)
取分组内排序后,截止到当前行,第一个值
LAST_VALUE(col)
取分组内排序后,截止到当前行,最后一个值