Airbnb 面试：找出预订率低于平均值且取消率高于平均值的房源。

考察要点

这道题面试官想看你是否懂：

1. 复杂指标的 SQL 表达能力：如何将“预订率”和“取消率”这种业务概念，转化为 SUM(CASE WHEN ...) / COUNT(*) 这样的精确 SQL 聚合。
2. 多层次聚合查询：如何在一次查询中同时处理“单个房源”粒度的指标和“全局平均”粒度的指标，并进行比较。这通常涉及 CTE、子查询或窗口函数。
3. SQL 查询优化意识：是否能写出逻辑清晰、可读性高且性能优良的 SQL。面试官会通过你的解法选择（例如，优先使用 CTE 而不是重复的子查询）来评估这一点。

核心概念

本题的核心是计算两级聚合指标：房源级指标和全局级指标。

1. 假设数据模型 我们首先需要一个清晰的数据模型。假设我们有一张 bookings 表：

2. 核心指标定义

• 取消率 (Cancellation Rate)：某个房源被取消的预订数 / 该房源的总预订数。
  • SQL 表达: SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) * 1.0 / COUNT(*)
• 预订率 (Booking Rate)：这里我们定义为“确认率”，即某个房源被确认的预订数 / 该房源的总预订数。
  • SQL 表达: SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) * 1.0 / COUNT(*)
  • 注意: 乘以 1.0 是为了将整数运算转为浮点数运算，避免整数除法导致结果为 0。在 PostgreSQL 等数据库中，可以直接使用 ::numeric 或 CAST(... AS DECIMAL)。

3. 关键 SQL 构件

• 条件聚合 (Conditional Aggregation)：使用 CASE WHEN 结合聚合函数（SUM, COUNT）在一次 GROUP BY 中计算多个不同条件的指标。
  sql

  ⧉⛶

   1SELECT
   2    listing_id,
   3    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count,
   4    COUNT(*) AS total_count
   5FROM bookings
   6GROUP BY listing_id;

• 公用表表达式 (Common Table Expression - CTE)：使用 WITH 关键字定义一个或多个临时的、命名的结果集。CTE 使复杂查询更具模块化和可读性，并且可以被后续查询多次引用。
  sql

  ⧉⛶

   1WITH listing_metrics AS (
   2    -- ... 计算每个房源指标的查询 ...
   3)
   4SELECT * FROM listing_metrics WHERE ...;

• 窗口函数 (Window Functions)：在不改变原有行数的情况下，对与当前行相关的行集（窗口）进行计算。AVG(...) OVER () 可以在每一行上附加全局平均值，非常适合本题。
  sql

  ⧉⛶

   1-- 在每行上都计算出所有房源的平均取消率
   2SELECT
   3    listing_id,
   4    cancellation_rate,
   5    AVG(cancellation_rate) OVER () AS global_avg_cancellation_rate
   6FROM listing_metrics;

多种解法

解法 1：使用 CTE（公用表表达式）

这是最标准、最清晰的解法，推荐在面试中使用。它将问题分解为两个逻辑步骤：1) 计算每个房源的指标；2) 计算全局平均指标并进行过滤。

• 版本: ✅ MySQL 8.0+ / PostgreSQL / SQL Server / Oracle
• SQL:

 1WITH listing_metrics AS (
 2    -- 步骤1: 计算每个房源的预订率和取消率
 3    SELECT
 4        listing_id,
 5        -- 使用 AVG(CASE...) 是一种更简洁的写法
 6        AVG(CASE WHEN status = 'confirmed' THEN 1.0 ELSE 0.0 END) AS booking_rate,
 7        AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0.0 END) AS cancellation_rate
 8    FROM
 9        bookings
10    GROUP BY
11        listing_id
12),
13global_avg_metrics AS (
14    -- 步骤2: 基于上一步的结果，计算全局平均值
15    SELECT
16        AVG(booking_rate) AS avg_booking_rate,
17        AVG(cancellation_rate) AS avg_cancellation_rate
18    FROM
19        listing_metrics
20)
21-- 步骤3: 关联房源指标和全局指标，并进行筛选
22SELECT
23    lm.listing_id,
24    lm.booking_rate,
25    lm.cancellation_rate
26FROM
27    listing_metrics lm
28CROSS JOIN -- 全局指标只有一行，CROSS JOIN 即可
29    global_avg_metrics gam
30WHERE
31    lm.booking_rate < gam.avg_booking_rate
32    AND lm.cancellation_rate > gam.avg_cancellation_rate;

• 复杂度:
  • 时间复杂度: $O(N \log N)$ 或 $O(N)$。主要开销在 listing_metrics 的 GROUP BY。如果 listing_id 有索引，数据库可能使用 $O(N)$ 的 Hash Aggregate；否则可能需要排序，为 $O(N \log N)$。$N$ 是 bookings 表的行数。
  • 空间复杂度: $O(M)$，用于存储 listing_metrics 的中间结果。$M$ 是独立 listing_id 的数量。
