☰

企业官网建设流程全解析

首页 / 建站日记 / 企业官网建设流程全解析
别把 SQL 写乱了：先想清楚这三个问题
2026/7/6 5:37:25 网站建设 项目流程

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 isFinalPlan=false +- 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: struct<uid:int,submit_time:string>

这个物理执行计划展示了一个 Hive SQL 查询在 Spark 引擎下的执行步骤，从下往上分析如下：

  1. FileScan：从 HDFS 上的 ORC 文件中读取default.exam_record表的数据，只读取uid和submit_time列。

  2. HashAggregate (partial)：进行部分聚合操作，计算每个uid对应的未提交记录数（partial_sum(if (isnull(submit_time)) 1 else 0)）和总记录数（partial_count(1)）。

  3. Exchange (hashpartitioning)：对数据进行哈希分区，将相同uid的数据分发到同一个节点上，以便后续的全局聚合。

  4. HashAggregate：进行全局聚合操作，汇总每个uid的未提交记录数和总记录数。

  5. Exchange (SinglePartition)：将所有数据收集到一个分区中，以便后续的排序操作。

  6. Sort：按照_w0列进行降序排序。

  7. Window：使用窗口函数row_number()为排序后的数据添加排名信息。

  8. Project：选择最终需要的列，即uid、incomplete_rate和ranking。

3.基础知识点小结

❝

谨记

  1. 窗口函数不改变原数据表解构（在当前表上的滑动计算，可以分区、排序，甚至可以和窗口函数嵌套使用）

  2. 窗口函数可以和聚合函数嵌套使用，但是请注意执行顺序： 先分组--->组内做聚合运算--->聚合后对当前表进行窗口函数。

此外：窗口函数通常写在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 BY+user_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 时，不要一上来就套函数，而是先想清楚三个问题：

  1. 第一，最终结果是什么粒度？

  2. 第二，排序或累计的指标是不是需要先聚合？

  3. 第三，窗口函数是在全局范围内算，还是在每个分组内算？

只要这三个问题想清楚，SQL 结构就很清晰：

  • 明细数据 → group by 聚合到业务粒度 → over 在聚合结果上做窗口分析

标签： 网站建设 企业官网 项目流程 UI设计 前端开发

热门文章

文章分类

标签云

网站建设 响应式设计 用户体验 SEO优化 前端开发 小程序 电商平台 性能优化

相关文章

您可能感兴趣的其他内容

每日热门skill：你的AI Agent被人偷偷“洗脑“了？OpenClaw记忆安全扫描器memory-scan深度解析
2026/7/6 5:37:19 网站建设

每日热门skill：你的AI Agent被人偷偷“洗脑“了？OpenClaw记忆安全扫描器memory-scan深度解析

2026年初&#xff0c;ClawHavoc供应链攻击席卷OpenClaw生态&#xff0c;1184个恶意Skill通过ClawHub传播&#xff0c;30万实例被攻陷&#xff0c;150万个API Token泄露。这不是安全厂商的恐吓文案——这是真实发生过的灾难。而你的Agent记忆文件里&#xff0c;可能就藏着下一个…...

阅读更多 →
3分钟快速解锁B站缓存视频：m4s-converter无损转换完全指南
2026/7/6 5:37:11 网站建设

3分钟快速解锁B站缓存视频：m4s-converter无损转换完全指南

3分钟快速解锁B站缓存视频&#xff1a;m4s-converter无损转换完全指南 【免费下载链接】m4s-converter 一个跨平台小工具&#xff0c;将bilibili缓存的m4s格式音视频文件合并成mp4 项目地址: https://gitcode.com/gh_mirrors/m4/m4s-converter 你是否曾经遇到过这样的烦…...

阅读更多 →
Tudat航天动力学工具箱详解
2026/7/6 5:34:39 网站建设

Tudat航天动力学工具箱详解

Tudat (TU Delft Astrodynamics Toolbox) 航天动力学工具箱详解 引言 在航天动力学领域，高精度的轨道仿真和分析是任务成功的关键。荷兰代尔夫特理工大学（TU Delft）开发的 Tudat（TU Delft Astrodynamics Toolbox）是一个功能强大的开源航天动力学软件套件，为研究人员和工…...

阅读更多 →

需要专业的网站建设服务？

联系我们获取免费的网站建设咨询和方案报价，让我们帮助您实现业务目标

立即咨询