文章目录一、分类总览1、函数分类2、全部函数二、窗口函数基础1、定义2、区别3、简单记忆4、案例三、聚合统计类1、定义2、区别3、简单记忆4、案例四、排名 / 分组类1、定义2、区别3、简单记忆4、案例五、前后取值类1、定义2、区别3、简单记忆4、案例六、分布 / 百分位类1、定义2、区别3、简单记忆4、案例七、随机抽样类1、定义2、区别3、简单记忆4、案例八、最容易混淆的函数对比九、常用案例1、分组取最新一条2、按多个字段去重3、累计求和4、计算环比5、获取下一条记录6、分组 TOP N十、参考资料一、分类总览1、函数分类分类主要解决的问题代表函数1、聚合统计类在窗口内求和、均值、最大值、最小值、中位数、标准差SUM、AVG、COUNT、MAX、MIN、MEDIAN、STDDEV、STDDEV_SAMP2、排名 / 分组类对分区内数据编号、排名、分桶ROW_NUMBER、RANK、DENSE_RANK、NTILE3、前后取值类获取第一条、最后一条、第 N 条、前后 N 行FIRST_VALUE、LAST_VALUE、NTH_VALUE、LAG、LEAD4、分布 / 百分位类计算累计分布、百分比排名、百分位CUME_DIST、PERCENT_RANK、PERCENTILE_CONT、PERCENTILE_DISC5、随机抽样类在窗口 / 分区内随机抽样CLUSTER_SAMPLE2、全部函数分类函数功能1、聚合统计类AVG计算窗口内平均值1、聚合统计类COUNT计算窗口内记录数1、聚合统计类MAX计算窗口内最大值1、聚合统计类MEDIAN计算窗口内中位数1、聚合统计类MIN计算窗口内最小值1、聚合统计类STDDEV计算窗口内总体标准差1、聚合统计类STDDEV_SAMP计算窗口内样本标准差1、聚合统计类SUM计算窗口内总和2、排名 / 分组类DENSE_RANK连续排名相同值同名次且不跳号2、排名 / 分组类NTILE将有序数据切成 N 份并返回分组编号2、排名 / 分组类RANK排名相同值同名次但可能跳号2、排名 / 分组类ROW_NUMBER为分区内每行生成唯一行号3、前后取值类FIRST_VALUE获取当前窗口第一条数据的值3、前后取值类LAG获取当前行之前第 N 行的值3、前后取值类LAST_VALUE获取当前窗口最后一条数据的值3、前后取值类LEAD获取当前行之后第 N 行的值3、前后取值类NTH_VALUE获取当前窗口第 N 条数据的值4、分布 / 百分位类CUME_DIST计算累计分布4、分布 / 百分位类PERCENT_RANK计算百分比排名4、分布 / 百分位类PERCENTILE_CONT计算连续型精确百分位可插值4、分布 / 百分位类PERCENTILE_DISC计算离散型百分位返回实际值5、随机抽样类CLUSTER_SAMPLE在窗口内随机抽样返回是否被抽中二、窗口函数基础关键字功能OVER声明窗口函数PARTITION BY将数据划分为多个独立分区ORDER BY定义分区内的排序顺序ROWS按行数定义窗口范围RANGE按排序字段的值范围定义窗口GROUPS按相同排序值组成的组定义窗口PRECEDING当前行之前FOLLOWING当前行之后CURRENT ROW当前行UNBOUNDED PRECEDING分区起点UNBOUNDED FOLLOWING分区终点1、定义窗口函数会在当前行对应的“窗口”中进行计算同时保留原始明细行。基本结构函数(...)OVER(PARTITIONBY分组字段ORDERBY排序字段ROWSBETWEEN起点AND终点)2、区别关键字核心含义PARTITION BY决定“和谁一起算”ORDER BY决定“先后顺序”ROWS按具体行数确定范围RANGE按排序字段值的范围确定窗口GROUPS按相同排序值形成的组确定窗口窗口函数与普通GROUP BY最大区别GROUP BY会把多行聚合为更少的行窗口函数保留原始行只在每行后面增加计算结果。官方使用限制窗口函数只能出现在SELECT中。窗口函数内部不能嵌套窗口函数和聚合函数。窗口函数不能与同级别聚合函数直接混用。FILTER只适用于聚合类窗口函数例如COUNT、SUM、AVG、MAX、MIN等。3、简单记忆PARTITION BY 分组。ORDER BY 排序。ROWS 按行。RANGE 按值范围。GROUPS 按同值组。OVER 在这个窗口上计算。4、案例-- 结果每个部门内部按工资升序计算累计工资SELECTdeptno,ename,sal,SUM(sal)OVER(PARTITIONBYdeptnoORDERBYsalROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASrunning_salFROMemp;-- 结果只让 value 100 的记录参与窗口 SUM 计算SELECTkey,value,SUM(value)FILTER(WHEREvalue100)OVER(PARTITIONBYkeyORDERBYvalue)AStotal_valueFROMmf_window_fun;三、聚合统计类函数功能SUM窗口求和AVG窗口求平均值COUNT窗口计数MAX窗口最大值MIN窗口最小值MEDIAN窗口中位数STDDEV窗口总体标准差STDDEV_SAMP窗口样本标准差1、定义这一类与普通聚合函数的计算逻辑基本一致但增加OVER(...)后不会压缩明细行。2、区别函数核心区别SUM总和AVG平均值COUNT数量MAX最大值MIN最小值MEDIAN中位数STDDEV总体标准差STDDEV_SAMP样本标准差最容易混淆对比区别普通SUMvs 窗口SUM前者通常减少行数后者保留明细AVGvsMEDIAN平均值 vs 中位数STDDEVvsSTDDEV_SAMP总体标准差 vs 样本标准差3、简单记忆SUM加起来。AVG平均。COUNT数数量。MAX / MIN最大 / 最小。MEDIAN找中间。STDDEV总体标准差。STDDEV_SAMP样本标准差。4、案例-- 结果每行显示所在部门的工资总额SELECTdeptno,ename,sal,SUM(sal)OVER(PARTITIONBYdeptno)ASdept_sumFROMemp;-- 结果每行显示所在部门的平均工资SELECTdeptno,ename,sal,AVG(sal)OVER(PARTITIONBYdeptno)ASdept_avgFROMemp;-- 结果每行显示所在部门的员工数量SELECTdeptno,ename,COUNT(*)OVER(PARTITIONBYdeptno)ASdept_cntFROMemp;-- 结果每行显示所在部门的最高工资SELECTdeptno,ename,sal,MAX(sal)OVER(PARTITIONBYdeptno)ASdept_maxFROMemp;-- 结果每行显示所在部门的最低工资SELECTdeptno,ename,sal,MIN(sal)OVER(PARTITIONBYdeptno)ASdept_minFROMemp;-- 结果每行显示所在部门的工资中位数SELECTdeptno,ename,sal,MEDIAN(sal)OVER(PARTITIONBYdeptno)ASdept_medianFROMemp;-- 结果每行显示所在部门工资的总体标准差SELECTdeptno,ename,sal,STDDEV(sal)OVER(PARTITIONBYdeptno)ASdept_stddevFROMemp;-- 结果每行显示所在部门工资的样本标准差SELECTdeptno,ename,sal,STDDEV_SAMP(sal)OVER(PARTITIONBYdeptno)ASdept_stddev_sampFROMemp;四、排名 / 分组类函数功能ROW_NUMBER唯一连续编号RANK并列排名之后跳号DENSE_RANK并列排名之后不跳号NTILE按顺序切成 N 组1、定义这一类都依赖ORDER BY的排序结果对分区内数据进行编号、排名或分桶。2、区别假设排序结果为100 100 90 80值ROW_NUMBERRANKDENSE_RANK1001111002119033280443函数相同值处理是否跳号ROW_NUMBER不并列不存在并列RANK并列跳号DENSE_RANK并列不跳号NTILE不排名切成 N 组3、简单记忆ROW_NUMBER 每行唯一编号。RANK 并列后跳号。DENSE_RANK 排名很密不跳号。NTILE 切成 N 份。4、案例-- 结果部门内工资从高到低生成唯一序号 1、2、3...SELECTdeptno,ename,sal,ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASrnFROMemp;-- 结果相同工资并列后续可能为 1、1、3SELECTdeptno,ename,sal,RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASrkFROMemp;-- 结果相同工资并列后续为 1、1、2SELECTdeptno,ename,sal,DENSE_RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASdense_rkFROMemp;-- 结果部门内按工资从高到低切成 3 组SELECTdeptno,ename,sal,NTILE(3)OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASsalary_groupFROMemp;五、前后取值类函数功能FIRST_VALUE当前窗口第一条值LAST_VALUE当前窗口最后一条值NTH_VALUE当前窗口第 N 条值LAG当前行之前第 N 行LEAD当前行之后第 N 行1、定义这一类根据窗口排序从其他行获取对应字段值。2、区别函数参照对象FIRST_VALUE当前窗口第一行LAST_VALUE当前窗口最后一行NTH_VALUE当前窗口第 N 行LAG相对当前行往前LEAD相对当前行往后核心区别FIRST_VALUE / LAST_VALUE / NTH_VALUE是看“窗口里的固定位置”LAG / LEAD是看“当前行前后偏移”。3、简单记忆FIRST_VALUE第一条。LAST_VALUE最后一条。NTH_VALUE第 N 条。LAG往前看。LEAD往后看。4、案例-- 结果返回部门内按工资排序后的第一条工资SELECTdeptno,ename,sal,FIRST_VALUE(sal)OVER(PARTITIONBYdeptnoORDERBYsal)ASfirst_salFROMemp;-- 结果返回部门内按工资排序后的最后一条工资SELECTdeptno,ename,sal,LAST_VALUE(sal)OVER(PARTITIONBYdeptnoORDERBYsalROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASlast_salFROMemp;-- 结果返回部门窗口中的第 2 条工资SELECTdeptno,ename,sal,NTH_VALUE(sal,2)OVER(PARTITIONBYdeptnoORDERBYsalROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASsecond_salFROMemp;-- 结果返回当前员工前一行工资第一行返回 NULLSELECTdeptno,ename,sal,LAG(sal,1)OVER(PARTITIONBYdeptnoORDERBYsal)ASprev_salFROMemp;-- 结果返回当前员工后一行工资最后一行返回 NULLSELECTdeptno,ename,sal,LEAD(sal,1)OVER(PARTITIONBYdeptnoORDERBYsal)ASnext_salFROMemp;六、分布 / 百分位类函数功能CUME_DIST累计分布PERCENT_RANK百分比排名PERCENTILE_CONT连续型精确百分位PERCENTILE_DISC离散型百分位1、定义这一类用于分析一条数据在整体分布中的位置或计算 P50、P90、P95 等百分位。2、区别函数核心含义CUME_DIST当前值及之前累计占整体的比例PERCENT_RANK当前排名在整体中的百分比位置PERCENTILE_CONT连续型百分位可通过插值产生不存在于原数据中的值PERCENTILE_DISC离散型百分位结果一定来自实际数据例如数据1, 2, 3, 4P50函数结果特点PERCENTILE_CONT可以得到2.5PERCENTILE_DISC返回实际存在的数据值3、简单记忆CUME_DIST 累计占比。PERCENT_RANK 排名百分比。CONT Continuous可以插值。DISC Discrete只拿实际值。4、案例-- 结果返回每名员工工资的累计分布比例SELECTdeptno,ename,sal,CUME_DIST()OVER(PARTITIONBYdeptnoORDERBYsal)AScume_distFROMemp;-- 结果返回每名员工工资的百分比排名SELECTdeptno,ename,sal,PERCENT_RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASpct_rankFROMemp;-- 结果返回部门工资精确 P50可能产生插值SELECTdeptno,ename,sal,PERCENTILE_CONT(sal,0.5)OVER(PARTITIONBYdeptno)ASp50FROMemp;-- 结果返回部门工资离散 P50结果来自实际工资值SELECTdeptno,ename,sal,PERCENTILE_DISC(sal,0.5)OVER(PARTITIONBYdeptno)ASp50FROMemp;七、随机抽样类函数功能CLUSTER_SAMPLE在窗口 / 分区内随机抽样1、定义CLUSTER_SAMPLE用于对窗口内数据进行随机抽样返回BOOLEANTRUE当前行被抽中。FALSE当前行未被抽中。2、区别它与排名后取前 N 条的最大区别对比区别ROW_NUMBER按明确排序规则确定性取数CLUSTER_SAMPLE随机抽样3、简单记忆CLUSTER_SAMPLE 分区内随机抽样。4、案例-- 结果每个部门随机抽样被抽中的行返回 TRUESELECTdeptno,ename,CLUSTER_SAMPLE(1)OVER(PARTITIONBYdeptno)ASis_sampledFROMemp;八、最容易混淆的函数对比函数组合最核心区别ROW_NUMBERvsRANK唯一编号 vs 相同值并列排名RANKvsDENSE_RANK并列后跳号 vs 并列后不跳号ROW_NUMBERvsNTILE每行编号 vs 将数据切成 N 组LAGvsLEAD往前取值 vs 往后取值FIRST_VALUEvsLAG窗口第一条 vs 当前行前 N 条LAST_VALUEvsLEAD窗口最后一条 vs 当前行后 N 条NTH_VALUEvsLAG / LEAD窗口固定位置 vs 相对当前行偏移SUM OVERvsGROUP BY SUM保留明细行 vs 聚合减少行数AVGvsMEDIAN平均值 vs 中位数STDDEVvsSTDDEV_SAMP总体标准差 vs 样本标准差CUME_DISTvsPERCENT_RANK累计分布比例 vs 排名百分比PERCENTILE_CONTvsPERCENTILE_DISC可插值 vs 只返回实际值ROWSvsRANGE按行数范围 vs 按排序字段值范围RANGEvsGROUPS按值范围 vs 按相同排序值组成的组ROW_NUMBERvsCLUSTER_SAMPLE确定性排序编号 vs 随机抽样九、常用案例1、分组取最新一条-- 结果每个 shop_id 只保留 etl_time 最新的一条SELECT*FROM(SELECTt.*,ROW_NUMBER()OVER(PARTITIONBYshop_idORDERBYetl_timeDESC)ASrnFROMsource_table t)aWHERErn1;2、按多个字段去重-- 结果按 start_date、end_date、city 去重每组保留 ds 最新的一条SELECT*FROM(SELECTt.*,ROW_NUMBER()OVER(PARTITIONBYTRIM(start_date),TRIM(end_date),TRIM(city)ORDERBYdsDESC)ASrnFROMsource_table t)aWHERErn1;3、累计求和-- 结果每个门店按日期计算累计销售额SELECTshop_id,biz_date,amount,SUM(amount)OVER(PARTITIONBYshop_idORDERBYbiz_dateROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AStotal_amountFROMsales;4、计算环比-- 结果获取前一天金额并计算与当前金额差值SELECTshop_id,biz_date,amount,LAG(amount,1)OVER(PARTITIONBYshop_idORDERBYbiz_date)ASprev_amount,amount-LAG(amount,1)OVER(PARTITIONBYshop_idORDERBYbiz_date)ASdiff_amountFROMsales;5、获取下一条记录-- 结果获取同一门店的下一条业务日期SELECTshop_id,biz_date,LEAD(biz_date,1)OVER(PARTITIONBYshop_idORDERBYbiz_date)ASnext_dateFROMsales;6、分组 TOP N-- 结果每个城市销售额最高的前 3 个门店SELECT*FROM(SELECTcity,shop_id,amount,ROW_NUMBER()OVER(PARTITIONBYcityORDERBYamountDESC)ASrnFROMshop_sales)tWHERErn3;十、参考资料阿里云 MaxCompute 官方文档https://help.aliyun.com/zh/maxcompute/window-functions-1
MaxCompute中窗口函数
文章目录一、分类总览1、函数分类2、全部函数二、窗口函数基础1、定义2、区别3、简单记忆4、案例三、聚合统计类1、定义2、区别3、简单记忆4、案例四、排名 / 分组类1、定义2、区别3、简单记忆4、案例五、前后取值类1、定义2、区别3、简单记忆4、案例六、分布 / 百分位类1、定义2、区别3、简单记忆4、案例七、随机抽样类1、定义2、区别3、简单记忆4、案例八、最容易混淆的函数对比九、常用案例1、分组取最新一条2、按多个字段去重3、累计求和4、计算环比5、获取下一条记录6、分组 TOP N十、参考资料一、分类总览1、函数分类分类主要解决的问题代表函数1、聚合统计类在窗口内求和、均值、最大值、最小值、中位数、标准差SUM、AVG、COUNT、MAX、MIN、MEDIAN、STDDEV、STDDEV_SAMP2、排名 / 分组类对分区内数据编号、排名、分桶ROW_NUMBER、RANK、DENSE_RANK、NTILE3、前后取值类获取第一条、最后一条、第 N 条、前后 N 行FIRST_VALUE、LAST_VALUE、NTH_VALUE、LAG、LEAD4、分布 / 百分位类计算累计分布、百分比排名、百分位CUME_DIST、PERCENT_RANK、PERCENTILE_CONT、PERCENTILE_DISC5、随机抽样类在窗口 / 分区内随机抽样CLUSTER_SAMPLE2、全部函数分类函数功能1、聚合统计类AVG计算窗口内平均值1、聚合统计类COUNT计算窗口内记录数1、聚合统计类MAX计算窗口内最大值1、聚合统计类MEDIAN计算窗口内中位数1、聚合统计类MIN计算窗口内最小值1、聚合统计类STDDEV计算窗口内总体标准差1、聚合统计类STDDEV_SAMP计算窗口内样本标准差1、聚合统计类SUM计算窗口内总和2、排名 / 分组类DENSE_RANK连续排名相同值同名次且不跳号2、排名 / 分组类NTILE将有序数据切成 N 份并返回分组编号2、排名 / 分组类RANK排名相同值同名次但可能跳号2、排名 / 分组类ROW_NUMBER为分区内每行生成唯一行号3、前后取值类FIRST_VALUE获取当前窗口第一条数据的值3、前后取值类LAG获取当前行之前第 N 行的值3、前后取值类LAST_VALUE获取当前窗口最后一条数据的值3、前后取值类LEAD获取当前行之后第 N 行的值3、前后取值类NTH_VALUE获取当前窗口第 N 条数据的值4、分布 / 百分位类CUME_DIST计算累计分布4、分布 / 百分位类PERCENT_RANK计算百分比排名4、分布 / 百分位类PERCENTILE_CONT计算连续型精确百分位可插值4、分布 / 百分位类PERCENTILE_DISC计算离散型百分位返回实际值5、随机抽样类CLUSTER_SAMPLE在窗口内随机抽样返回是否被抽中二、窗口函数基础关键字功能OVER声明窗口函数PARTITION BY将数据划分为多个独立分区ORDER BY定义分区内的排序顺序ROWS按行数定义窗口范围RANGE按排序字段的值范围定义窗口GROUPS按相同排序值组成的组定义窗口PRECEDING当前行之前FOLLOWING当前行之后CURRENT ROW当前行UNBOUNDED PRECEDING分区起点UNBOUNDED FOLLOWING分区终点1、定义窗口函数会在当前行对应的“窗口”中进行计算同时保留原始明细行。基本结构函数(...)OVER(PARTITIONBY分组字段ORDERBY排序字段ROWSBETWEEN起点AND终点)2、区别关键字核心含义PARTITION BY决定“和谁一起算”ORDER BY决定“先后顺序”ROWS按具体行数确定范围RANGE按排序字段值的范围确定窗口GROUPS按相同排序值形成的组确定窗口窗口函数与普通GROUP BY最大区别GROUP BY会把多行聚合为更少的行窗口函数保留原始行只在每行后面增加计算结果。官方使用限制窗口函数只能出现在SELECT中。窗口函数内部不能嵌套窗口函数和聚合函数。窗口函数不能与同级别聚合函数直接混用。FILTER只适用于聚合类窗口函数例如COUNT、SUM、AVG、MAX、MIN等。3、简单记忆PARTITION BY 分组。ORDER BY 排序。ROWS 按行。RANGE 按值范围。GROUPS 按同值组。OVER 在这个窗口上计算。4、案例-- 结果每个部门内部按工资升序计算累计工资SELECTdeptno,ename,sal,SUM(sal)OVER(PARTITIONBYdeptnoORDERBYsalROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASrunning_salFROMemp;-- 结果只让 value 100 的记录参与窗口 SUM 计算SELECTkey,value,SUM(value)FILTER(WHEREvalue100)OVER(PARTITIONBYkeyORDERBYvalue)AStotal_valueFROMmf_window_fun;三、聚合统计类函数功能SUM窗口求和AVG窗口求平均值COUNT窗口计数MAX窗口最大值MIN窗口最小值MEDIAN窗口中位数STDDEV窗口总体标准差STDDEV_SAMP窗口样本标准差1、定义这一类与普通聚合函数的计算逻辑基本一致但增加OVER(...)后不会压缩明细行。2、区别函数核心区别SUM总和AVG平均值COUNT数量MAX最大值MIN最小值MEDIAN中位数STDDEV总体标准差STDDEV_SAMP样本标准差最容易混淆对比区别普通SUMvs 窗口SUM前者通常减少行数后者保留明细AVGvsMEDIAN平均值 vs 中位数STDDEVvsSTDDEV_SAMP总体标准差 vs 样本标准差3、简单记忆SUM加起来。AVG平均。COUNT数数量。MAX / MIN最大 / 最小。MEDIAN找中间。STDDEV总体标准差。STDDEV_SAMP样本标准差。4、案例-- 结果每行显示所在部门的工资总额SELECTdeptno,ename,sal,SUM(sal)OVER(PARTITIONBYdeptno)ASdept_sumFROMemp;-- 结果每行显示所在部门的平均工资SELECTdeptno,ename,sal,AVG(sal)OVER(PARTITIONBYdeptno)ASdept_avgFROMemp;-- 结果每行显示所在部门的员工数量SELECTdeptno,ename,COUNT(*)OVER(PARTITIONBYdeptno)ASdept_cntFROMemp;-- 结果每行显示所在部门的最高工资SELECTdeptno,ename,sal,MAX(sal)OVER(PARTITIONBYdeptno)ASdept_maxFROMemp;-- 结果每行显示所在部门的最低工资SELECTdeptno,ename,sal,MIN(sal)OVER(PARTITIONBYdeptno)ASdept_minFROMemp;-- 结果每行显示所在部门的工资中位数SELECTdeptno,ename,sal,MEDIAN(sal)OVER(PARTITIONBYdeptno)ASdept_medianFROMemp;-- 结果每行显示所在部门工资的总体标准差SELECTdeptno,ename,sal,STDDEV(sal)OVER(PARTITIONBYdeptno)ASdept_stddevFROMemp;-- 结果每行显示所在部门工资的样本标准差SELECTdeptno,ename,sal,STDDEV_SAMP(sal)OVER(PARTITIONBYdeptno)ASdept_stddev_sampFROMemp;四、排名 / 分组类函数功能ROW_NUMBER唯一连续编号RANK并列排名之后跳号DENSE_RANK并列排名之后不跳号NTILE按顺序切成 N 组1、定义这一类都依赖ORDER BY的排序结果对分区内数据进行编号、排名或分桶。2、区别假设排序结果为100 100 90 80值ROW_NUMBERRANKDENSE_RANK1001111002119033280443函数相同值处理是否跳号ROW_NUMBER不并列不存在并列RANK并列跳号DENSE_RANK并列不跳号NTILE不排名切成 N 组3、简单记忆ROW_NUMBER 每行唯一编号。RANK 并列后跳号。DENSE_RANK 排名很密不跳号。NTILE 切成 N 份。4、案例-- 结果部门内工资从高到低生成唯一序号 1、2、3...SELECTdeptno,ename,sal,ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASrnFROMemp;-- 结果相同工资并列后续可能为 1、1、3SELECTdeptno,ename,sal,RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASrkFROMemp;-- 结果相同工资并列后续为 1、1、2SELECTdeptno,ename,sal,DENSE_RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASdense_rkFROMemp;-- 结果部门内按工资从高到低切成 3 组SELECTdeptno,ename,sal,NTILE(3)OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASsalary_groupFROMemp;五、前后取值类函数功能FIRST_VALUE当前窗口第一条值LAST_VALUE当前窗口最后一条值NTH_VALUE当前窗口第 N 条值LAG当前行之前第 N 行LEAD当前行之后第 N 行1、定义这一类根据窗口排序从其他行获取对应字段值。2、区别函数参照对象FIRST_VALUE当前窗口第一行LAST_VALUE当前窗口最后一行NTH_VALUE当前窗口第 N 行LAG相对当前行往前LEAD相对当前行往后核心区别FIRST_VALUE / LAST_VALUE / NTH_VALUE是看“窗口里的固定位置”LAG / LEAD是看“当前行前后偏移”。3、简单记忆FIRST_VALUE第一条。LAST_VALUE最后一条。NTH_VALUE第 N 条。LAG往前看。LEAD往后看。4、案例-- 结果返回部门内按工资排序后的第一条工资SELECTdeptno,ename,sal,FIRST_VALUE(sal)OVER(PARTITIONBYdeptnoORDERBYsal)ASfirst_salFROMemp;-- 结果返回部门内按工资排序后的最后一条工资SELECTdeptno,ename,sal,LAST_VALUE(sal)OVER(PARTITIONBYdeptnoORDERBYsalROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASlast_salFROMemp;-- 结果返回部门窗口中的第 2 条工资SELECTdeptno,ename,sal,NTH_VALUE(sal,2)OVER(PARTITIONBYdeptnoORDERBYsalROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASsecond_salFROMemp;-- 结果返回当前员工前一行工资第一行返回 NULLSELECTdeptno,ename,sal,LAG(sal,1)OVER(PARTITIONBYdeptnoORDERBYsal)ASprev_salFROMemp;-- 结果返回当前员工后一行工资最后一行返回 NULLSELECTdeptno,ename,sal,LEAD(sal,1)OVER(PARTITIONBYdeptnoORDERBYsal)ASnext_salFROMemp;六、分布 / 百分位类函数功能CUME_DIST累计分布PERCENT_RANK百分比排名PERCENTILE_CONT连续型精确百分位PERCENTILE_DISC离散型百分位1、定义这一类用于分析一条数据在整体分布中的位置或计算 P50、P90、P95 等百分位。2、区别函数核心含义CUME_DIST当前值及之前累计占整体的比例PERCENT_RANK当前排名在整体中的百分比位置PERCENTILE_CONT连续型百分位可通过插值产生不存在于原数据中的值PERCENTILE_DISC离散型百分位结果一定来自实际数据例如数据1, 2, 3, 4P50函数结果特点PERCENTILE_CONT可以得到2.5PERCENTILE_DISC返回实际存在的数据值3、简单记忆CUME_DIST 累计占比。PERCENT_RANK 排名百分比。CONT Continuous可以插值。DISC Discrete只拿实际值。4、案例-- 结果返回每名员工工资的累计分布比例SELECTdeptno,ename,sal,CUME_DIST()OVER(PARTITIONBYdeptnoORDERBYsal)AScume_distFROMemp;-- 结果返回每名员工工资的百分比排名SELECTdeptno,ename,sal,PERCENT_RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC)ASpct_rankFROMemp;-- 结果返回部门工资精确 P50可能产生插值SELECTdeptno,ename,sal,PERCENTILE_CONT(sal,0.5)OVER(PARTITIONBYdeptno)ASp50FROMemp;-- 结果返回部门工资离散 P50结果来自实际工资值SELECTdeptno,ename,sal,PERCENTILE_DISC(sal,0.5)OVER(PARTITIONBYdeptno)ASp50FROMemp;七、随机抽样类函数功能CLUSTER_SAMPLE在窗口 / 分区内随机抽样1、定义CLUSTER_SAMPLE用于对窗口内数据进行随机抽样返回BOOLEANTRUE当前行被抽中。FALSE当前行未被抽中。2、区别它与排名后取前 N 条的最大区别对比区别ROW_NUMBER按明确排序规则确定性取数CLUSTER_SAMPLE随机抽样3、简单记忆CLUSTER_SAMPLE 分区内随机抽样。4、案例-- 结果每个部门随机抽样被抽中的行返回 TRUESELECTdeptno,ename,CLUSTER_SAMPLE(1)OVER(PARTITIONBYdeptno)ASis_sampledFROMemp;八、最容易混淆的函数对比函数组合最核心区别ROW_NUMBERvsRANK唯一编号 vs 相同值并列排名RANKvsDENSE_RANK并列后跳号 vs 并列后不跳号ROW_NUMBERvsNTILE每行编号 vs 将数据切成 N 组LAGvsLEAD往前取值 vs 往后取值FIRST_VALUEvsLAG窗口第一条 vs 当前行前 N 条LAST_VALUEvsLEAD窗口最后一条 vs 当前行后 N 条NTH_VALUEvsLAG / LEAD窗口固定位置 vs 相对当前行偏移SUM OVERvsGROUP BY SUM保留明细行 vs 聚合减少行数AVGvsMEDIAN平均值 vs 中位数STDDEVvsSTDDEV_SAMP总体标准差 vs 样本标准差CUME_DISTvsPERCENT_RANK累计分布比例 vs 排名百分比PERCENTILE_CONTvsPERCENTILE_DISC可插值 vs 只返回实际值ROWSvsRANGE按行数范围 vs 按排序字段值范围RANGEvsGROUPS按值范围 vs 按相同排序值组成的组ROW_NUMBERvsCLUSTER_SAMPLE确定性排序编号 vs 随机抽样九、常用案例1、分组取最新一条-- 结果每个 shop_id 只保留 etl_time 最新的一条SELECT*FROM(SELECTt.*,ROW_NUMBER()OVER(PARTITIONBYshop_idORDERBYetl_timeDESC)ASrnFROMsource_table t)aWHERErn1;2、按多个字段去重-- 结果按 start_date、end_date、city 去重每组保留 ds 最新的一条SELECT*FROM(SELECTt.*,ROW_NUMBER()OVER(PARTITIONBYTRIM(start_date),TRIM(end_date),TRIM(city)ORDERBYdsDESC)ASrnFROMsource_table t)aWHERErn1;3、累计求和-- 结果每个门店按日期计算累计销售额SELECTshop_id,biz_date,amount,SUM(amount)OVER(PARTITIONBYshop_idORDERBYbiz_dateROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AStotal_amountFROMsales;4、计算环比-- 结果获取前一天金额并计算与当前金额差值SELECTshop_id,biz_date,amount,LAG(amount,1)OVER(PARTITIONBYshop_idORDERBYbiz_date)ASprev_amount,amount-LAG(amount,1)OVER(PARTITIONBYshop_idORDERBYbiz_date)ASdiff_amountFROMsales;5、获取下一条记录-- 结果获取同一门店的下一条业务日期SELECTshop_id,biz_date,LEAD(biz_date,1)OVER(PARTITIONBYshop_idORDERBYbiz_date)ASnext_dateFROMsales;6、分组 TOP N-- 结果每个城市销售额最高的前 3 个门店SELECT*FROM(SELECTcity,shop_id,amount,ROW_NUMBER()OVER(PARTITIONBYcityORDERBYamountDESC)ASrnFROMshop_sales)tWHERErn3;十、参考资料阿里云 MaxCompute 官方文档https://help.aliyun.com/zh/maxcompute/window-functions-1
