滴滴：分析顺风车订单中实际行驶时长比预估时长多 20% 以上的订单占比。

考察要点

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

1. 业务逻辑转换能力：能否将“实际时长比预估时长多 20% 以上”这个业务需求，精确地翻译成 SQL 中的 WHERE 或 CASE WHEN 条件。
2. 条件聚合函数：是否熟练掌握使用 CASE WHEN 结合 COUNT 或 SUM 在一次全表扫描中完成复杂统计，这是衡量 SQL 熟练度的重要指标。
3. 数据精度与边界处理：是否注意到整数除法可能导致结果为 0 的问题，以及如何处理 NULL 值或分母为 0 等异常情况。

核心概念

本题的核心是条件聚合（Conditional Aggregation），即在一次聚合查询中，根据不同条件计算多个指标。

1. CASE WHEN 表达式: 这是实现条件聚合的基石。其标准语法如下：

   sql

   ⧉⛶

    1CASE
    2    WHEN condition1 THEN result1
    3    WHEN condition2 THEN result2
    4    ...
    5    ELSE result_else
    6END

   在本题中，我们用它来“标记”出符合条件的订单。

2. 结合聚合函数: CASE WHEN 可以无缝嵌套在 COUNT, SUM, AVG 等聚合函数内部。

   • COUNT(CASE WHEN ... THEN 1 END): CASE 表达式在满足条件时返回 1，否则返回 NULL。COUNT 函数只计算非 NULL 的值，因此这等价于计算满足条件的行数。这是最标准、最高效的写法。
   • SUM(CASE WHEN ... THEN 1 ELSE 0 END): CASE 表达式在满足条件时返回 1，不满足时返回 0。SUM 将这些 1 和 0 加起来，同样得到满足条件的行数。性能上与 COUNT 写法几乎无差别。

3. 浮点数除法: 在 SQL 中，两个整数相除的结果默认可能还是整数（取决于具体数据库实现，但为了可移植性和准确性，应始终注意）。例如 5 / 10 结果是 0。

   • 解决方案: 必须将分子或分母至少一个转换为浮点数或高精度小数。
     • 乘以 1.0: COUNT(...) * 1.0 / COUNT(*)
     • 使用 CAST: CAST(COUNT(...) AS DECIMAL(10, 4)) / COUNT(*)

4. 数据表结构（假设）: 面试时可以先和面试官确认表结构，如果未提供，可以主动提出一个合理的假设。 表名: hitch_orders

   列名	类型	描述
   order_id	BIGINT	订单唯一 ID
   estimated_duration	INT	预估行驶时长（分钟）
   actual_duration	INT	实际行驶时长（分钟）
   city_id	INT	城市 ID
   create_time	DATETIME	订单创建时间

多种解法

解法 1：单次扫描 + 条件聚合（最优解）

这是最推荐的解法，因为它只需要对表进行一次扫描，性能最高，代码也最简洁。

• 思路: 使用 COUNT(CASE WHEN ...) 计算分子，COUNT(*) 计算分母，并在同一次查询中完成除法。
• 版本: ✅ 所有主流数据库版本（MySQL, PostgreSQL, Oracle, SQL Server）均支持。
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    COUNT(CASE WHEN actual_duration > estimated_duration * 1.2 THEN 1 END) * 100.0 / COUNT(*) AS overtime_percentage
   3FROM
   4    hitch_orders
   5WHERE
   6    -- 增加过滤条件保证数据质量，避免除零或负数等无效数据干扰
   7    estimated_duration > 0 AND actual_duration > 0;

• 时间复杂度: $O(N)$，其中 N 是表 hitch_orders 的行数。需要一次全表扫描。
• 空间复杂度: $O(1)$，聚合计算仅需常数级别的内存。
• 索引依赖: 由于需要计算 COUNT(*)，必须扫描全表，无法通过索引来显著优化此特定查询。即使 (estimated_duration, actual_duration) 上有索引，也无法避免全表扫描来获取总数。

解法 2：CTE（通用表表达式）+ AVG（代码可读性更佳）

当判断逻辑变得复杂时，使用 CTE 可以让 SQL 结构更清晰，更易于维护和调试。

