DD 数据开发学习站DATA ENGINEERING
模块 02 · SQL 与数据库

高级查询

子查询、CASE WHEN、CTE、日期函数、行列转换和集合运算。

难度 · 进阶 约 60 分钟 必修 练习 12 项 计划内 刷题 0/5
建议先学：多表查询与 JOIN
本页目录 · 19 节
1. 为什么需要它2. 核心概念2.1 子查询的四种形态2.2 CASE WHEN、IF、COALESCE、NULLIF2.3 行列转换：用 CASE WHEN 做 PIVOT2.4 CTE 与递归 CTE2.5 日期函数与方言差异2.6 集合运算2.7 复杂业务题的拆法3. 动手示例3.1 用 CASE 给订单分档4. 常见坑5. 在线练习6. 面试延伸面试题：UNION 和 UNION ALL 有什么区别？面试题：IN 和 EXISTS 怎么选？面试题：递归 CTE 能解决什么问题？7. 本章验收下一章

本章目标：掌握子查询、CASE WHEN、CTE、日期函数和集合运算，能独立拆解复购率、占比、环比等复杂业务题。 前置知识：SQL 基础、聚合与分组、多表查询与 JOIN。 预计用时：60 分钟。

1. 为什么需要它

前几章的查询是一条直线：筛行、分组、关联。真实需求经常不是直线：

  • 找出价格高于平均价的商品，先算平均价，再拿每一行去比。
  • 找出从没下过有效订单的客户，需要判断"不存在"。
  • 把按月、按状态竖着排的结果，变成横着的报表。
  • 算复购率，先把订单压到用户粒度，再算比例。

这些都需要把一段查询嵌进另一段查询，或者在一行里做条件判断。

本章继续用玩具库 project/sql_demo.duckdb 的九张 demo_* 小表。

2. 核心概念

2.1 子查询的四种形态

标量（scalar）子查询：返回一行一列，可以当普通数值用。

SELECT ROUND(AVG(unit_price), 2) AS 全站均价 FROM demo_order_items;

SELECT product_id, unit_price
FROM demo_order_items
WHERE unit_price > (SELECT AVG(unit_price) FROM demo_order_items)
ORDER BY unit_price DESC
LIMIT 5;

全站均价 319.91，高于均价的明细有 4 行；最高单价是 1,299.00。如果子查询返回多行，数据库会报"子查询返回多行"。

IN 子查询：返回一列多行，用来做集合判断。

SELECT COUNT(DISTINCT o.customer_id) AS 买过电子产品的客户数
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
JOIN demo_products p ON p.product_id = i.product_id
WHERE p.category = '电子';

结果 6 人。等价写法是把客户列表放进 IN (...)；IN 适合"值在某个列表里"。

EXISTS 子查询：不关心返回什么值，只关心有没有行。

SELECT COUNT(*) AS 没下过有效订单的客户
FROM demo_customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM demo_orders o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
);

结果 1 人（客户 7）。EXISTS 找到第一行就停，通常比把整列算出来再 IN 更高效。

相关子查询：内层查询引用外层的列，外层每行执行一次。

SELECT COUNT(*) AS 高于本品类均价的明细行数
FROM (
    SELECT i.product_id, i.unit_price, p.category
    FROM demo_order_items i
    JOIN demo_products p ON p.product_id = i.product_id
) x
WHERE unit_price > (
    SELECT AVG(i2.unit_price)
    FROM demo_order_items i2
    JOIN demo_products p2 ON p2.product_id = i2.product_id
    WHERE p2.category = x.category
);

结果 2 行，都来自电子品类。内层的 x.category 来自外层，所以不能把子查询单独拎出来跑。

注意：NOT IN 的子查询如果返回 NULL，整个条件会失效。找"不存在"优先用 NOT EXISTS，语义清晰，也不会被 NULL 坑。

2.2 CASE WHEN、IF、COALESCE、NULLIF

CASE WHEN 是 SQL 里的 if-else，从上往下判断，命中第一个就返回：

SELECT order_id,
       quantity * unit_price AS 明细金额,
       CASE
           WHEN quantity * unit_price >= 500 THEN '大额'
           WHEN quantity * unit_price >= 100 THEN '中额'
           ELSE '小额'
       END AS 档位
