1.前言大家在写 SQL 的时候应该会发现group by 和开窗函数是两个非常高频的能力。group by 负责聚合开窗函数负责在当前结果集上做排序、累计、取前后行、分组排名等分析。单独看它们都不难但一旦放在一起使用很多同学就容易混淆group by 和窗口函数到底谁先执行为什么可以写 row_number() over(order by sum(pay_amount) desc)为什么有时候要写 sum(sum(pay_amount)) over(...)为什么 ntile(5) 不等于累计金额前 20%为什么明细表上直接开窗排名会算错这篇文章就围绕这些问题系统聊一下 group by 和开窗函数的结合使用2.group by 和开窗函数的执行顺序首先分组和窗口函数是可以一起使用的而在执行顺序上GROUP BY通常会首先执行然后窗口函数在分组后的结果上操作。SELECT uid, SUM(IF(submit_time IS NULL, 1, 0))/COUNT(*) AS incomplete_rate, ROW_NUMBER() OVER (ORDER BY SUM(IF(submit_time IS NULL, 1, 0))/COUNT(*) DESC) AS ranking FROM exam_record GROUP BY uid上述代码就是先按照uid进行分组然后为每个uid的组打上序号。因为ROW_NUMBER中没有限制分组所以默认将所有数据作为一个大窗口进行操作排序字段是一个聚合值打印一下执行计划看看 Physical Plan AdaptiveSparkPlan isFinalPlanfalse - Project [uid#55, incomplete_rate#44, ranking#45] - Window [row_number() windowspecdefinition(_w0#62 DESC NULLS LAST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS ranking#45], [_w0#62 DESC NULLS LAST] - Sort [_w0#62 DESC NULLS LAST], false, 0 - Exchange SinglePartition, ENSURE_REQUIREMENTS, [id#48] - HashAggregate(keys[uid#55], functions[sum(if (isnull(submit_time#58)) 1 else 0), count(1)]) - Exchange hashpartitioning(uid#55, 8192), ENSURE_REQUIREMENTS, [id#45] - HashAggregate(keys[uid#55], functions[partial_sum(if (isnull(submit_time#58)) 1 else 0), partial_count(1)]) - FileScan orc default.exam_record[uid#55,submit_time#58] Batched: true, DataFilters: [], Format: ORC, Location: InMemoryFileIndex(1 paths)[hdfs://ds/user/hive/warehouse/exam_record], PartitionFilters: [], PushedFilters: [], ReadSchema: structuid:int,submit_time:string这个物理执行计划展示了一个 Hive SQL 查询在 Spark 引擎下的执行步骤从下往上分析如下FileScan从 HDFS 上的 ORC 文件中读取default.exam_record表的数据只读取uid和submit_time列。HashAggregate (partial)进行部分聚合操作计算每个uid对应的未提交记录数partial_sum(if (isnull(submit_time)) 1 else 0)和总记录数partial_count(1)。Exchange (hashpartitioning)对数据进行哈希分区将相同uid的数据分发到同一个节点上以便后续的全局聚合。HashAggregate进行全局聚合操作汇总每个uid的未提交记录数和总记录数。Exchange (SinglePartition)将所有数据收集到一个分区中以便后续的排序操作。Sort按照_w0列进行降序排序。Window使用窗口函数row_number()为排序后的数据添加排名信息。Project选择最终需要的列即uid、incomplete_rate和ranking。3.基础知识点小结❝谨记窗口函数不改变原数据表解构在当前表上的滑动计算可以分区、排序甚至可以和窗口函数嵌套使用窗口函数可以和聚合函数嵌套使用但是请注意执行顺序 先分组---组内做聚合运算---聚合后对当前表进行窗口函数。此外窗口函数通常写在SELECT的最后位置但是写到中间SQL语法也不会报错的哈。❝应用Case1统计每月活跃、新增、历史最大新增、累计用户数问题请输出自从有用户作答记录以来每月的试卷作答记录中月活用户数、每月的试卷作答记录中新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数结果按月份升序输出。-- 分析 -- 1.自从有用户作答记录以来 就是从上述记录中进行查询 -- 2.每月的试卷作答记录中月活用户数 按照年-月进行分组组内的用户数(Distinct uid) -- 3.新增用户数 按照年-月进行分组组内首次出现的uid个数 -- 4.截止当月的单月最大新增用户数 截至当月就是按照月份进行排序然后整个数据视为一个窗口 -- 5.截止当月的累积用户数 按月分组截至当月的DISTINCT uid总数 -- 6.需要用到的字段就只有uid和start_time然后COUNT(DISTINCT uid)就可以求出月活用户数 -- 7.新增用户数我们需要单独计算 -- 7.1.使用窗口函数按照uid进行分组统计每个组内的min(start_time)这样就会有一行属性来专门记录每个用户对应的第一次登陆的日期然后将start_time和改行日期进行比较相等的话就是第一次登陆uid否则就不是 -- 8.有了新增用户数行之后就直接在这个基础上进行SELETC就可以了注意GROUP BY和窗口函数的联合使用 WITH A AS( SELECT uid ,DATE_FORMAT(start_time, yy-MM) AS times ,IF(start_time MIN(start_time) OVER (PARTITION BY uid), 1, 0) AS new_uid FROM table ) SELECT COUNT(DISTINCT uid) AS mau ,SUM(new_uid) AS month_add_uv -- 先确定窗口和范围再确定我们要求和在窗口内的计算函数 ,MAX(SUM(new_uid)) OVER (ORDER BY times ROWS BETWEEN unbounded preceding AND current row) AS max_month_add_uv, -- 这里也是先确定窗口范围和排序顺序然后再确定基于当前窗口的计算函数 ,sum(sum(is_first)) over(order by times rows between unbounded preceding and current row) as cum_sum_uv --用每个月新增人数累加求和就是当前累计用户数 FROM A GROUP BY times ORDER BY times❝应用Case2找出支付额度排名前 20% 的用户题目user_sales_table有如下字段 user_name用户名pay_amount用户支付额度要求找出支付额度在累计前20%的用户-- 思路 -- 1.按照用户进行分组统计每个组内的用户总支付额度然后取前20% -- 2.GROUP BYuser_name进行分组 -- 3.然后使用窗口函数ntile为每个组打上百分比记录这个时候窗口函数中ORDER BY后面必须要跟聚合函数 -- 3.1.因为我们是先执行了GROUP BY分组函数再去执行窗口函数此时数据已经按照GROUP BY分组字段进行了分组所以窗口函数中出现的字段要么就是分组键要么就得是聚合函数因为此时【窗口函数的粒度已经变成了组】而不是原来的行 WITH A AS ( SELECT user_name ,ntile(5) OVER (ORDER BY SUM(pay_amount) DESC) AS percent FROM user_sales_table GROUP BY user_name ) SELECT user_name FROM A WHERE percent 1;总结group by 和开窗函数结合使用本质是先聚合再分析group by 负责把明细数据聚合到业务需要的粒度比如用户粒度、月份粒度、商品粒度。开窗函数负责在聚合后的结果上继续做排名、累计、分桶等分析。写这类 SQL 时不要一上来就套函数而是先想清楚三个问题第一最终结果是什么粒度第二排序或累计的指标是不是需要先聚合第三窗口函数是在全局范围内算还是在每个分组内算只要这三个问题想清楚SQL 结构就很清晰明细数据 → group by 聚合到业务粒度 → over 在聚合结果上做窗口分析