• 索引依赖: 在 bookings(listing_id, status) 上建立复合索引，可以极大地加速 GROUP BY 的过程，甚至可能实现覆盖索引，避免回表。

解法 2：使用窗口函数

这是一种更现代、更简洁的解法，一步到位，无需显式 JOIN。

• 版本: ✅ MySQL 8.0+ / PostgreSQL 等现代数据库
• SQL:

 1WITH listing_metrics AS (
 2    SELECT
 3        listing_id,
 4        AVG(CASE WHEN status = 'confirmed' THEN 1.0 ELSE 0.0 END) AS booking_rate,
 5        AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0.0 END) AS cancellation_rate
 6    FROM
 7        bookings
 8    GROUP BY
 9        listing_id
10)
11SELECT
12    listing_id,
13    booking_rate,
14    cancellation_rate
15FROM (
16    SELECT
17        listing_id,
18        booking_rate,
19        cancellation_rate,
20        -- 使用窗口函数，在每行上附加全局平均值
21        AVG(booking_rate) OVER () AS avg_booking_rate,
22        AVG(cancellation_rate) OVER () AS avg_cancellation_rate
23    FROM
24        listing_metrics
25) AS t
26WHERE
27    booking_rate < avg_booking_rate
28    AND cancellation_rate > avg_cancellation_rate;

• 复杂度:
  • 时间复杂度: $O(N \log N)$ 或 $O(N)$。与解法 1 类似，瓶颈依然是初始的 GROUP BY。窗口函数的计算通常是 $O(M)$ 或 $O(M \log M)$，$M$ 是窗口大小（这里是所有房源数）。
  • 空间复杂度: $O(M)$，用于存储 listing_metrics 和窗口函数计算的中间结果。
• 索引依赖: 同解法 1，bookings(listing_id, status) 上的索引至关重要。

解法 3：使用多个子查询（兼容老版本，但不推荐）

这种方法在不支持 CTE 或窗口函数的老版本 MySQL（如 5.7）中可用，但可读性和性能较差。

• 版本: ✅ 所有版本 MySQL
• SQL:

 1SELECT
 2    lm.listing_id,
 3    lm.booking_rate,
 4    lm.cancellation_rate
 5FROM
 6    ( -- 子查询计算每个房源的指标
 7        SELECT
 8            listing_id,
 9            AVG(CASE WHEN status = 'confirmed' THEN 1.0 ELSE 0.0 END) AS booking_rate,
10            AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0.0 END) AS cancellation_rate
11        FROM
12            bookings
13        GROUP BY
14            listing_id
15    ) AS lm
16CROSS JOIN
17    ( -- 子查询计算全局平均指标
18        SELECT
19            AVG(booking_rate) AS avg_booking_rate,
20            AVG(cancellation_rate) AS avg_cancellation_rate
21        FROM (
22            SELECT
23                AVG(CASE WHEN status = 'confirmed' THEN 1.0 ELSE 0.0 END) AS booking_rate,
24                AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0.0 END) AS cancellation_rate
25            FROM
26                bookings
27            GROUP BY
28                listing_id
29        ) AS temp_metrics
30    ) AS gam
31WHERE
32    lm.booking_rate < gam.avg_booking_rate
33    AND lm.cancellation_rate > gam.avg_cancellation_rate;

• 说明: 这种写法非常冗长，并且重复计算了 listing_metrics。虽然现代查询优化器可能会识别并优化掉重复计算，但这严重依赖于优化器的智能程度，且代码难以维护。面试中应避免此写法，除非明确要求兼容老版本。

常见低分回答

1. 在 WHERE 子句中使用聚合函数：WHERE cancellation_rate > AVG(cancellation_rate)。这是语法错误，聚合函数不能直接用于 WHERE 子句，必须在 HAVING 子句或子查询/CTE 中使用。
2. HAVING 子句误用：HAVING cancellation_rate > AVG(cancellation_rate)。HAVING 中的 AVG(cancellation_rate) 作用域是当前分组（即单个房源），所以 cancellation_rate 永远等于 AVG(cancellation_rate)，这个条件无效。必须与全局平均值比较。
3. 指标计算错误：使用 COUNT(status = 'cancelled')。在 SQL 标准中，COUNT 只计算非 NULL 值，status = 'cancelled' 的结果是布尔值，行为不确定。正确的应为 COUNT(CASE WHEN status = 'cancelled' THEN 1 END) 或 SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)。
4. 整数除法陷阱：SUM(...) / COUNT(*)。如果 SUM 和 COUNT 都是整数，在很多数据库（如 SQL Server）中会执行整数除法，导致 1 / 2 结果为 0。应乘以 1.0 或使用 CAST 转换为浮点数/小数。
5. 性能糟糕的关联子查询：在 WHERE 中为每一行都执行一次子查询来获取全局平均值。这种写法虽然逻辑上可能正确，但性能极差，因为子查询会被执行 M 次（M 为房源数量）。
   sql

   ⧉⛶

    1-- 反例：性能极差
    2SELECT listing_id, ...
    3FROM listing_metrics lm
    4WHERE lm.cancellation_rate > (SELECT AVG(cancellation_rate) FROM listing_metrics);

