（课堂笔记回顾）聚合函数、开窗函数

SQL核心函数聚合函数SUM/AVG/MAX/MIN/COUNT实现数据汇总GROUP BY分组后HAVING过滤组级结果。注意COUNT(*)包含NULLCOUNT(列)忽略NULL。窗口函数通过OVER子句实现高级分析包含五大类排名函数ROW_NUMBER/RANK/DENSE_RANK取值函数LAG/LEAD获取相邻行FIRST_VALUE取首项分片函数NTILE数据分桶聚合窗口SUM/AVG实现滚动累计偏移分析同比环比计算关键区别聚合函数压缩行数窗口函数保留原数据行数。执行顺序上窗口函数在GROUP BY之后处理。 昨日回顾 - 复习笔记一、聚合函数核心语法sqlSELECT 字段, 聚合函数(列) AS 别名 FROM 表 WHERE 行级过滤条件 GROUP BY 分组字段 HAVING 组级过滤条件(可使用聚合函数) ORDER BY 排序字段;聚合函数一览函数作用空值处理SUM(列)求和忽略NULLAVG(列)平均值忽略NULLMAX(列)最大值忽略NULLMIN(列)最小值忽略NULLCOUNT(列)计数忽略NULLCOUNT(*)总行数包含NULLCOUNT(1)总行数包含NULLCOUNT 的区别sqlCOUNT(字段) -- 排除NULL值统计非空行数 COUNT(*) -- 统计所有行包含NULL COUNT(1) -- 统计所有行性能略优于COUNT(*)核心规则规则说明SELECT约束非分组字段、非聚合值不能出现在SELECT中HAVING只用于聚合值过滤如总薪资10000WHERE用于行级过滤如薪资1000NULL处理空值不参与聚合计算二、窗口函数开窗函数基础语法sql函数() OVER (PARTITION BY 分组字段 ORDER BY 排序字段)五种窗口函数类型类型函数用途排名类ROW_NUMBER(),RANK(),DENSE_RANK()取TOP N取值类LAG(),LEAD(),FIRST_VALUE()同环比、上下行分片类NTILE()数据切片/分桶聚合类SUM(),AVG(),COUNT()等累计、占比偏移类LAG(),LEAD()偏移取值三、排名函数详解三种排名对比函数效果示例ROW_NUMBER()连续不重复1,2,3,4RANK()有间隔1,1,3,4DENSE_RANK()无间隔1,1,2,3示例数据张三、李四的成绩sql姓名 科目 成绩 ROW_NUMBER RANK DENSE_RANK --------------------------------------------------------- 张三 英语 105 1 1 1 张三 语文 98 2 2 2 张三 数学 76 3 3 3 李四 语文 99 1 1 1 李四 数学 78 2 2 2 李四 英语 66 3 3 3四、LAG / LEAD同环比计算语法sqlLAG(目标字段, 偏移量, 缺省值) OVER (PARTITION BY 分组 ORDER BY 排序) LEAD(目标字段, 偏移量, 缺省值) OVER (PARTITION BY 分组 ORDER BY 排序)示例数据sql-- 按姓名分组按成绩排序取上一行科目 姓名 科目 成绩 LAG(科目) 说明 --------------------------------------------- 张三 数学 76 NULL -- 成绩最低无上一行 张三 英语 105 数学 -- 上一行是数学 张三 语文 98 英语 -- 上一行是英语 李四 英语 66 NULL -- 成绩最低无上一行 李四 数学 78 英语 -- 上一行是英语 李四 语文 99 数学 -- 上一行是数学五、FIRST_VALUE取首个值语法sqlFIRST_VALUE(目标字段) OVER (PARTITION BY 分组 ORDER BY 排序)示例数据sql-- 按姓名分组按成绩降序取最高分科目 姓名 科目 成绩 FIRST_VALUE(科目) 说明 --------------------------------------------- 张三 英语 105 英语 -- 最高分科目 张三 语文 98 英语 -- 全部显示英语 张三 数学 76 英语 -- 全部显示英语 李四 语文 99 语文 -- 最高分科目 李四 数学 78 语文 -- 全部显示语文 李四 英语 66 语文 -- 全部显示语文六、NTILE数据切片语法sqlNTILE(桶数) OVER (PARTITION BY 分组 ORDER BY 排序)示例数据2个桶按成绩从高到低sql姓名 科目 成绩 NTILE(2) 说明 ---------------------------------------- 张三 英语 105 1 -- 前50% 张三 语文 98 1 -- 前50% 张三 数学 76 2 -- 后50% 李四 语文 99 1 -- 前50% 李四 数学 78 1 -- 前50% 李四 英语 66 2 -- 后50%七、聚合型开窗三种模式对比1. 全局窗口无PARTITION无ORDER BYsqlSUM(成绩) OVER ()姓名科目成绩全局总和张三语文98522张三数学76522张三英语105522李四语文99522李四数学78522李四英语665222. 分组窗口有PARTITION无ORDER BYsqlSUM(成绩) OVER (PARTITION BY 姓名)姓名科目成绩个人总和张三语文98279张三数学76279张三英语105279李四语文99243李四数学78243李四英语662433. 累计窗口有PARTITION ORDER BYsqlSUM(成绩) OVER (ORDER BY 成绩) -- 全局累计 SUM(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩) -- 组内累计全局累计按成绩升序姓名科目成绩全局累计计算过程李四英语666666张三数学761426676李四数学78220667678张三语文9831898李四语文9941799张三英语105522105组内累计按姓名分组按成绩升序姓名科目成绩组内累计计算过程张三数学767676张三语文981747698张三英语1052797698105李四英语666666李四数学781446678李四语文99243667899八、执行顺序对比聚合函数执行顺序textFROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY窗口函数执行顺序textFROM → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → ORDER BY⚠️重点窗口函数在 GROUP BY 和 HAVING之后执行九、快速记忆卡text┌─────────────────────────────────────────────────────────────┐ │ 聚合函数 vs 窗口函数 │ ├─────────────────────────────────────────────────────────────┤ │ 聚合函数GROUP BY 压缩行数HAVING 过滤组 │ │ 窗口函数PARTITION BY 保留行数OVER 开窗计算 │ ├─────────────────────────────────────────────────────────────┤ │ 排名ROW_NUMBER (连续) / RANK (跳号) / DENSE_RANK (不跳) │ │ 取值LAG (上一行) / LEAD (下一行) / FIRST_VALUE (首行) │ │ 分片NTILE(N) 分成 N 份 │ │ 累计SUM/AVG OVER (ORDER BY) 实现滚动计算 │ ├─────────────────────────────────────────────────────────────┤ │ 口诀 │ │ 分组聚合用GROUP开窗计算用PARTITION │ │ WHERE行过滤HAVING组过滤 │ │ 排名就在OVER里TOP N轻松取 │ └─────────────────────────────────────────────────────────────┘