【SQL】SQL-常见窗口函数有哪些-上篇一、引言二、窗口函数基础概念三、聚合类窗口函数理解了前面都是坦途1. SUM 累计求和基础用法带分区累计带排序的滑动窗口2. AVG 平均值3. COUNT 计数四、排名类窗口函数面试有笔试的话必考1. ROW_NUMBER / RANK / DENSE_RANK 详解2. 实战班级内排名3. 实战筛选TopN4. 实战连续出现N次 -- 【连续一出面试基本能挂掉90%的人】五、总结对比一、引言今天来聊聊 Hive 里窗口函数的应用场景原来大家对SQL这么感兴趣阅读比Python高好多.orz【窗口函数绝对SQL进阶的关键门槛。SQL 水平能否上一个台阶除了思维更重要的是高效运用窗口函数。】很多同学写聚合查询时只会GROUP BY其实窗口函数才是真爱啊这个浮躁的社会。窗口既能分组聚合又不会丢失明细数据还能轻松实现排名、累计、同比等高阶分析妥妥的。Hive 从 2.0 开始支持窗口函数咱们先讲上篇——聚合类窗口函数和排名类窗口函数配合实战解析保证你看完就能用上。SQL专题往期内容【SQL】基于多源SQL 去重方法对比 – 精华版【SQL】常见SQL 行列转换的方法汇总 - 精华版【SQL】MySql常见的性能优化方式【SQL】SQL同环比计算的多种实现方式【SQL】COUNT… FILTER 的适用场景【SQL】SQL的日期与时间函数【SQL】SQL-常见窗口函数有哪些-上篇二、窗口函数基础概念在正式讲之前先搞清楚几个关键概念窗口函数的基础概念说明示例OVER()窗口函数的声明定义计算窗口SUM(col) OVER(...)PARTITION BY分区类似 GROUP BY但保留明细按部门分组统计ORDER BY窗口内排序决定计算顺序按时间正序ROWS BETWEEN窗口边界控制参与计算的行往前3行~当前行-- 窗口函数基本语法SELECTSUM(column)OVER(PARTITIONBYcol1,col2-- 可选分区ORDERBYcol3-- 可选排序ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW-- 可选窗口边界)AS窗口函数基本语法是不是看着很难理解没事确实也不简单orz…慢慢来~三、聚合类窗口函数理解了前面都是坦途1. SUM 累计求和基础用法-- 简单累计所有数据求和SELECTname,score,SUM(score)OVER()AS总分FROMstudent_scores;划重点OVER()不带任何参数时窗口是全部数据等同于GROUP BY的聚合效果但保留每行明细。带分区累计-- 按班级分组计算累计SELECTclass,name,score,SUM(score)OVER(PARTITIONBYclass)AS班级总分,ROUND(score*100.0/SUM(score)OVER(PARTITIONBYclass),2)AS班级占比FROMstudent_scores;带排序的滑动窗口-- 按时间顺序累计滚动求和SELECTdt,sales,SUM(sales)OVER(ORDERBYdtROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AS累计销售额FROMdaily_salesORDERBYdt;划重点ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从第一行到当前行这是滑动窗口求累计的标配写法。2. AVG 平均值-- 计算每人的平均月度消费SELECTuser_id,month,consumption,AVG(consumption)OVER(PARTITIONBYuser_idORDERBYmonthROWSBETWEEN2PRECEDINGANDCURRENTROW)AS近3月平均-- 包含当前月和前2个月FROMuser_consumptionORDERBYuser_id,month;划重点滑动窗口ROWS BETWEEN 2 PRECEDING AND CURRENT ROW实现了近3个月平均的效果比GROUP BY灵活多了。3. COUNT 计数-- 统计每笔订单之后还有多少笔订单 是不是比你们写一堆的LEFT JOIN 省力多了SELECTorder_id,create_time,amount,COUNT(*)OVER(ORDERBYcreate_timeROWSBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING)AS后续订单数-- 当前行之后所有行FROMordersORDERBYcreate_time;四、排名类窗口函数面试有笔试的话必考1. ROW_NUMBER / RANK / DENSE_RANK 详解这三个函数都是排名但有区别函数特点示例同样是1,2,2,3ROW_NUMBER连续不重复编号1, 2, 3, 4RANK并列跳号1, 2, 2, 4DENSE_RANK并列不跳号1, 2, 2, 3-- 对比三种排名SELECTname,score,ROW_NUMBER()OVER(ORDERBYscoreDESC)AS行号,RANK()OVER(ORDERBYscoreDESC)AS普通排名,DENSE_RANK()OVER(ORDERBYscoreDESC)AS紧凑排名FROMstudent_scoresORDERBYscoreDESC;输出示例name | score | 行号 | 普通排名 | 紧凑排名 张三 | 95 | 1 | 1 | 1 李四 | 90 | 2 | 2 | 2 王五 | 90 | 3 | 2 | 2 赵六 | 85 | 4 | 4 | 3划重点分数相同时ROW_NUMBER给不同编号RANK编号相同但下一个跳号DENSE_RANK编号相同且下一个不跳号。2. 实战班级内排名-- 给每个班级的学生按成绩排名SELECTclass,name,score,ROW_NUMBER()OVER(PARTITIONBYclassORDERBYscoreDESC)AS班级排名,RANK()OVER(PARTITIONBYclassORDERBYscoreDESC)AS班级排名_并列FROMstudent_scoresORDERBYclass,班级排名;3. 实战筛选TopN-- 取出每个班级成绩前3名SELECT*FROM(SELECTclass,name,score,ROW_NUMBER()OVER(PARTITIONBYclassORDERBYscoreDESC)ASrnFROMstudent_scores)tWHERErn3;划重点窗口函数不能直接在 WHERE 里用得套一层子查询来实现筛选。4. 实战连续出现N次 – 【连续一出面试基本能挂掉90%的人】-- 找出连续3天以上活跃的用户WITHuser_activeAS(SELECTDISTINCTuser_id,dtFROMuser_log)SELECTuser_id,MIN(dt)AS连续开始,MAX(dt)AS连续结束,COUNT(*)AS连续天数FROM(SELECTuser_id,dt,-- 把不连续的日期分组dt-ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYdt)*1ASgrpFROMuser_active)tGROUPBYuser_id,grpHAVINGCOUNT(*)3ORDERBYuser_id,连续开始;这是经典的连续天数问题解法通过dt - rn把连续日期归到同一组。五、总结对比函数类别常用函数典型场景聚合类SUM, AVG, COUNT, MAX, MIN累计、占比、滑动平均排名类ROW_NUMBER, RANK, DENSE_RANK名次、TopN、连续天数怎么样窗口函数很好用吧~关键是理解、属于数据结构中如何构建结构的空间思维。下篇预告偏移类窗口函数LAG/LEAD/FIRST_VALUE/LAST_VALUE以及综合实战案例敬请期待今天的分享就到这里觉得有用动动小手点赞关注收藏一键三连~ 有问题留言沟通啦~