• 思路: 先用一个 CTE 将每一行标记为是否超时（1 或 0），然后在主查询中对这个标记列求平均值 AVG()，结果乘以 100 就是百分比。
• 版本: ✅ MySQL 8.0+, PostgreSQL, Oracle, SQL Server 等支持 CTE 的版本。
• SQL 代码:
  sql

  ⧉⛶

   1WITH OrderFlags AS (
   2    SELECT
   3        CASE
   4            WHEN actual_duration > estimated_duration * 1.2 THEN 1
   5            ELSE 0
   6        END AS is_overtime
   7    FROM
   8        hitch_orders
   9    WHERE
  10        estimated_duration > 0 AND actual_duration > 0
  11)
  12SELECT
  13    AVG(is_overtime) * 100 AS overtime_percentage
  14FROM
  15    OrderFlags;

• 时间复杂度: $O(N)$。数据库优化器通常会将此查询视作与解法 1 等效，执行计划基本一致，也是一次全表扫描。
• 空间复杂度: $O(1)$。逻辑上存在中间表，但物理执行时通常是流式处理，不会物化整个 CTE。
• 索引依赖: 同解法 1，无法通过索引优化。

常见低分回答

1. 整数除法导致结果错误:

   sql

   ⧉⛶

    1-- 错误示例：100 / 300 的结果是 0，而不是 0.3333
    2SELECT COUNT(CASE WHEN actual_duration > estimated_duration * 1.2 THEN 1 END) * 100 / COUNT(*) FROM hitch_orders;

   错误原因: COUNT() 返回整数，* 100 也是整数，两个整数相除结果被截断为整数 0。没有正确处理浮点数运算。

2. 错误使用 WHERE 子句:

   sql

   ⧉⛶

    1-- 错误示例：这只计算了超时订单的数量，而不是占比
    2SELECT COUNT(*) FROM hitch_orders WHERE actual_duration > estimated_duration * 1.2;

   错误原因: 逻辑混淆。将筛选条件放在 WHERE 中，导致 COUNT(*) 计算的是分子（超时订单数），而不是分母（总订单数）。

3. 多次查询，效率低下:

   sql

   ⧉⛶

    1-- 错误示例：在应用代码中执行两次查询
    2// 第一次查询
    3SELECT COUNT(*) FROM hitch_orders WHERE actual_duration > estimated_duration * 1.2;
    4// 第二次查询
    5SELECT COUNT(*) FROM hitch_orders;
    6// 在代码中计算百分比

   错误原因: 对同一张大表进行了两次全表扫描，网络开销和数据库负载都增加了一倍。面试官期望在数据库端用一条 SQL 完成计算。

4. 忽略数据质量和边界条件:

   sql

   ⧉⛶

    1-- 有风险的示例
    2SELECT COUNT(CASE WHEN actual_duration / estimated_duration > 1.2 THEN 1 END) * 100.0 / COUNT(*) FROM hitch_orders;

   错误原因: 如果 estimated_duration 存在为 0 的行，这将导致“division by zero”错误。虽然题目给的条件 actual_duration > estimated_duration * 1.2 巧妙地避免了这个问题，但如果候选人改写成除法形式，就必须考虑分母为 0 的情况，并使用 NULLIF(estimated_duration, 0) 或 WHERE estimated_duration > 0 来规避。

工程实践与优化

• 数据量级问题: 当 hitch_orders 表达到十亿、百亿级别时，每次都进行全表扫描是不可接受的，会导致查询缓慢并消耗大量数据库资源。
• 解决方案:
  1. 预聚合/物化视图: 这是最常见的 OLAP 解决方案。创建一个每日（或每小时）的聚合结果表 dws_hitch_order_kpi。通过定时任务（如 Airflow, DolphinScheduler）在业务低峰期运行聚合 SQL，将结果（如总订单数、超时订单数、城市等维度）写入该表。线上的查询直接从这个小得多的结果表中获取数据，响应时间可以做到毫秒级。
  2. 采样查询: 如果不需要 100% 精确的结果，只是为了趋势分析，可以进行采样。
     sql

     ⧉⛶

      1-- MySQL 中一种不精确但快速的采样方式
      2SELECT AVG(CASE WHEN actual_duration > estimated_duration * 1.2 THEN 1 ELSE 0 END) * 100
      3FROM hitch_orders WHERE order_id % 100 = 1; -- 假设 order_id 分布均匀，进行 1% 采样

     注意: WHERE RAND() < 0.01 在 MySQL 中性能很差，因为它需要为每一行计算 RAND()，无法有效利用索引。
  3. 使用列存数据库/OLAP引擎: 对于海量数据的分析场景，应使用专门的 OLAP 系统，如 ClickHouse, Doris, StarRocks。它们的列式存储和并行计算能力天生适合这类全列扫描的聚合查询，性能比行存的 MySQL 高出几个数量级。
