SQL 手写实战题

• 窗口函数排名
• 相关子查询
• 第二高

窗口函数写法：用 DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) 排名，取 rn=2。若并列算同一名次用 DENSE_RANK（并列都算第二），若并列算不同名次用 ROW_NUMBER。相关子查询写法：自连接比较，找出工资第二高的人（工资小于该部门最高工资中的最大值）。处理不存在（部门只有一人/无第二高）时，窗口写法会无匹配行，可注意。常用 DENSE_RANK 并列语义。

窗口函数用 DENSE_RANK 排名取 rn=2，相关子查询自连接比较工资。并列用 DENSE_RANK。

• 日期减行号
• 连续分组
• 推广

连续登录 ≥3 天的用户：先按用户去重日期，再用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) 编号，用 date - 编号 得到"分组键"，同组的日期连续（date 减行号在连续日期时相等）。按用户和分组键聚合，组内日期数 >=3 即连续登录 ≥3 天。推广场景：连续签到、连续消费、连续活跃、gaps-and-islands（连续区间）问题。该技巧把"连续"转化为"分组"。

日期减行号分组法：连续日期 date-row_number 相同，聚合计数 >=3 即连续。推广到连续区间分析。

• 留存率
• 自关联
• 窗口

次日留存率 = 次日活跃用户 / 当日活跃用户。自关联写法：先取用户首次活跃日期（作为基准日），再关联后续活跃，计算次日/7 日是否留存。窗口写法：用 LAG/FIRST 获取用户首次活跃日期，判断是否在基准日+1/7 活跃。通常：基准日（用户首日）与后续活跃日自关联，若基准日+1 有活跃则留存。窗口写法用 MIN(active_date) OVER (PARTITION BY user) 得首日，再 LEFT JOIN 判断次日活跃。

留存率 = 首日活跃用户在 N 日后仍活跃的比例。自关联按首日分组，窗口用 MIN 首日 + 判断后续活跃。

• 行转列
• CASE WHEN/PIVOT
• 列转行

行转列（成绩表科目透视）：把同一个学生的多个科目行转成科目列。用 CASE WHEN 条件聚合：SELECT student_id, MAX(CASE WHEN subject='math' THEN score END) AS math, MAX(CASE WHEN subject='chinese' THEN score END) AS chinese FROM score GROUP BY student_id。或用 PIVOT（SQL Server/Oracle）。列转行：把宽表多列转成行，用 UNION ALL 或 UNPIVOT：SELECT id, 'math' AS subject, math AS score FROM t UNION ALL SELECT id, 'chinese', chinese FROM t。CASE WHEN 是跨数据库最可移植的行转列写法。

行转列用 CASE WHEN 条件聚合（或 PIVOT），列转行用 UNION ALL 或 UNPIVOT。CASE WHEN 可移植。

• 日期去重
• 窗口函数
• 分组技巧

连续登录 N 天：先对日期去重（一天可能多次登录，DISTINCT user_id, login_date），再用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) 编号，用 login_date - 编号作分组键（连续日期归同组），按 user_id + 分组键 GROUP BY 计数，HAVING COUNT(*) >= N。窗口函数用于编号，分组技巧（日期减行号）识别连续区间。去重保证一天只算一次。

去重日期 → 窗口编号 → 日期减行号分组 → 计数 >= N。窗口+分组技巧是核心。

• row_number TopN
• 自连接
• 对比

窗口函数：SELECT * FROM (SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM emp e) t WHERE rn <= 3。自连接：用 EXISTS 判断该部门内工资高于当前行的行数 < 3（即部门内排名前 3）。对比：窗口函数简洁、一次扫描、可读性好，是推荐写法；自连接代码复杂、性能差（子查询/笛卡尔比较），但仅依赖 SQL 基础语法。窗口函数明显优于自连接。

窗口函数 ROW_NUMBER 简洁高效，自连接用 EXISTS 数排名复杂低效。推荐窗口函数。

• 中位数
• 众数
• PERCENTILE_CONT

中位数：PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) 求连续中位数（插值）；或自连接排序取中间值。众数（出现次数最多的值）：按值分组计数取最大值：SELECT col FROM t GROUP BY col ORDER BY COUNT(*) DESC LIMIT 1；或用窗口 COUNT 分组 + RANK 取众数。PERCENTILE_CONT 是标准中位数方法，自连接/分组计数求众数。窗口写法可对分组（如按部门）求中位数/众数。

