1、grouping sets 和 rollup 和cube 的区别
转载:https://blog.csdn.net/hu_pengxue/article/details/81172100
- grouping sets :返回所有分组字段的小计
- rollup: 返回所有分组字段小计(除去最后一个分组字段)、总体合计、第n个字段的详细信息
- cube: 返回总体合计、所有分组字段的小计、第n个字段的详细信息
2、rank() 和 dense_rank() 的区别
- rank() :生成数据项在分组中的排名,排名相等会在名次中留下空位
- dense() :生成数据项在分组排名中的排名,排名相等不会在名次中留下空位
- 举例:
##sql##
SELECT name,price,
RANK() OVER(ORDER BY price DESC) as rank_1
DENSE() OVER(ORDER BY price DESC) as rank_2
FROM Products;
结果显示:
ROW_NUMBER() 从1开始,按照顺序,生成分组内记录的序列,比如,按照pv降序排列,生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多,再比如,获取分组内排序第一的记录;获取一个session中的第一条refer等。
CUME_DIST() 小于等于当前值的行数/分组内总行数。比如,统计小于等于当前薪水的人数,所占总人数的比例
PERCENT_RANK() 分组内当前行的RANK值-1/分组内总行数-1
NTILE(n) 用于将分组数据按照顺序切分成n片,返回当前切片值,如果切片不均匀,默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。
3、
first_value:取分组内排序后,截止到当前行,第一个值
last_value: 取分组内排序后,截止到当前行,最后一个值
lead(col,n,DEFAULT) :用于统计窗口内往下第n行值。第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
lag(col,n,DEFAULT) :与lead相反,用于统计窗口内往上第n行值。第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
4、
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句:
PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:当前行
UNBOUNDED:无界限(起点或终点)
UNBOUNDED PRECEDING:表示从前面的起点
UNBOUNDED FOLLOWING:表示到后面的终点
其他COUNT、AVG,MIN,MAX,和SUM用法一样。