工程实践与优化

• 索引优化: 对于亿级 bookings 表，GROUP BY listing_id 是主要瓶颈。必须在 (listing_id) 或 (listing_id, status) 上建立索引。后者是覆盖索引，性能最佳，因为引擎只需扫描索引，无需读取主表数据。
• 物化视图/汇总表: 这种分析类查询在生产环境中不应直接扫大表。最佳实践是，通过离线任务（如每日 Spark/Flink 作业）预先计算每个房源的各项指标（预订数、取消数等），存入一个 listing_daily_summary 的汇总表中。查询时直接基于这个小得多的汇总表进行，响应速度可以从分钟级提升到毫秒级。
• 执行计划分析: 使用 EXPLAIN / EXPLAIN ANALYZE 查看执行计划。
  • 关注 GROUP BY 操作。理想情况是 type: index 或 Extra: Using index，表示利用了索引。避免 Extra: Using temporary; Using filesort，这表示需要创建临时表并在磁盘上排序，性能很差。
  • 检查 CTE 是否被物化。在 MySQL 8.0+ 中，CTE 默认不会被物化，而是被“内联”到主查询中。如果 CTE 被多次引用，可以使用优化器提示 /*+ MATERIALIZE */ 强制物化，避免重复计算。
• 数据倾斜: 如果某些房源（如网红房源）的预订量远超其他房源，GROUP BY listing_id 时可能发生数据倾斜，导致单个 reducer/task 负载过重。在分布式计算引擎（如 Spark SQL）中，需要开启 AQE (Adaptive Query Execution) 或对热点 key 进行特殊处理（如加盐）。

常见追问与延伸题目

1. 追问：如果 bookings 表有 100 亿行，你的 SQL 会有什么问题？如何优化？

   • 回答要点 1 (问题)：直接在 100 亿行数据上执行 GROUP BY 会导致巨大的 I/O 和计算开销，查询可能需要数小时甚至失败。数据库连接可能会超时，并且会长时间占用大量资源，影响线上其他业务。
   • 回答要点 2 (优化)：
     • 架构层面：引入数据仓库和离线计算。将生产库（OLTP）的 bookings 数据通过 ETL 同步到数据仓库（如 Hive, BigQuery, Snowflake）中。
     • 计算层面：使用分布式计算框架（如 Spark, Flink）编写批处理作业，每日或每小时计算 listing_metrics 汇总表。线上查询直接访问这个轻量的汇总表。
     • SQL层面（如果必须在原库查）：如果只是临时查询，可以添加 WHERE 条件缩小范围，例如 WHERE booking_date > 'YYYY-MM-DD'，只分析近期数据。但这治标不治本。

2. 追问：如果要分析的不是“所有时间”，而是“过去 30 天”的预订/取消率，SQL 该如何修改？

   • 回答要点：很简单，在第一个 CTE（listing_metrics）的 FROM 子句后增加一个 WHERE 条件来过滤时间范围。这会显著减少扫描的数据量，性能更好。
   sql

   ⧉⛶

    1WITH listing_metrics AS (
    2    SELECT ...
    3    FROM bookings
    4    WHERE booking_date >= CURDATE() - INTERVAL 30 DAY -- 或其他日期函数
    5    GROUP BY listing_id
    6), ...

   • 索引：此时，bookings 表的索引最好调整为 (listing_id, booking_date, status)，或者至少有 (booking_date) 索引，以便快速筛选时间范围。

3. 追问：如果我想找出“连续 3 次或以上预订都被取消”的用户，该怎么写？

   • 回答要点：这是一个典型的“连续出现”问题，需要使用窗口函数 LAG 或 LEAD 来解决。
   • 思路：
     1. 使用 ROW_NUMBER() 或 RANK() 按用户和预订时间排序，为每个用户的预订记录编号。
     2. 创建一个标记列，如果 status = 'cancelled' 则为 1，否则为 0。
     3. 使用 SUM(...) OVER (PARTITION BY user_id ORDER BY booking_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 来计算一个滑动窗口（当前行+前面2行）内取消的总次数。
     4. 筛选出上述窗口和为 3 的记录。
   • 这道题考察的是对高级窗口函数的掌握，是很好的延伸题。