• 执行计划分析:
  • 对于解法 1 或 2，在 MySQL 中 EXPLAIN 会显示：
    • type: ALL：表示全表扫描。
    • Extra: Using where：表示应用了 WHERE 子句中的过滤条件。
  • 这是符合预期的，因为没有索引可以完全覆盖这个查询。优化的重点不在于 SQL 本身，而在于改变数据架构（预聚合）或更换技术栈（OLAP 引擎）。

常见追问与延伸题目

1. 追问：如果要按城市分析这个超时订单占比，SQL 该怎么写？

   • 回答要点: 这考察的是 GROUP BY 的使用。我们只需在查询中加入 city_id 列，并在末尾添加 GROUP BY city_id 即可。
   • SQL 示例:
     sql

     ⧉⛶

      1SELECT
      2    city_id,
      3    COUNT(CASE WHEN actual_duration > estimated_duration * 1.2 THEN 1 END) * 100.0 / COUNT(*) AS overtime_percentage
      4FROM
      5    hitch_orders
      6WHERE
      7    estimated_duration > 0 AND actual_duration > 0
      8GROUP BY
      9    city_id
     10HAVING
     11    COUNT(*) > 1000; -- 可以增加 HAVING 子句过滤掉订单量过少的城市，使统计更有意义

2. 追问：如果数据量增长 100 倍，达到百亿级别，你这条 SQL 会有什么问题？怎么优化？

   • 回答要点:
     • 问题: 单机 MySQL 无法在可接受的时间内完成百亿级表格的全表扫描。查询会非常慢（可能数小时），并可能拖垮整个数据库实例。
     • 优化方案:
       1. 架构层面: 引入 OLAP 解决方案。将数据从业务库（如 MySQL）通过 ETL 同步到 ClickHouse、Doris 或大数据平台（Hive/Spark）。在这些专为分析设计的系统上，百亿级聚合查询可以在秒级或分钟级完成。
       2. 应用层面: 如果不能引入新组件，唯一的方法是预计算。创建一张日维度的城市级聚合表 daily_city_hitch_summary，包含 dt, city_id, total_orders, overtime_orders 等字段。每天凌晨定时任务计算前一天的数据并插入此表。查询时直接查这张汇总表，即使原始数据有百亿行，汇总表也只有 城市数量 * 天数 行，查询极快。

3. 追问：如果 actual_duration 和 estimated_duration 列存在 NULL 值，你的查询会如何处理？这符合预期吗？

   • 回答要点:
     • SQL 的行为: 在 CASE WHEN actual_duration > estimated_duration * 1.2 THEN 1 END 中，如果 actual_duration 或 estimated_duration 为 NULL，那么 > 比较的结果是 UNKNOWN (等同于 NULL)，CASE 表达式的结果也是 NULL。COUNT(NULL) 的结果是 0，所以这些行不会被计为分子（超时订单）。同时，COUNT(*) 会把这些行计入分母。
     • 是否符合预期: 这取决于业务需求。通常，数据不完整的订单应该被排除在统计之外，即分子和分母都不应包含它们。
     • 修正后的查询: 应该在 WHERE 子句中明确排除 NULL 值，这比依赖 CASE 的隐式行为更清晰、更安全。
       sql

       ⧉⛶

        1SELECT
        2    COUNT(CASE WHEN actual_duration > estimated_duration * 1.2 THEN 1 END) * 100.0 / COUNT(*) AS overtime_percentage
        3FROM
        4    hitch_orders
        5WHERE
        6    actual_duration IS NOT NULL
        7    AND estimated_duration IS NOT NULL
        8    AND estimated_duration > 0;

     这体现了候选人对 NULL 语义的深刻理解和代码的严谨性。