SQL总结-开窗函数 SQL总结-开窗函数

一 . 开窗函数分组函数

语法结构：select col1，分析函数（col2） from table group by col1

开窗函数

语法结构：函数名（col2）over（partition by ool1 order by col3）

分组与开窗的区别
分组函数每组只返回一行，而开窗函数每组返回多行。如下：
分组查询

select visit_id, count(session_id) ct from event group by visit_id

文章图片
image 开窗查询

select visit_id,count(session_id) over(partition by visit_id) ct from event

文章图片
image 对比可以发现，visit_id为00009e20-e3ec-4340-bf44-484b523fafbc时，用分组查询，ct只返回一行结果：8，而开窗查询visit_id的8条记录，都返回了，并且这八条记录的ct都为8。
二 . rows between关键字含义 PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点
举例如下：

文章图片
image 三 . 常见分析函数总结
【SQL总结-开窗函数】测试数据

文章图片
image 1）sum求和函数加order by时

select cookieid, createtime, pv, sum(pv) over(partition by cookieid order by createtime) as pv1 from event

文章图片
image 不加order by时

select cookieid, createtime, pv, sum(pv) over(partition by cookieid) as pv1 from event

文章图片
image 对比可以发现，sum函数加了order by之后，不仅会对createtime排序，而且求的和是从起点到当前行的求和，而不是整个分区（分组）的求和。而不加order by，是对整个分组（分片）求和注：max()函数无论有没有order by 都是计算整个分区的最大值
2）几个排序函数排序函数：row_number() 、rank() 、dense_rank() 、ntile() ，其区别如下：
（1） row_number() over()：对相等的值不进行区分，相等的值对应的排名相同，序号从1到n连续。
（2）rank() over()：相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。
（3）dense_rank() over()：对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。
（4） ntile( n ) over()：可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。学生成绩表同上，查询语句如下：

select id, stu_name, course_name, grades, row_number() over(order by grades) as row_num, rank() over(order by grades) as rank, dense_rank() over(order by grades) as dense_rank, ntile(5) over(order by grades) as ntile from students_grades

查询结果如下：

文章图片
image 3）lag和leag函数 lag(col,n,default) 用于统计窗口内往前第n行值，default表示默认值，意思是如果往前第n行的值null时，则返回默认值，如果不指定，则返回null。

SELECT cookieid, createtime,pv, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, LAG(createtime,1,'1970-01-01') OVER(PARTITION BY cookieid ORDER BY createtime) AS lag1, LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS lag2 FROM event;

文章图片
image ps：leag(col,n,default) 用于统计窗口内往下第n行值
4）first_value和last_value函数 first_value返回分组内排序后，截止到当前行，第一个值

SELECT cookieid, createtime, pv, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, FIRST_VALUE(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS first FROM event

文章图片
image ps：last_value和first_value相反，取最后一个值
参考文档：
1）Hive开窗函数整理
https://blog.csdn.net/Abysscarry/article/details/81408265?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase
2）数据分析（SQL）常见面试题（一）：开窗函数
https://www.douban.com/group/topic/155112949/
最后欢迎关注微信公众号