1.聚合函数
SUM( 列 ) OVER( ... ) :求总和
AVG( 列 ) OVER( ... ) :求平均
COUNT( 列 ) OVER( ... ) :求总数
MIN( 列 ) OVER( ... ) :取最小值
MAX( 列 ) OVER( ... ) :取最大值
2.排名函数
RANK() OVER(...) :如果排名相同,编号跳过,比如 1、2、2、5
DENSE_RANK() OVER(...) :编号不跳过,比如 1、2、2、4
ROW_NUMBER() OVER(...) :如果排名相同,编号不会重复,比如 1、2、3、4
NTILE( n ) OVER(...) :均匀地划分n个组,返回对应组号。
3.跨行取值函数
LAG( 列, 偏移, 默认值 ) OVER(...) :上 n 行,可以只写 LAG(列),默认取上一行
LEAD( 列, 偏移, 默认值 ) OVER(...) :下 n 行,同上
FIRST_VALUE( 列 ) OVER(...) :首行
LAST_VALUE( 列 ) OVER(...) :尾行
4.分布函数
PERCENT_RANK() OVER(ORDER BY 列) :计算 小于 当前值的行数占 总行数 - 1 的比例。
CUME_DIST() OVER(ORDER BY 列) :计算 小于等于 当前值的行数占总行数的比例。
5.排序分组关键字
OVER( PARTITION BY 列 ) :分组
OVER( ORDER BY 列 ) :排序,注意:如果配合聚合函数使用会产生截断效果,导致只能聚合到当前行及之前的数据。可结合移动关键字解除。
6.窗口移动
移动关键字:
ROWS :以 行号 为单位移动窗口。
RANGE :以 字段值 为单位移动窗口,必须结合排序关键字,以 ORDER BY 的列值为移动基准。
范围关键字:
UNBOUNDED PRECEDING :首行
n PRECEDING :前 n 行
CURRENT ROW :当前行
n FOLLOWING :后 n 行
UNBOUNDED FOLLOWING :末行
演示:
# 上一行 到 当前行
OVER( ROWS 1 PRECEDING )
# 首行 到 当前行
OVER( ROWS UNBOUNDED PRECEDING )
# 当前行 到 下一行
OVER( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING )
# 上一行 到 末行
OVER( ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING )
# 当前天 到 下一天
# RANGE 以值为单位
# 如果当前日期是 2024-5-1,那么所有等于 2024-5-1 和 2024-5-2 的数据都会被聚合。
# RANGE 只会统计当前天 和 +1 天的记录,如果没有 2024-5-2 ,不会接着去找 2024-5-3。
COUNT( mydate )OVER( ORDER BY mydate RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING )
# 上一天 到 当前天
COUNT( myinter )OVER( ORDER BY mydate RANGE ROWS 1 PRECEDING )