1.开窗函数
1. row_number() over()
- 示例: row_number() OVER (PARTITION BY category ORDER BY revenue DESC) rank
作用: 给每个分组的数据,按照排序顺序,打上分组内的行号(分组topN)
2. sum、max、min、count、avg等聚合函数
示例:sum(pv) over(partition by cookieid order by createtime) as pv1
作用: 给每个分组按顺序排序后, 前后两两累加
window子句:
- rows between … and …(开始到结束,位置不能交换)
- unbounded preceding :从第一行开始
- current row:到当前行
- 首行:unbounded preceding
- 末行:unbounded following
- 前 n 行:n preceding
- 后 n 行:n following
rows & range
- 是逻辑窗口,是指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内
- 是物理窗口,根据order by子句排序后,取前n行的数据以及后n行的数据进行计算(与当前行的值无关,至于排序由的行号有关)
3. 几种排序函数
- row_number() :顺序排,忽略并列排名
- dense_rank() :有并列,后面的元素接着排名
- rank() :有并列,后面的元素跳着排名
- ntile(n) :用于将分组数据按照顺序切分成n片
4. lag & lead
- lag(field,n):取前 n 行的值
- lead(field n):取后 n 行的值
5. first_value & last_value
- first_value(field) :取分组内排序后,截止到当前行的第一个值
- last_value(field) :取分组内排序后,截止到当前行的最后一个值