中位数用 PERCENTILE_CONT(0.5)，众数用 GROUP BY COUNT 排序取最多。窗口可分组。

• ROW_NUMBER
• 关联子查询
• MAX 回连

三种写法：1) 窗口 ROW_NUMBER：SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user ORDER BY order_date DESC) rn FROM orders) t WHERE rn=1，一次扫描排序，推荐；2) 关联子查询：用 WHERE order_date = (SELECT MAX(order_date) FROM orders o2 WHERE o2.user=o.user)，逐行子查询，性能差；3) 组内 MAX 回连：先 GROUP BY user 求 MAX(order_date)，再 JOIN 回原表取订单，分两步。性能：窗口函数最优（一次扫描），关联子查询最差（逐行执行），MAX 回连中等（需两次扫描 + JOIN）。推荐窗口函数。

窗口一次扫描最优，关联子查询逐行最差，MAX 回连两次扫描。推荐窗口函数。

• gaps-and-islands
• 连续区间
• 分组

gaps-and-islands（连续区间）通用解法：用 ROW_NUMBER() OVER (ORDER BY 序号) 编号，用 序号 - 编号 作为分组键（连续序号该差相同，形成 island），GROUP BY 分组键，取 MIN/MAX 得区间的起止。例如连续座位号：SELECT MIN(seat) AS start, MAX(seat) AS end FROM (SELECT seat, seat - ROW_NUMBER() OVER (ORDER BY seat) AS grp FROM seats) t GROUP BY grp。该技巧把连续区间识别为分组，常用于补缺失、找区间。

gaps-and-islands 用 序号-行号 分组，同组为连续区间，取 MIN/MAX 得起止。

• 去重保留最新
• 删除
• 安全

去重保留最新记录：用窗口 ROW_NUMBER() OVER (PARTITION BY 去重键 ORDER BY 时间 DESC) 编号，保留 rn=1 的行，删除其余。安全写法：先 SELECT 确认要删除的行（子查询），再用 DELETE 关联删除：DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) rn FROM t) x WHERE rn > 1)。先充分验证（SELECT 检查）再删，删前备份。保留最新用 ORDER BY ts DESC 取 rn=1。

用 ROW_NUMBER 按时间去重键排序，保留 rn=1，删除 rn>1。先 SELECT 验证再 DELETE 安全。

• 同比/环比
• LAG
• 日期对齐

环比：用 LAG(metric, 1) OVER (PARTITION BY 维度 ORDER BY 日期) 取上一期值，计算环比增长率 = (本期-上期)/上期。同比：用 LAG(metric, 12) OVER (PARTITION BY 维度 ORDER BY 月份) 取一年前值（月数据），或按日期对齐。边界处理：首期/无上期时 LAG 返回 NULL（或指定 default），需处理 NULL（如 COALESCE 或用的 IS NULL 判断）。日期对齐：同比需确保日期对应（如月同比用 LAG 周期数=12，日同比用 LAG 日期差=365）。整数/无数据月份需补足。

环比用 LAG(1) 取上期，同比用 LAG 按周期数/日期对齐取同期。边界（首期）用 default 处理 NULL。

• PIVOT/UNPIVOT
• GROUP_CONCAT
• 实现

行列转换：行转列用 CASE WHEN 条件聚合或 PIVOT（SELECT student, MAX(CASE WHEN subject='math' THEN score END) AS math FROM t GROUP BY student）；列转行用 UNION ALL 或 UNPIVOT。字符串聚合：把组内多行聚合成一个字符串，MySQL 用 GROUP_CONCAT(col SEPARATOR ',')，PostgreSQL 用 STRING_AGG(col, ',')，SQL Server 用 STRING_AGG。例如 SELECT dept, GROUP_CONCAT(name SEPARATOR ',') FROM emp GROUP BY dept。实现都基于 GROUP BY 分组聚合。

行转列用 CASE WHEN/PIVOT，列转行用 UNION ALL/UNPIVOT，字符串聚合用 GROUP_CONCAT/STRING_AGG + GROUP BY。

• 分组 TopN
• 窗口函数
• 写法

每个部门薪资前三名：SELECT * FROM (SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn FROM emp e) t WHERE rn <= 3。用 ROW_NUMBER 按部门分区、薪资降序编号，取 rn <= 3。若并列算同一名次用 DENSE_RANK 或 RANK。这是分组 TopN 的标准窗口写法。