FROM demo_order_items
LIMIT 5;

条件聚合是它的高频用法：配合 SUM，一次算出多个指标。

IF 是两分支简写，DuckDB、Hive、Spark 都支持 IF(条件, 真值, 假值)；标准写法仍是 CASE WHEN，可移植性最好。

COALESCE 返回第一个非 NULL 值。用推荐关系做标签：

SELECT COALESCE(CAST(referrer_id AS VARCHAR), '无推荐人') AS 来源,
       COUNT(*) AS 人数
FROM demo_customers
GROUP BY 来源
ORDER BY 人数 DESC;

结果：有推荐人 4 人、无推荐人 3 人。如果某列保留原始 NULL，可以写 COALESCE(列, '未知') 把空值统一成可读标签。

NULLIF(a, b)：a = b 时返回 NULL，否则返回 a。常用来防除零：

SELECT ROUND(SUM(i.quantity * i.unit_price) / NULLIF(COUNT(DISTINCT o.order_id), 0), 2) AS aov
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid';

结果 441.92。分母为 0 时结果是 NULL，而不是报错。

2.3 行列转换：用 CASE WHEN 做 PIVOT

原始结果是一行一个"月 + 状态"，报表想要一行一月、每个状态一列：

SELECT strftime(created_at, '%Y-%m') AS 月份,
       SUM(CASE WHEN status = 'paid'     THEN 1 ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded,
       SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) AS canceled
FROM demo_orders
GROUP BY strftime(created_at, '%Y-%m')
ORDER BY 月份;
月份 paid refunded canceled
2024-03 6 1 1
2024-04 4 0 0

这就是"行转列"：分组键做行，条件做列，SUM(CASE WHEN ... THEN 1 ELSE 0 END) 填格子。DuckDB 和 Spark 2.4+ 支持 PIVOT 语法，Hive 没有；需要跨引擎时用 CASE 写法最稳。

2.4 CTE 与递归 CTE

普通 CTE 用 WITH 名字 AS (...)，把复杂查询拆成积木：

WITH order_amount AS (
    SELECT o.order_id, SUM(i.quantity * i.unit_price) AS amt
    FROM demo_orders o
    JOIN demo_order_items i ON i.order_id = o.order_id
    WHERE o.status = 'paid'
    GROUP BY o.order_id
)
SELECT COUNT(*) AS 订单数, ROUND(AVG(amt), 2) AS aov
FROM order_amount;

递归 CTE 多一个 RECURSIVE，由"起点"和"从上一轮生成下一轮"两部分组成：

WITH RECURSIVE dates(d) AS (
    SELECT DATE '2024-03-01'
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < DATE '2024-03-07'
)
SELECT d FROM dates;

返回 7 天。用它可以生成日期骨架、补全没有数据的日期、展开层级关系。

注意：递归 CTE 在 DuckDB、PostgreSQL、MySQL 8 可用；Hive 不支持，Spark 3.5 也不支持 WITH RECURSIVE（Spark 4.0 才加入）。这些引擎里常用迭代任务或窗口函数替代，使用前先确认版本。

2.5 日期函数与方言差异

SELECT date_trunc('month', DATE '2024-03-15');                -- 2024-03-01 00:00:00
SELECT strftime(DATE '2024-03-15', '%Y-%m');                  -- '2024-03'
SELECT datediff('day', DATE '2024-03-01', DATE '2024-03-31'); -- 30
SELECT DATE '2024-03-15' - INTERVAL 1 DAY;                    -- 2024-03-14
需求 DuckDB Hive Spark SQL
截断到月 date_trunc('month', d) trunc(d, 'MM') date_trunc('month', d)
格式化 strftime(d, '%Y-%m') date_format(d, 'yyyy-MM') date_format(d, 'yyyy-MM')
日期差 datediff('day', a, b) datediff(b, a) datediff(b, a)
加一天 d + INTERVAL 1 DAY date_add(d, 1) date_add(d, 1)

2.6 集合运算

