1.1 窗口函数核心概念1.1.1 什么是窗口函数窗口函数Window Function是Hive SQL中一类特殊的函数它可以在不改变原始数据行数的前提下对一组相关的行进行计算。与聚合函数不同窗口函数不会将多行数据压缩为一行而是为每一行返回一个计算结果。1.1.2 窗口函数分类函数类别核心函数功能说明排序函数ROW_NUMBER()唯一连续排名1,2,3…排序函数RANK()跳跃排名1,2,2,4…排序函数DENSE_RANK()密集排名1,2,2,3…取值函数LAG(col, offset, default)取前N行数据取值函数LEAD(col, offset, default)取后N行数据取值函数FIRST_VALUE(col)取窗口内第一行取值函数LAST_VALUE(col)取窗口内最后一行聚合窗口SUM/AVG/COUNT/MIN/MAX OVER()窗口内聚合计算分布函数CUME_DIST()累积分布分布函数PERCENT_RANK()百分比排名分组函数NTILE(n)将数据分为N组1.1.3 窗口函数语法function_name(expression)OVER([PARTITIONBYpartition_col]-- 分区按哪些字段分组[ORDERBYorder_col]-- 排序窗口内数据的排序方式[ROWS|RANGE frame_clause]-- 窗口帧计算范围)1.1.4 窗口帧详解-- 常用窗口帧写法ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW-- 从分区开始到当前行ROWSBETWEEN1PRECEDINGAND1FOLLOWING-- 前1行到后1行ROWSBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING-- 当前行到分区末尾RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW-- 值范围去重1.2 窗口函数综合练习以下练习继续使用第8章的电商数据库数据表结构请参考初级函数篇。1.2.1 用户消费排名ROW_NUMBER vs RANK vs DENSE_RANK1. 题目需求统计2021年每个用户的订单总金额并按金额降序排名分别使用ROW_NUMBER、RANK和DENSE_RANK三种方式排名。期望结果部分user_idtotal_amountrow_numrank_numdense_rank_num101144860.00111102177850.0022210375890.003332. 思路分析知识储备ROW_NUMBER()连续唯一排名相同值也会分配不同序号。RANK()跳跃排名相同值排名相同后续排名跳过。DENSE_RANK()密集排名相同值排名相同后续排名连续。执行步骤筛选2021年订单数据按user_id分组sum(total_amount)。使用OVER(ORDER BY total_amount DESC)开窗分别应用三种排序函数。3. 代码实现SELECTuser_id,total_amount,ROW_NUMBER()OVER(ORDERBYtotal_amountDESC)row_num,RANK()OVER(ORDERBYtotal_amountDESC)rank_num,DENSE_RANK()OVER(ORDERBYtotal_amountDESC)dense_rank_numFROM(SELECTuser_id,SUM(total_amount)total_amountFROMorder_infoWHEREyear(create_date)2021GROUPBYuser_id)t;4. 三种排序函数对比1.2.2 每个用户最近三次登录间隔分析LAG函数1. 题目需求从登录明细表中查询每个用户每次登录与上一次登录的间隔天数。期望结果部分user_idlogin_datelast_login_datediff_days1012021-09-21NULLNULL1012021-09-272021-09-2161012021-09-282021-09-2711012021-09-292021-09-2811012021-09-302021-09-2912. 思路分析知识储备LAG(col, offset, default_value)获取当前行向前offset行的col值。LEAD(col, offset, default_value)获取当前行向后offset行的col值。执行步骤使用date_format将login_ts转为日期格式。按user_id分区、login_ts排序LAG获取上一次登录日期。datediff计算间隔天数。3. 代码实现SELECTuser_id,login_date,last_login_date,datediff(login_date,last_login_date)diff_daysFROM(SELECTuser_id,date_format(login_ts,yyyy-MM-dd)login_date,LAG(date_format(login_ts,yyyy-MM-dd),1,NULL)OVER(PARTITIONBYuser_idORDERBYlogin_ts)last_login_dateFROMuser_login_detail)t;4. LAG/LEAD函数示意图1.2.3 商品销量累计占比分析聚合窗口函数1. 题目需求统计2021年各商品累计销量并计算每个商品的销量累计占比帕累托分析。期望结果部分sku_idsku_sumcum_sumtotal_sumcum_ratio122058320583684380.302601826601684380.3945368438684381.002. 思路分析知识储备SUM(col) OVER (ORDER BY ...)累计求和。SUM(col) OVER ()全局求和不分区。执行步骤统计各商品2021年累计销量。按销量降序排列SUM OVER计算累计销量。SUM OVER()计算总销量二者相除得累计占比。3. 代码实现SELECTsku_id,sku_sum,cum_sum,total_sum,round(cum_sum/total_sum,2)cum_ratioFROM(SELECTsku_id,sku_sum,SUM(sku_sum)OVER(ORDERBYsku_sumDESC)cum_sum,SUM(sku_sum)OVER()total_sumFROM(SELECTsku_id,SUM(sku_num)sku_sumFROMorder_detailWHEREyear(create_date)2021GROUPBYsku_id)t1)t2;1.2.4 用户消费分层NTILE函数1. 题目需求根据2021年用户订单总金额将用户分为高、中、低三个消费层级。期望结果部分user_idtotal_amountntile_grouplevel101144860.001高消费102177850.001高消费10375890.002中消费10489880.002中消费2. 思路分析知识储备NTILE(n)将有序数据分为n组每组数量尽可能相等。执行步骤统计2021年各用户订单总额。NTILE(3)将用户分为3组。CASE WHEN映射为中文层级。3. 代码实现SELECTuser_id,total_amount,ntile_group,CASEntile_groupWHEN1THEN高消费WHEN2THEN中消费WHEN3THEN低消费ENDlevelFROM(SELECTuser_id,SUM(total_amount)total_amount,NTILE(3)OVER(ORDERBYSUM(total_amount)DESC)ntile_groupFROMorder_infoWHEREyear(create_date)2021GROUPBYuser_id)t;1.2.5 每日商品销量7日移动平均1. 题目需求统计商品1在2021年10月的每日销量及7日移动平均销量。期望结果部分create_datedaily_saleavg_7day2021-10-0188.002021-10-0204.002021-10-0344.002021-10-0433.752021-10-0554.002021-10-0684.672021-10-0785.142. 思路分析知识储备AVG(col) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)7日移动平均。执行步骤筛选商品1在2021年10月的订单数据。按日期分组统计每日销量。使用ROWS BETWEEN 6 PRECEDING AND CURRENT ROW计算7日移动平均。3. 代码实现SELECTcreate_date,daily_sale,ROUND(AVG(daily_sale)OVER(ORDERBYcreate_dateROWSBETWEEN6PRECEDINGANDCURRENTROW),2)avg_7dayFROM(SELECTcreate_date,SUM(sku_num)daily_saleFROMorder_detailWHEREsku_id1ANDcreate_date2021-10-01ANDcreate_date2021-10-31GROUPBYcreate_date)t;1.2.6 用户首次购买和复购分析FIRST_VALUE1. 题目需求分析每个用户的首次购买日期、最近一次购买日期以及购买次数。期望结果部分user_idfirst_order_datelast_order_dateorder_count1012020-10-082021-10-2491022021-10-012021-10-0241032021-10-022021-10-0342. 思路分析知识储备FIRST_VALUE(col)取窗口内第一行的值。LAST_VALUE(col)取窗口内最后一行的值注意默认窗口帧。执行步骤按user_id分区、create_date排序。FIRST_VALUE获取首次购买日期LAST_VALUE获取末次购买日期。COUNT统计购买次数。3. 代码实现SELECTDISTINCTuser_id,FIRST_VALUE(create_date)OVER(PARTITIONBYuser_idORDERBYcreate_dateROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)first_order_date,LAST_VALUE(create_date)OVER(PARTITIONBYuser_idORDERBYcreate_dateROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)last_order_date,COUNT(*)OVER(PARTITIONBYuser_id)order_countFROMorder_info;1.2.7 商品品类销售排名分区排序1. 题目需求统计2021年各品类下每个商品的销售金额并计算该商品在品类内的销售排名。期望结果部分category_namesku_idnameamountrank_in_category数码4xiaomi 13318000.001数码3apple 12175000.002数码1xiaomi 1098000.003数码2手机壳60180.004厨卫6洗碗机52000.0012. 思路分析执行步骤关联order_detail、sku_info、category_info获取品类信息。按category_id和sku_id分组统计销售金额。按品类分区PARTITION BY category_id按金额排序ROW_NUMBER排名。3. 代码实现SELECTcategory_name,sku_id,name,amount,ROW_NUMBER()OVER(PARTITIONBYcategory_idORDERBYamountDESC)rank_in_categoryFROM(SELECTsi.category_id,ci.category_name,od.sku_id,si.name,SUM(od.price*od.sku_num)amountFROMorder_detail odJOINsku_info siONod.sku_idsi.sku_idJOINcategory_info ciONsi.category_idci.category_idWHEREyear(od.create_date)2021GROUPBYsi.category_id,ci.category_name,od.sku_id,si.name)t;1.2.8 用户连续登录天数统计复杂窗口应用1. 题目需求统计每个用户最长的连续登录天数。期望结果user_idmax_consecutive_days10151021103110411051106210721081109110101101172. 思路分析核心思路——断点法获取每个用户的登录日期去重。使用ROW_NUMBER()为每个用户的登录日期编号。用登录日期减去编号如果连续登录差值相同。按用户和差值分组COUNT统计每组数量即为连续天数。取最大值。3. 代码实现SELECTuser_id,MAX(consecutive_days)max_consecutive_daysFROM(SELECTuser_id,diff_group,COUNT(*)consecutive_daysFROM(SELECTuser_id,login_date,DATE_SUB(login_date,rn)diff_groupFROM(SELECTDISTINCTuser_id,date_format(login_ts,yyyy-MM-dd)login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYdate_format(login_ts,yyyy-MM-dd))rnFROMuser_login_detail)t1)t2GROUPBYuser_id,diff_group)t3GROUPBYuser_id;1.2.9 用户消费趋势对比同比环比1. 题目需求统计每个用户每月消费金额并计算环比增长率与上月对比和同比增长率与去年同月对比。期望结果部分user_idyear_monthamountlast_month_amountmom_ratiolast_year_amountyoy_ratio1012021-011200.00NULLNULLNULLNULL1012021-09143660.001200.00118.72NULLNULL1012021-101200.00143660.00-0.99NULLNULL1012022-09240.00NULLNULL143660.00-1.002. 思路分析知识储备LAG(amount, 1)取上月数据环比。LAG(amount, 12)取去年同月数据同比。执行步骤按user_id和年月分组统计消费金额。LAG(1)获取上月金额计算环比。LAG(12)获取去年同期金额计算同比。3. 代码实现SELECTuser_id,year_month,amount,last_month_amount,ROUND((amount-last_month_amount)/last_month_amount,2)mom_ratio,last_year_amount,ROUND((amount-last_year_amount)/last_year_amount,2)yoy_ratioFROM(SELECTuser_id,DATE_FORMAT(create_date,yyyy-MM)year_month,SUM(total_amount)amount,LAG(SUM(total_amount),1)OVER(PARTITIONBYuser_idORDERBYDATE_FORMAT(create_date,yyyy-MM))last_month_amount,LAG(SUM(total_amount),12)OVER(PARTITIONBYuser_idORDERBYDATE_FORMAT(create_date,yyyy-MM))last_year_amountFROMorder_infoGROUPBYuser_id,DATE_FORMAT(create_date,yyyy-MM))t;1.2.10 TOP-N问题每个品类销量前3的商品1. 题目需求查询2021年每个品类下销量排名前3的商品。期望结果category_namesku_idnamesku_sumrank_num厨卫12遮阳伞205561厨卫2手机壳60182厨卫6洗碗机263户外10帐篷2051户外11烧烤架2252户外9自行车1823数码4xiaomi 13531数码1xiaomi 10492数码3apple 123532. 思路分析执行步骤关联order_detail、sku_info、category_info。按品类和商品分组统计销量。按品类分区销量降序ROW_NUMBER排名。过滤排名≤3的记录。3. 代码实现SELECTcategory_name,sku_id,name,sku_sum,rank_numFROM(SELECTci.category_name,od.sku_id,si.name,SUM(od.sku_num)sku_sum,ROW_NUMBER()OVER(PARTITIONBYsi.category_idORDERBYSUM(od.sku_num)DESC)rank_numFROMorder_detail odJOINsku_info siONod.sku_idsi.sku_idJOINcategory_info ciONsi.category_idci.category_idWHEREyear(od.create_date)2021GROUPBYci.category_name,si.category_id,od.sku_id,si.name)tWHERErank_num3;1.3 本章总结1.3.1 窗口函数 vs 聚合函数特性聚合函数窗口函数输出行数压缩为一行保持原始行数GROUP BY必须可选PARTITION BY排序无支持ORDER BY范围控制无支持ROWS/RANGE典型场景汇总统计排名、累计、趋势1.3.2 常见面试题ROW_NUMBER和RANK的区别ROW_NUMBER连续唯一RANK跳跃排名。如何计算7日移动平均AVG OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)如何统计连续登录天数日期 - ROW_NUMBER编号差值相同即为连续。LAG和LEAD的区别LAG取前N行LEAD取后N行。窗口函数的执行顺序在WHERE、GROUP BY、HAVING之后ORDER BY之前执行。1.3.3 学习路径建议初级函数 → 窗口函数 → 高级优化 → 实际项目 ↓ ↓ ↓ ↓ 条件聚合 排序取值 性能调优 业务建模 类型转换 累计计算 执行计划 数据治理
Hive窗口函数综合案例
发布时间：2026/6/3 0:16:50
1.1 窗口函数核心概念1.1.1 什么是窗口函数窗口函数Window Function是Hive SQL中一类特殊的函数它可以在不改变原始数据行数的前提下对一组相关的行进行计算。与聚合函数不同窗口函数不会将多行数据压缩为一行而是为每一行返回一个计算结果。1.1.2 窗口函数分类函数类别核心函数功能说明排序函数ROW_NUMBER()唯一连续排名1,2,3…排序函数RANK()跳跃排名1,2,2,4…排序函数DENSE_RANK()密集排名1,2,2,3…取值函数LAG(col, offset, default)取前N行数据取值函数LEAD(col, offset, default)取后N行数据取值函数FIRST_VALUE(col)取窗口内第一行取值函数LAST_VALUE(col)取窗口内最后一行聚合窗口SUM/AVG/COUNT/MIN/MAX OVER()窗口内聚合计算分布函数CUME_DIST()累积分布分布函数PERCENT_RANK()百分比排名分组函数NTILE(n)将数据分为N组1.1.3 窗口函数语法function_name(expression)OVER([PARTITIONBYpartition_col]-- 分区按哪些字段分组[ORDERBYorder_col]-- 排序窗口内数据的排序方式[ROWS|RANGE frame_clause]-- 窗口帧计算范围)1.1.4 窗口帧详解-- 常用窗口帧写法ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW-- 从分区开始到当前行ROWSBETWEEN1PRECEDINGAND1FOLLOWING-- 前1行到后1行ROWSBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING-- 当前行到分区末尾RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW-- 值范围去重1.2 窗口函数综合练习以下练习继续使用第8章的电商数据库数据表结构请参考初级函数篇。1.2.1 用户消费排名ROW_NUMBER vs RANK vs DENSE_RANK1. 题目需求统计2021年每个用户的订单总金额并按金额降序排名分别使用ROW_NUMBER、RANK和DENSE_RANK三种方式排名。期望结果部分user_idtotal_amountrow_numrank_numdense_rank_num101144860.00111102177850.0022210375890.003332. 思路分析知识储备ROW_NUMBER()连续唯一排名相同值也会分配不同序号。RANK()跳跃排名相同值排名相同后续排名跳过。DENSE_RANK()密集排名相同值排名相同后续排名连续。执行步骤筛选2021年订单数据按user_id分组sum(total_amount)。使用OVER(ORDER BY total_amount DESC)开窗分别应用三种排序函数。3. 代码实现SELECTuser_id,total_amount,ROW_NUMBER()OVER(ORDERBYtotal_amountDESC)row_num,RANK()OVER(ORDERBYtotal_amountDESC)rank_num,DENSE_RANK()OVER(ORDERBYtotal_amountDESC)dense_rank_numFROM(SELECTuser_id,SUM(total_amount)total_amountFROMorder_infoWHEREyear(create_date)2021GROUPBYuser_id)t;4. 三种排序函数对比1.2.2 每个用户最近三次登录间隔分析LAG函数1. 题目需求从登录明细表中查询每个用户每次登录与上一次登录的间隔天数。期望结果部分user_idlogin_datelast_login_datediff_days1012021-09-21NULLNULL1012021-09-272021-09-2161012021-09-282021-09-2711012021-09-292021-09-2811012021-09-302021-09-2912. 思路分析知识储备LAG(col, offset, default_value)获取当前行向前offset行的col值。LEAD(col, offset, default_value)获取当前行向后offset行的col值。执行步骤使用date_format将login_ts转为日期格式。按user_id分区、login_ts排序LAG获取上一次登录日期。datediff计算间隔天数。3. 代码实现SELECTuser_id,login_date,last_login_date,datediff(login_date,last_login_date)diff_daysFROM(SELECTuser_id,date_format(login_ts,yyyy-MM-dd)login_date,LAG(date_format(login_ts,yyyy-MM-dd),1,NULL)OVER(PARTITIONBYuser_idORDERBYlogin_ts)last_login_dateFROMuser_login_detail)t;4. LAG/LEAD函数示意图1.2.3 商品销量累计占比分析聚合窗口函数1. 题目需求统计2021年各商品累计销量并计算每个商品的销量累计占比帕累托分析。期望结果部分sku_idsku_sumcum_sumtotal_sumcum_ratio122058320583684380.302601826601684380.3945368438684381.002. 思路分析知识储备SUM(col) OVER (ORDER BY ...)累计求和。SUM(col) OVER ()全局求和不分区。执行步骤统计各商品2021年累计销量。按销量降序排列SUM OVER计算累计销量。SUM OVER()计算总销量二者相除得累计占比。3. 代码实现SELECTsku_id,sku_sum,cum_sum,total_sum,round(cum_sum/total_sum,2)cum_ratioFROM(SELECTsku_id,sku_sum,SUM(sku_sum)OVER(ORDERBYsku_sumDESC)cum_sum,SUM(sku_sum)OVER()total_sumFROM(SELECTsku_id,SUM(sku_num)sku_sumFROMorder_detailWHEREyear(create_date)2021GROUPBYsku_id)t1)t2;1.2.4 用户消费分层NTILE函数1. 题目需求根据2021年用户订单总金额将用户分为高、中、低三个消费层级。期望结果部分user_idtotal_amountntile_grouplevel101144860.001高消费102177850.001高消费10375890.002中消费10489880.002中消费2. 思路分析知识储备NTILE(n)将有序数据分为n组每组数量尽可能相等。执行步骤统计2021年各用户订单总额。NTILE(3)将用户分为3组。CASE WHEN映射为中文层级。3. 代码实现SELECTuser_id,total_amount,ntile_group,CASEntile_groupWHEN1THEN高消费WHEN2THEN中消费WHEN3THEN低消费ENDlevelFROM(SELECTuser_id,SUM(total_amount)total_amount,NTILE(3)OVER(ORDERBYSUM(total_amount)DESC)ntile_groupFROMorder_infoWHEREyear(create_date)2021GROUPBYuser_id)t;1.2.5 每日商品销量7日移动平均1. 题目需求统计商品1在2021年10月的每日销量及7日移动平均销量。期望结果部分create_datedaily_saleavg_7day2021-10-0188.002021-10-0204.002021-10-0344.002021-10-0433.752021-10-0554.002021-10-0684.672021-10-0785.142. 思路分析知识储备AVG(col) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)7日移动平均。执行步骤筛选商品1在2021年10月的订单数据。按日期分组统计每日销量。使用ROWS BETWEEN 6 PRECEDING AND CURRENT ROW计算7日移动平均。3. 代码实现SELECTcreate_date,daily_sale,ROUND(AVG(daily_sale)OVER(ORDERBYcreate_dateROWSBETWEEN6PRECEDINGANDCURRENTROW),2)avg_7dayFROM(SELECTcreate_date,SUM(sku_num)daily_saleFROMorder_detailWHEREsku_id1ANDcreate_date2021-10-01ANDcreate_date2021-10-31GROUPBYcreate_date)t;1.2.6 用户首次购买和复购分析FIRST_VALUE1. 题目需求分析每个用户的首次购买日期、最近一次购买日期以及购买次数。期望结果部分user_idfirst_order_datelast_order_dateorder_count1012020-10-082021-10-2491022021-10-012021-10-0241032021-10-022021-10-0342. 思路分析知识储备FIRST_VALUE(col)取窗口内第一行的值。LAST_VALUE(col)取窗口内最后一行的值注意默认窗口帧。执行步骤按user_id分区、create_date排序。FIRST_VALUE获取首次购买日期LAST_VALUE获取末次购买日期。COUNT统计购买次数。3. 代码实现SELECTDISTINCTuser_id,FIRST_VALUE(create_date)OVER(PARTITIONBYuser_idORDERBYcreate_dateROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)first_order_date,LAST_VALUE(create_date)OVER(PARTITIONBYuser_idORDERBYcreate_dateROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)last_order_date,COUNT(*)OVER(PARTITIONBYuser_id)order_countFROMorder_info;1.2.7 商品品类销售排名分区排序1. 题目需求统计2021年各品类下每个商品的销售金额并计算该商品在品类内的销售排名。期望结果部分category_namesku_idnameamountrank_in_category数码4xiaomi 13318000.001数码3apple 12175000.002数码1xiaomi 1098000.003数码2手机壳60180.004厨卫6洗碗机52000.0012. 思路分析执行步骤关联order_detail、sku_info、category_info获取品类信息。按category_id和sku_id分组统计销售金额。按品类分区PARTITION BY category_id按金额排序ROW_NUMBER排名。3. 代码实现SELECTcategory_name,sku_id,name,amount,ROW_NUMBER()OVER(PARTITIONBYcategory_idORDERBYamountDESC)rank_in_categoryFROM(SELECTsi.category_id,ci.category_name,od.sku_id,si.name,SUM(od.price*od.sku_num)amountFROMorder_detail odJOINsku_info siONod.sku_idsi.sku_idJOINcategory_info ciONsi.category_idci.category_idWHEREyear(od.create_date)2021GROUPBYsi.category_id,ci.category_name,od.sku_id,si.name)t;1.2.8 用户连续登录天数统计复杂窗口应用1. 题目需求统计每个用户最长的连续登录天数。期望结果user_idmax_consecutive_days10151021103110411051106210721081109110101101172. 思路分析核心思路——断点法获取每个用户的登录日期去重。使用ROW_NUMBER()为每个用户的登录日期编号。用登录日期减去编号如果连续登录差值相同。按用户和差值分组COUNT统计每组数量即为连续天数。取最大值。3. 代码实现SELECTuser_id,MAX(consecutive_days)max_consecutive_daysFROM(SELECTuser_id,diff_group,COUNT(*)consecutive_daysFROM(SELECTuser_id,login_date,DATE_SUB(login_date,rn)diff_groupFROM(SELECTDISTINCTuser_id,date_format(login_ts,yyyy-MM-dd)login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYdate_format(login_ts,yyyy-MM-dd))rnFROMuser_login_detail)t1)t2GROUPBYuser_id,diff_group)t3GROUPBYuser_id;1.2.9 用户消费趋势对比同比环比1. 题目需求统计每个用户每月消费金额并计算环比增长率与上月对比和同比增长率与去年同月对比。期望结果部分user_idyear_monthamountlast_month_amountmom_ratiolast_year_amountyoy_ratio1012021-011200.00NULLNULLNULLNULL1012021-09143660.001200.00118.72NULLNULL1012021-101200.00143660.00-0.99NULLNULL1012022-09240.00NULLNULL143660.00-1.002. 思路分析知识储备LAG(amount, 1)取上月数据环比。LAG(amount, 12)取去年同月数据同比。执行步骤按user_id和年月分组统计消费金额。LAG(1)获取上月金额计算环比。LAG(12)获取去年同期金额计算同比。3. 代码实现SELECTuser_id,year_month,amount,last_month_amount,ROUND((amount-last_month_amount)/last_month_amount,2)mom_ratio,last_year_amount,ROUND((amount-last_year_amount)/last_year_amount,2)yoy_ratioFROM(SELECTuser_id,DATE_FORMAT(create_date,yyyy-MM)year_month,SUM(total_amount)amount,LAG(SUM(total_amount),1)OVER(PARTITIONBYuser_idORDERBYDATE_FORMAT(create_date,yyyy-MM))last_month_amount,LAG(SUM(total_amount),12)OVER(PARTITIONBYuser_idORDERBYDATE_FORMAT(create_date,yyyy-MM))last_year_amountFROMorder_infoGROUPBYuser_id,DATE_FORMAT(create_date,yyyy-MM))t;1.2.10 TOP-N问题每个品类销量前3的商品1. 题目需求查询2021年每个品类下销量排名前3的商品。期望结果category_namesku_idnamesku_sumrank_num厨卫12遮阳伞205561厨卫2手机壳60182厨卫6洗碗机263户外10帐篷2051户外11烧烤架2252户外9自行车1823数码4xiaomi 13531数码1xiaomi 10492数码3apple 123532. 思路分析执行步骤关联order_detail、sku_info、category_info。按品类和商品分组统计销量。按品类分区销量降序ROW_NUMBER排名。过滤排名≤3的记录。3. 代码实现SELECTcategory_name,sku_id,name,sku_sum,rank_numFROM(SELECTci.category_name,od.sku_id,si.name,SUM(od.sku_num)sku_sum,ROW_NUMBER()OVER(PARTITIONBYsi.category_idORDERBYSUM(od.sku_num)DESC)rank_numFROMorder_detail odJOINsku_info siONod.sku_idsi.sku_idJOINcategory_info ciONsi.category_idci.category_idWHEREyear(od.create_date)2021GROUPBYci.category_name,si.category_id,od.sku_id,si.name)tWHERErank_num3;1.3 本章总结1.3.1 窗口函数 vs 聚合函数特性聚合函数窗口函数输出行数压缩为一行保持原始行数GROUP BY必须可选PARTITION BY排序无支持ORDER BY范围控制无支持ROWS/RANGE典型场景汇总统计排名、累计、趋势1.3.2 常见面试题ROW_NUMBER和RANK的区别ROW_NUMBER连续唯一RANK跳跃排名。如何计算7日移动平均AVG OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)如何统计连续登录天数日期 - ROW_NUMBER编号差值相同即为连续。LAG和LEAD的区别LAG取前N行LEAD取后N行。窗口函数的执行顺序在WHERE、GROUP BY、HAVING之后ORDER BY之前执行。1.3.3 学习路径建议初级函数 → 窗口函数 → 高级优化 → 实际项目 ↓ ↓ ↓ ↓ 条件聚合 排序取值 性能调优 业务建模 类型转换 累计计算 执行计划 数据治理