ROW_NUMBER OVER (PARTITION BY dept ORDER BY salary DESC) 编号，取 rn<=3。并列用 DENSE_RANK。

• 递归 CTE
• 组织树
• 层级缩进

组织架构树递归展开：WITH RECURSIVE org AS (锚成员取根节点 UNION ALL 递归成员连接子节点，带 depth 深度) SELECT ... FROM org。层级缩进：用 REPEAT(' ', depth) 或 LPAD 按深度前缀缩进，显示层级。例如 org 递归：锚成员 SELECT id, name, parent_id, 0 AS depth FROM emp WHERE parent_id IS NULL，递归成员 SELECT e.id, e.name, e.parent_id, o.depth+1 FROM emp e JOIN org o ON e.parent_id=o.id。缩进用 REPEAT(' ', depth) || name。

递归 CTE 锚成员取根、递归成员连子节点带 depth，缩进用 REPEAT 按 depth 前缀。

• 分批更新
• 主键切片
• 避免锁表

大表分批 UPDATE/DELETE：按主键范围切片，每次处理一批（如 LIMIT 或 WHERE id BETWEEN），避免长事务与锁表。写法：循环按主键切片：循环中执行 UPDATE/DELETE ... WHERE id > last_id AND ... LIMIT n，或用 WHERE id BETWEEN 分段，每批 COMMIT。用脚本/存储过程循环，每批取一个主键范围处理，减小锁粒度与事务长度。关键：按主键有序切片、分批提交、控制每批大小。

按主键切片分批 UPDATE/DELETE，每批提交，避免长事务与锁表。关键是有序切片、分批提交。

• 累计和
• 移动平均
• 窗口

累计求和：SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 从第一行到当前行累计。移动平均：AVG(amount) OVER (ORDER BY date ROWS BETWEEN n PRECEDING AND CURRENT ROW) 最近 n+1 期平均。两者都用窗口函数 + 帧。累计和用 UNBOUNDED PRECEDING，移动平均用 n PRECEDING。可加 PARTITION BY 分区计算。

累计和用 UNBOUNDED PRECEDING 帧，移动平均用 n PRECEDING 帧。窗口函数 + 帧控制范围。

• 原子扣减
• 条件更新
• 乐观锁

库存扣减原子 SQL：条件更新：UPDATE inventory SET quantity = quantity - N WHERE product_id = ? AND quantity >= N，返回影响行数，若为 0 说明库存不足（原子、防超卖）。乐观锁：UPDATE inventory SET quantity = quantity - N, version = version + 1 WHERE product_id = ? AND version = ?，影响行数为 0 说明版本冲突，需重试。两种都利用 WHERE 条件保证原子性与并发安全。条件更新用 quantity >= N 防超卖，乐观锁用 version 防并发覆盖。

条件更新用 WHERE quantity >= N 防超卖，乐观锁用 version 比较防并发冲突。都原子、靠影响行数判断。

• LAG
• SUM OVER
• 实现

环比：LAG(metric, 1) OVER (ORDER BY period) 取上期值，环比增长率 = (metric - LAG(...))/LAG(...)。同比：LAG(metric, 12) OVER (ORDER BY month) 取一年前值。累计：SUM(metric) OVER (ORDER BY period ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 累计和。三者都用窗口函数：LAG 取前后期值，SUM OVER 做累计。用 COALESCE 处理边界 NULL。

LAG 取上期/同期值算同比环比，SUM OVER 做累计。窗口函数 + COALESCE 处理边界。

• ORDER BY RANDOM
• TABLESAMPLE
• 性能

随机抽样两种方式：ORDER BY RANDOM() LIMIT n：对全表每行计算随机值并全表排序，取前 n 行，性能差（全表扫描 + 排序），适合小表；TABLESAMPLE SYSTEM/BERNOULLI：按块/行采样，只读部分数据，性能好（尤其大表），但 SYSTEM 抽样可能不精确。性能差异：TABLESAMPLE 只读采样块（快），ORDER BY RANDOM() 全表扫描排序（慢）。大表应优先用 TABLESAMPLE。

ORDER BY RANDOM() 全表扫描排序慢，TABLESAMPLE 只读采样块快。大表用 TABLESAMPLE。