UNION 合并两个结果并去重；UNION ALL 保留重复；EXCEPT 取差集；INTERSECT 取交集。要求列数相同、类型兼容。

玩具库没有登录/浏览行为表，这里比较"有有效购买的客户"和"有评价的客户"：

WITH buyers AS (
    SELECT DISTINCT customer_id
    FROM demo_orders
    WHERE status = 'paid'
),
reviewers AS (
    SELECT DISTINCT o.customer_id
    FROM demo_orders o
    JOIN demo_reviews r ON r.order_id = o.order_id
)
SELECT
    (SELECT COUNT(*) FROM (SELECT customer_id FROM buyers INTERSECT SELECT customer_id FROM reviewers) t) AS 交集,
    (SELECT COUNT(*) FROM (SELECT customer_id FROM buyers UNION SELECT customer_id FROM reviewers) t) AS 并集,
    (SELECT COUNT(*) FROM (SELECT customer_id FROM buyers UNION ALL SELECT customer_id FROM reviewers) t) AS 直接拼接,
    (SELECT COUNT(*) FROM (SELECT customer_id FROM buyers EXCEPT SELECT customer_id FROM reviewers) t) AS 只在买家;

买家 6 人，评价客户 5 人；交集 5 人，并集 6 人，UNION ALL 直接拼接 11 行，只在买家侧、没有评价的 1 人（客户 5）。

注意：UNION 要去重，通常比 UNION ALL 多一次排序或哈希，数据量大时优先想清楚是否真的需要去重。

2.7 复杂业务题的拆法

复购率：先聚合到用户粒度，再算比例。

