前言FDE系列内容总纲【大纲】FDE 前沿部署工程师学习系列教程-CSDN博客前置课程列表见文档结尾附录。阶段2·Day 33进阶查询 — 窗口函数与 CTEFDE 学习系列教程 · 第二阶段 · 第 3 周 · Day 3预计时长3 小时 | 难度★★★★☆ | 前置知识JOIN、GROUP BY、聚合函数一句话目标掌握子查询、CTEWITH 子句、窗口函数ROW_NUMBER / RANK / SUM OVER用一行 SQL 搞定分组排名组内 TopN这类复杂统计。‍‍ 开场一个 GROUP BY 搞不定的需求先抛个真实需求老板让你找出每个车间温度最高的 2 台设备。用昨天学的 GROUP BY 试试你会发现撞墙了GROUP BY 一车间 一车间 → 平均温度 77.0最高温度 91.0 ← 只剩统计数字 哪两台设备信息没了GROUP BY 的特点是压扁——一个车间好几台设备分组后每个车间只剩一行聚合值原始的设备明细全没了。你知道一车间最高温是 91但你不知道那台设备叫 A3。你真正想要的是这种效果——行一行不少但每行额外带一个组内排名设备名 | 温度 | 车间 | 组内温度排名 注塑机A3 | 91 | 一车间 | 1 ← 明细还在 冲压机B1 | 75 | 一车间 | 2 注塑机A1 | 65 | 一车间 | 3 冲压机B2 | 88 | 二车间 | 1 注塑机A2 | 82 | 二车间 | 2这就是**窗口函数Window Function**干的活。今天难度上了台阶别慌一步步来每一步都给你拆清楚。 一、子查询把一个查询的结果当成值或表用在学窗口函数前先补一块基础——子查询就是查询里套查询。场景找出温度高于平均值的设备平均值本身需要一个查询算出来然后拿它当 WHERE 的比较标准SELECT name, temperature FROM devices_new WHERE temperature ( SELECT AVG(temperature) FROM devices_new -- 子查询先算出平均温度 );执行顺序数据库很聪明先跑括号里的第 1 步SELECT AVG(temperature) FROM devices_new → 80.2 第 2 步SELECT name,temperature FROM devices_new WHERE temperature 80.2结果name | temperature 注塑机A2 | 82.0 注塑机A3 | 91.0 冲压机B2 | 88.0子查询能出现的三个位置位置例子含义WHERE 里WHERE temp (SELECT AVG...)把子查询结果当比较值SELECT 里SELECT name, (SELECT COUNT(*)...)每行配一个子查询算出的列FROM 里FROM (SELECT ...) AS t把子查询结果当一张临时表❌ 子查询的痛点子查询能解决问题但套多了可读性极差像俄罗斯套娃-- 看三层嵌套谁不头大 SELECT * FROM ( SELECT department, AVG(temperature) AS avg_t FROM ( SELECT d.temperature, e.department FROM devices_new d JOIN engineers e ON d.engineer_ide.id ) x GROUP BY department ) y WHERE y.avg_t 80;这时候CTE就来救场了。 二、CTEWITH 子句给子查询起名字像流水线一样写CTT 全称 Common Table Expression公共表表达式。名字唬人用法就一句话WITH 名字 AS (一段查询)—— 先把一段查询结果起个名字后面就能像用普通表一样用它。用 CTE 改写刚才高于平均值的例子WITH avg_temp AS ( SELECT AVG(temperature) AS avg_val FROM devices_new ) SELECT d.name, d.temperature FROM devices_new d CROSS JOIN avg_temp -- 把单行的平均值拼进来 WHERE d.temperature avg_temp.avg_val; CTE 的核心价值是可读性。它让你把一个复杂问题拆成几个有名字的步骤从上往下像读文章一样读 SQL。嵌套子查询是套娃CTE 是流水线。多段 CTE 串联一段喂给下一段这个写法在真实报表里非常常见。来看个完整例子——统计每个车间的工单数和平均温度WITH -- 第 1 段先把设备和工程师关联好起名为 device_info device_info AS ( SELECT d.id, d.name, d.temperature, e.department FROM devices_new d INNER JOIN engineers e ON d.engineer_id e.id ), -- 第 2 段基于第 1 段按车间数工单 ticket_count AS ( SELECT di.department, COUNT(t.id) AS ticket_num FROM device_info di LEFT JOIN tickets t ON t.device_id di.id GROUP BY di.department ), -- 第 3 段基于第 1 段按车间算平均温度 summary AS ( SELECT department, AVG(temperature) AS avg_temp FROM device_info GROUP BY department ) -- 最后把第 2、3 段拼起来输出 SELECT s.department AS 车间, tc.ticket_num AS 工单数, ROUND(s.avg_temp, 1) AS 平均温度 FROM summary s JOIN ticket_count tc ON s.department tc.department ORDER BY tc.ticket_num DESC;结果车间 | 工单数 | 平均温度 一车间 | 3 | 77.0 二车间 | 2 | 85.0CTE 流水线示意 原始三表 │ ▼ ┌─────────────┐ │ device_info │ 第1段设备人关联 └──────┬──────┘ ┌───┴───────────────┐ ▼ ▼ ┌──────────────┐ ┌──────────┐ │ ticket_count │ │ summary │ 第2、3段并行统计 └──────┬───────┘ └────┬─────┘ └────────┬───────────┘ ▼ 最终 JOIN 输出CTE 两个要点① 每段用逗号,分隔最后一段不加逗号② 后面的段可以直接引用前面段的名字。写复杂查询时先在纸上拆步骤再一段段翻译成 CTE。 三、窗口函数不压扁行也能做统计正式进入今天的主角。先看它和 GROUP BY 的本质区别┌──────────────────────────────────────────────────────────────┐ │ GROUP BY 把同组多行压扁成一行只留聚合值 │ │ 窗口函数 OVER() 行一行不少每行额外开一扇窗看组内信息 │ │ 排名、累计、平均、总和…… │ └──────────────────────────────────────────────────────────────┘ 原始数据 GROUP BY A1 一车间 65 一车间 → AVG77明细没了 B1 一车间 75 A3 一车间 91 窗口函数 A2 二车间 82 A1 一车间 65 组内AVG77 排名3 B2 二车间 88 B1 一车间 75 组内AVG77 排名2 A3 一车间 91 组内AVG77 排名1 ← 每行都在 A2 二车间 82 组内AVG85 排名2 B2 二车间 88 组内AVG85 排名1 什么是窗口函数窗口函数也叫分析函数Analytic Functions它会对一组行称为“窗口”进行计算但不会将多行合并为一行而是为每一行返回一个计算结果。你可以把它想象成在不破坏原始数据明细的前提下给每一行数据额外“开一扇窗”让它看到自己所属分组内的统计信息。传统聚合GROUP BY把一屋子人聚在一起算完平均身高后只告诉你一个数字原本谁是谁全忘了。窗口函数OVER一屋子人排排坐算完平均身高后不仅保留每个人的名字还在每个人背后贴个标签“你的身高是175你们组的平均身高是170”。窗口函数的通用长相函数名([参数]) OVER ( [PARTITION BY 分区列1, 分区列2, ...] [ORDER BY 排序列1 [ASC|DESC], ...] [ROWS | RANGE 窗口帧范围] )三个核心组成部分函数名可以是聚合函数、专用窗口函数或排名函数。PARTITION BY分区可选。将数据分成不同的组窗口类似于GROUP BY但不会合并行。如果不写整个结果集就是一个大窗口。ORDER BY排序可选。决定窗口内数据的顺序。对于排名函数和累计计算排序是必须的。ROWS | RANGE窗口帧可选。进一步限定窗口的范围例如只计算当前行及前两行。默认情况下如果写了ORDER BY窗口帧通常是“从分组开始到当前行”如果没写ORDER BY窗口帧是整个分区。三、 窗口函数的分类按功能窗口函数主要分为三大类1. 聚合类窗口函数这类函数原本是配合GROUP BY使用的但加上OVER()后就变成了窗口函数。SUM()求和AVG()平均值COUNT()计数MAX()/MIN()最大值/最小值示例计算每个员工的工资以及该部门的平均工资。sqlSELECT name, dept, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg_salary FROM employees;结果每一行都保留但多了一列显示该部门的平均工资。2. 排名类窗口函数专门用于给数据打排名。ROW_NUMBER()连续排名。即使数值相同排名也依次递增1, 2, 3, 4。RANK()跳跃排名。数值相同排名相同但后续排名会跳跃1, 2, 2, 4。DENSE_RANK()密集排名。数值相同排名相同后续排名不跳跃1, 2, 2, 3。NTILE(n)将数据分成 n 个桶返回桶的编号常用于分位数。示例按工资在部门内排名。sqlSELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept FROM employees;3. 偏移/取值类窗口函数这类函数用于访问当前行之前或之后的行常用于计算同比、环比、差值。LAG(col, n)获取当前行之前第 n 行的值默认 n1。LEAD(col, n)获取当前行之后第 n 行的值。FIRST_VALUE(col)获取窗口内第一行的值。LAST_VALUE(col)获取窗口内最后一行的值。示例计算每个员工与上一位员工工资的差额。sqlSELECT name, salary, LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary, salary - LAG(salary, 1) OVER (ORDER BY salary) AS diff FROM employees;四、 深入理解PARTITION BY与ORDER BY这是窗口函数最容易混淆的地方结合你之前图片中的例子PARTITION BY分组决定了“窗户”开在哪里。每个分区是独立的计算不会跨区。例如PARTITION BY 车间那么一车间的平均值不会影响到二车间的计算。ORDER BY排序决定了“窗户”内的顺序。对于排名和累计计算至关重要。例如ORDER BY 工资 DESC那么排名就是按工资从高到低。关键点当同时有PARTITION BY和ORDER BY时计算是在每个分区内部独立排序并计算的。累计求和示例sqlSELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total FROM daily_sales;结果每一行显示截止到当天的累计销售额。五、 窗口帧Window Frame—— 进阶必知窗口帧定义了当前行计算时具体包含分区内的哪些行。语法sqlROWS BETWEEN 开始位置 AND 结束位置常见选项UNBOUNDED PRECEDING从分区第一行开始。CURRENT ROW当前行。UNBOUNDED FOLLOWING到分区最后一行结束。n PRECEDING当前行之前的 n 行。n FOLLOWING当前行之后的 n 行。示例计算当前行及前两行的移动平均。sqlSELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM daily_sales;️ 四、三种排名函数ROW_NUMBER / RANK / DENSE_RANK先来最简单的——给所有设备按温度从高到低排名不分组SELECT name, temperature, ROW_NUMBER() OVER (ORDER BY temperature DESC) AS row_num, RANK() OVER (ORDER BY temperature DESC) AS rank_val, DENSE_RANK() OVER (ORDER BY temperature DESC) AS dense_rank_val FROM devices_new ORDER BY temperature DESC;当前数据没有并列三个结果一模一样设备名 | 温度 | row_num | rank_val | dense_rank_val 注塑机A3 | 91.0 | 1 | 1 | 1 冲压机B2 | 88.0 | 2 | 2 | 2 注塑机A2 | 82.0 | 3 | 3 | 3 冲压机B1 | 75.0 | 4 | 4 | 4 注塑机A1 | 65.0 | 5 | 5 | 5三者的区别只有在出现并列时才看得出来。假设两台设备都是 88 度并列第 2设备名 | 温度 | ROW_NUMBER | RANK | DENSE_RANK 注塑机A3 | 91 | 1 | 1 | 1 冲压机B2 | 88 | 2 | 2 | 2 ← 并列第 2 冲压机B3 | 88 | 3 | 2 | 2 ← 三个函数在这里分叉 注塑机A2 | 82 | 4 | 4 | 3 ───────────────────────────────────────── ROW_NUMBER强行编号 1,2,3,4绝不并列平局也分先后 RANK 并列同号之后跳号 1,2,2,4 DENSE_RANK 并列同号之后不跳 1,2,2,3给你一张选择表函数编号风格适用场景ROW_NUMBER()1 2 3 4 连续永不并列取 TopN、去重留一条最常用RANK()1 2 2 4 跳号比赛排名两个并列亚军就没季军了DENSE_RANK()1 2 2 3 不跳号看第几档成绩 FDE 现场用得最多的是ROW_NUMBER——因为每组取最新一条/取 TopN本质都要求每组恰好 N 行RANK 遇到并列可能多出一行。️ 五、PARTITION BY分组内排名核心大招光全局排名不够真正的杀手锏是每个组内部分别排名。回到开场需求每个车间温度最高的 2 台设备。WITH ranked AS ( SELECT e.department AS 车间, d.name AS 设备名, d.temperature AS 温度, ROW_NUMBER() OVER ( PARTITION BY e.department -- 按车间分组但不压扁 ORDER BY d.temperature DESC -- 每个车间内部按温度降序 ) AS 组内排名 FROM devices_new d INNER JOIN engineers e ON d.engineer_id e.id ) SELECT 车间, 设备名, 温度, 组内排名 FROM ranked WHERE 组内排名 2; -- 每组只留前 2 名结果车间 | 设备名 | 温度 | 组内排名 一车间 | 注塑机A3 | 91.0 | 1 一车间 | 冲压机B1 | 75.0 | 2 二车间 | 冲压机B2 | 88.0 | 1 二车间 | 注塑机A2 | 82.0 | 2这个分组 TopN模板请背下来┌────────────────────────────────────────────────────────┐ │ WITH 排名表 AS ( │ │ SELECT ..., │ │ ROW_NUMBER() OVER ( │ │ PARTITION BY 分组列 │ │ ORDER BY 排序列 DESC │ │ ) AS rn │ │ FROM ... │ │ ) │ │ SELECT * FROM 排名表 WHERE rn N; ← 每组取前 N │ └────────────────────────────────────────────────────────┘这个套路能解决一大批业务问题每个车间温度最高的 2 台设备每个客户最近的 3 笔订单每个部门工资最高的人每台设备最新的一条告警记录ORDER BY created_at DESC取 rn1每个分类下销量 Top 5 的商品PARTITION BY vs GROUP BY 一句话区分GROUP BY 车间一车间 3 行 → 1 行明细消失PARTITION BY 车间一车间 3 行 → 还是 3 行每行多个组内排名️ 六、SUM / AVG OVER让每行带着组的统计值窗口函数不止能排名还能在每行上附加聚合值且不损失明细。需求列出每台设备同时显示它所在车间的平均温度和总温度SELECT d.name AS 设备名, d.temperature AS 温度, e.department AS 车间, ROUND(AVG(d.temperature) OVER (PARTITION BY e.department), 1) AS 车间均温, SUM(d.temperature) OVER (PARTITION BY e.department) AS 车间总温, COUNT(*) OVER (PARTITION BY e.department) AS 车间设备数 FROM devices_new d INNER JOIN engineers e ON d.engineer_id e.id ORDER BY e.department, d.temperature DESC;结果设备名 | 温度 | 车间 | 车间均温 | 车间总温 | 车间设备数 注塑机A3 | 91.0 | 一车间 | 77.0 | 231.0 | 3 冲压机B1 | 75.0 | 一车间 | 77.0 | 231.0 | 3 ← 同组的统计值每行都带一份 注塑机A1 | 65.0 | 一车间 | 77.0 | 231.0 | 3 冲压机B2 | 88.0 | 二车间 | 85.0 | 170.0 | 2 注塑机A2 | 82.0 | 二车间 | 85.0 | 170.0 | 2这在做报表时特别有用既显示每台设备的明细又在同一行给出它所在组的整体水平方便对比这台设备比车间平均高还是低。钻研不通也别死磕窗口函数完整语法一次消化不了正常先把两个最常用的模式记住ROW_NUMBER PARTITION BY取组内 TopN、AVG/SUM OVER (PARTITION BY)带组统计。其余的移动平均、LAG/LEAD 取上下行等后面用到时再回来查。窗口函数分类速查混个眼熟即可类别函数干嘛用排名ROW_NUMBER / RANK / DENSE_RANK编号、排名聚合SUM/AVG/COUNT/MAX/MIN OVER(...)不压扁行做聚合取值FIRST_VALUE / LAST_VALUE取窗口内第一/最后一行的值偏移LAG / LEAD取上一行 / 下一行的值算环比常用分布NTILE(n)把结果平均切成 n 份️ 七、综合实战工单分析组合拳把今天学的串起来。需求找出每个上报人最新的那一张工单每人只看最近 1 条。WITH ranked_tickets AS ( SELECT e.name AS 上报人, t.title AS 标题, t.priority AS 优先级, t.created_at AS 创建时间, ROW_NUMBER() OVER ( PARTITION BY t.reporter_id -- 按上报人分组 ORDER BY t.created_at DESC -- 组内按时间倒序最新的排第1 ) AS rn FROM tickets t INNER JOIN engineers e ON t.reporter_id e.id ) SELECT 上报人, 标题, 优先级, 创建时间 FROM ranked_tickets WHERE rn 1; -- 每人最新的一张结果上报人 | 标题 | 优先级 | 创建时间 李工 | 例行保养 | 低 | ... 王工 | 振动超标 | 中 | ... ← 王工有2张只留时间最新的 赵工 | 温度严重超标 | 高 | ... 这是 FDE 数据处理的高频模式——每组最新一条。设备的最新状态、订单的最新进度、用户的最近登录全是PARTITION BY 主体 ORDER BY 时间 DESCrn 1。 本课小结知识点一句话记住子查询查询套查询结果可当值/表用CTEWITH 名字 AS(...)把复杂查询拆成命名步骤像流水线窗口函数函数() OVER(PARTITION BY... ORDER BY...)不压扁行ROW_NUMBER连续编号不并列取 TopN 首选RANK并列跳号 1,2,2,4DENSE_RANK并列不跳 1,2,2,3PARTITION BY分组但保留明细区别于 GROUP BY 压扁组内 TopN 模板CTE 里 ROW_NUMBER 编号外层 WHERE rnN最新一条PARTITION BY 主体 ORDER BY 时间 DESC rn1聚合窗口AVG/SUM OVER(PARTITION BY...)每行带组统计 核心认知GROUP BY 是望远镜只看每组的汇总窗口函数是放大镜标尺每一行都在还能标出它在组里的位置。CTE 则让复杂 SQL 变得能读、能改、能维护。 课后练习在 DBeaver 里完成窗口函数 SQLite 3.25 支持DBeaver 自带的驱动没问题用子查询找出温度高于全部设备平均值的设备再用CTE改写一遍对比哪种好读给所有设备按振动值从高到低用 ROW_NUMBER 排名找出每个车间温度最高的那台设备PARTITION BY ROW_NUMBER rn1用多段 CTE先算每个工程师的工单数再找出工单数最多的工程师按优先级给工单排名每个优先级只看最新的 1 张PARTITION BY priority ORDER BY created_at DESC进阶挑战列出每台设备的温度以及它比本车间平均温度高多少提示温度 - AVG OVER 下节预告三天下来查询的本事学了不少。明天进入 FDE 最脏也最接地气的工作——数据清洗。客户发来一份 Excel 导出有完全重复的行、温度写成中文八十二、手机号一会儿带横杠一会儿空着、设备名大小写空格五花八门……这种数据直接进库就是灾难。明天教你去重、补缺失值、格式对齐、类型转换、数据脱敏把一坨脏数据洗成漂漂亮亮的结构化数据。准备好当数据清洁工明天见附录前置课程列表阶段一【FDE系列】阶段1Day 1AI 层级关系 — 四个嵌套的圈-CSDN博客【FDE系列】阶段1Day 2AI 三阶段发展史 — 会认 → 会判断 → 会创造-CSDN博客【FDE系列】阶段1Day 3符号 AI vs 机器学习 — 两条路线的本质区别-CSDN博客【FDE系列】阶段1Day 4Transformer 的历史意义 — 2017 年的分水岭-CSDN博客【FDE系列】阶段1Day 5本周复习与自测 — 检验你的 AI 认知地基-CSDN博客【FDE系列】阶段1Day 6Transformer 架构 — 一张图纸盖出千千万万栋楼-CSDN博客【FDE系列】阶段1Day 7LLM 本质 — 文字接龙机器-CSDN博客【FDE系列】阶段1Day 8Token — 模型眼中的最小单位-CSDN博客【FDE系列】阶段1Day 9AI 幻觉 — 为什么会一本正经地胡说八道-CSDN博客【FDE系列】阶段1Day 10上下文窗口 — 模型的记忆力上限 本周复习-CSDN博客【FDE系列】阶段1Day 11Prompt — 给模型立规矩-CSDN博客【FDE系列】阶段1Day 12Memory — 让模型记住上下文【FDE系列】阶段1Day 13RAG — 给模型配图书管理员-CSDN博客【FDE系列】阶段1Day 14Tool Use — 让模型动手操作-CSDN博客【FDE系列】阶段1Day 15MCP — 统一的工具接口标准 第三周复习-CSDN博客【FDE系列】阶段1Day 16什么是 FDE — 把 AI 变成客户结果的人-CSDN博客【FDE系列】阶段1Day 17FDE vs 传统实施 — 三大本质区别-CSDN博客【FDE系列】阶段1Day 18FDE 三重身份 C6 胜任力模型-CSDN博客【FDE系列】阶段1Day 19七阶段行动路径 行业经验的价值-CSDN博客【FDE系列】阶段1Day 20阶段总结与产出物 — 第一阶段收官-CSDN博客阶段二【FDE系列】阶段2Day 21Python 环境搭建 — 写出你的第一行代码-CSDN博客【FDE系列】阶段2Day 22变量、数据类型、条件判断 — Python 的“记忆“和“判断“-CSDN博客【FDE系列】阶段2Day 23循环与函数 — 让代码跑 100 遍、把逻辑打包复用-CSDN博客【FDE系列】阶段2Day 24数据结构 — 列表、字典、集合、元组-CSDN博客【FDE系列】阶段2Day 25文件读写与 JSON — 让程序连通外部数据第一周收官-CSDN博客【FDE系列】阶段2Day 26模块化编程 — 把代码拆成“抽屉柜“-CSDN博客【FDE系列】阶段2Day 27异常处理与日志 — 让程序“摔不烂、查得到“-CSDN博客【FDE系列】阶段2Day 28FastAPI 入门 — 把你的函数变成 API 服务-CSDN博客【FDE系列】阶段2Day 29FastAPI 进阶 — Pydantic 模型与完整 CRUD 实战-CSDN博客【FDE系列】阶段2Day 30生产代码规范 — 测试、类型注解、配置管理第二周收官-CSDN博客【FDE系列】阶段2Day 31SQL 基础 — 增删改查一把梭-CSDN博客【FDE系列】阶段2Day 32多表查询 — JOIN 与聚合-CSDN博客