WITH per_user AS (
    SELECT customer_id, COUNT(DISTINCT order_id) AS cnt
    FROM demo_orders
    WHERE status = 'paid'
    GROUP BY customer_id
)
SELECT COUNT(*) AS 下单用户数,
       SUM(CASE WHEN cnt >= 2 THEN 1 ELSE 0 END) AS 复购用户数,
       ROUND(SUM(CASE WHEN cnt >= 2 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS 复购率
FROM per_user;

结果：6 人下单，3 人复购，复购率 50.00%。

品类占比：分子是品类销售额，分母是全站销售额。

SELECT p.category,
       ROUND(SUM(i.quantity * i.unit_price), 2) AS 销售额,
       ROUND(SUM(i.quantity * i.unit_price) * 100.0
             / (SELECT SUM(i2.quantity * i2.unit_price)
                FROM demo_order_items i2
                JOIN demo_orders o2 ON o2.order_id = i2.order_id
                WHERE o2.status = 'paid'), 2) AS 占比
FROM demo_order_items i
JOIN demo_orders o ON o.order_id = i.order_id
JOIN demo_products p ON p.product_id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY 销售额 DESC;

电子 92.37%、家居 4.03%、文具 3.60%。

月度环比：本月相对上月的增长率，需要窗口函数 LAG：

WITH monthly AS (
    SELECT strftime(o.created_at, '%Y-%m') AS 月份,
           SUM(i.quantity * i.unit_price) AS gmv
    FROM demo_orders o
    JOIN demo_order_items i ON i.order_id = o.order_id
    WHERE o.status = 'paid'
    GROUP BY strftime(o.created_at, '%Y-%m')
)
SELECT 月份, ROUND(gmv, 2) AS gmv,
       ROUND((gmv / LAG(gmv) OVER (ORDER BY 月份) - 1) * 100, 2) AS 环比
FROM monthly
ORDER BY 月份;

2024-03 是首月，没有上月，环比为 NULL；2024-04 环比 -15.36%。窗口函数的完整用法见窗口函数。

3. 动手示例

3.1 用 CASE 给订单分档

WITH order_amount AS (
    SELECT o.order_id, SUM(i.quantity * i.unit_price) AS amt
    FROM demo_orders o
    JOIN demo_order_items i ON i.order_id = o.order_id
    WHERE o.status = 'paid'
    GROUP BY o.order_id
)
SELECT CASE
           WHEN amt < 100 THEN '100以下'
           WHEN amt < 300 THEN '100-300'
           WHEN amt < 500 THEN '300-500'
           ELSE '500以上'
       END AS 档位,
       COUNT(*) AS 订单数
FROM order_amount
GROUP BY 档位
ORDER BY MIN(amt);

结果合计 10 单：

档位 订单数
100以下 2
100-300 4
300-500 2
500以上 2

先聚合到订单粒度再分档，否则一个多明细订单会被分进多个档位。

4. 常见坑

  1. 标量子查询返回多行。WHERE x = (SELECT ...) 要求子查询只返回一行，否则报错。
  2. NOT IN 遇到 NULL。结果可能为空，改用 NOT EXISTS。
  3. 相关子查询在大表上很慢。每行执行一次，能改成 JOIN 或先聚合就改。
  4. CASE 顺序写反。从上往下命中第一个，宽条件放前面会把后面的分支吃掉。
  5. 忘了 ELSE。不匹配时返回 NULL，报表里会多出空档位。
  6. UNION 和 UNION ALL 混用。前者去重、后者不去重，行数会不同。
  7. 递归 CTE 没有终止条件。WHERE 条件写错会一直递归，直到报错或耗尽资源。
  8. 日期当字符串比较。能自动转换时不报错，但格式一变就出错，统一用 DATE '...'。

5. 在线练习

题目 考点
176. 第二高的薪水 子查询 + NULL
177. 第 N 高的薪水 变量/窗口
184. 部门工资最高的员工 分组极值 + JOIN
185. 部门工资前三高的所有员工 窗口排名
196. 删除重复的电子邮箱 自连接删除

自测：

  • 能写出“每组 Top N”的两种解法：窗口函数、相关子查询。
  • 能处理“找不到结果返回 NULL”的边界。
  • 能解释 DENSE_RANK 和 ROW_NUMBER 的区别。

6. 面试延伸

面试题：UNION 和 UNION ALL 有什么区别？

是什么：UNION 合并结果并去重，UNION ALL 直接拼接、保留重复。两者都要求列数相同、类型兼容。

解决什么问题：确定没有重复或业务允许重复时，用 UNION ALL 更快；需要唯一结果时才用 UNION。写报表时先想清楚重复行是否应该存在。

我的实操边界：做多来源汇总时会先确认是否存在重叠用户或订单，再用 UNION ALL 加最终去重，避免中间步骤反复排序。

面试题：IN 和 EXISTS 怎么选？

是什么：IN 判断值是否在子查询结果集中；EXISTS 判断子查询是否至少返回一行，找到就停。

解决什么问题：子查询结果可能有 NULL 时，NOT IN 会失效，应改用 NOT EXISTS。相关条件下 EXISTS 往往更快，非相关的小结果集用 IN 也直观。

我的实操边界：找"没有发生过"的实体时固定用 NOT EXISTS；写完后会用行数和对账验证结果。

面试题：递归 CTE 能解决什么问题？

是什么：递归 CTE 由起点和递归步组成，重复执行直到不满足条件。

解决什么问题：生成日期序列、展开组织层级、沿父子关系向上或向下查找、补全没有数据的日期。

我的实操边界：本地用 DuckDB 练过日期骨架和简单层级；生产环境的 Hive / Spark 版本支持情况不同，我了解原理，未在大规模集群上使用。

7. 本章验收

  • 能分辨标量、IN、EXISTS、相关子查询，并各写出一条。
  • 能说清 NOT IN 的 NULL 陷阱，知道用 NOT EXISTS 替代。
  • 能用 CASE WHEN 做条件聚合和行转列，知道 ELSE 的作用。
  • 会用 COALESCE 补空值、用 NULLIF 防除零。
  • 能写出普通 CTE 和简单的递归 CTE，并说明终止条件。
  • 能说出 DuckDB 与 Hive / Spark 在日期函数上的至少 3 个差异。
  • 能区分 UNION、UNION ALL、EXCEPT、INTERSECT。
  • 能独立算出复购率 50.00%、品类占比和月度环比，并说清每步的粒度。
  • 5 道在线练习全部提交通过。

下一章

窗口函数：不减少行数做排名、占比、环比和连续下单，把"每个分组的前 N 名"一次写完。

本章任务0 / 12
本页目录