SQL 实战编程题（GitHub 高频题） · Top N / 排名 / 分位数

求出每个季度每个商品的同比增长率。(LAG)

Calculate the year-over-year (YoY) growth rate for each product per quarter. (LAG

答案语言

考察要点

这道题面试官想看你是否掌握以下核心能力：

窗口函数（Window Functions）：特别是 LAG 函数的熟练运用，这是解决时间序列分析问题的标准利器。
复杂逻辑拆解能力：能否将“计算季度同比增长率”这个业务需求，拆解为“按季度聚合”、“定位去年同期数据”、“计算比率”等多个 SQL 步骤，并用 CTE (Common Table Expression)清晰地组织起来。
SQL 健壮性：是否考虑到除数为零（如第一年没有同期数据）等边界情况，并使用 NULLIF 或 CASE WHEN 进行优雅处理。

核心概念

本题的核心是围绕窗口函数 LAG 和时间序列数据处理展开的。

窗口函数 (Window Function) 窗口函数对与当前行相关的多行（一个“窗口”）进行计算，但不会像 GROUP BY 那样将多行折叠为一行，而是为结果集中的每一行都返回一个值。
- 语法：function_name() OVER (PARTITION BY ... ORDER BY ...)
- PARTITION BY：定义窗口的范围。计算会在每个分区内独立进行。在本题中，我们需要对每个商品、每个季度分别计算，因此分区键是 (product_id, quarter_of_year)。
- ORDER BY：定义分区内行的顺序。LAG 函数依赖这个顺序来找到“前一行”。在本题中，我们需要按年份排序来找到“前一年”的数据。
LAG 函数 LAG 是一个位移函数，用于访问当前行之前的某一行的数据。
- 语法：LAG(expression, offset, default) OVER (PARTITION BY ... ORDER BY ...)
- expression：要获取其值的列或表达式，例如 total_amount。
- offset：相对于当前行的偏移量，默认为 1，即前一行。
- default：如果指定的行不存在（例如分区的第一行），则返回此默认值。若不指定，则返回 NULL。
同比增长 (Year-over-Year, YoY) vs 环比增长 (Quarter-over-Quarter, QoQ) 这是一个关键的概念区分，面试时主动澄清可以加分。
- 同比增长 (YoY)：指当前周期与上年同期相比的增长。例如，2023年Q2 vs 2022年Q2。本题明确要求“同比”。
- 环比增长 (QoQ)：指当前周期与上一个相邻周期相比的增长。例如，2023年Q2 vs 2023年Q1。
数据准备与聚合 原始数据通常是逐条销售记录，必须先将其聚合到我们分析的粒度——“商品-季度”。

示例数据 (sales 表):

product_id sale_date amount
P1 2022-02-15 100
P1 2022-05-20 150
P1 2023-02-10 120
P1 2023-05-25 180
P2 2022-02-01 200
P2 2023-03-01 250

第一步：聚合为季度销售额

product_id sale_year sale_quarter quarterly_amount
P1 2022 1 100
P1 2022 2 150
P1 2023 1 120
P1 2023 2 180
P2 2022 1 200
P2 2023 1 250

最终期望输出：

product_id year_quarter quarterly_amount prev_year_amount yoy_growth_rate
P1 2022-Q1 100 NULL NULL
P1 2022-Q2 150 NULL NULL
P1 2023-Q1 120 100 0.2000
P1 2023-Q2 180 150 0.2000
P2 2022-Q1 200 NULL NULL
P2 2023-Q1 250 200 0.2500

product_id	sale_date	amount
P1	2022-02-15	100
P1	2022-05-20	150
P1	2023-02-10	120
P1	2023-05-25	180
P2	2022-02-01	200
P2	2023-03-01	250

product_id	sale_year	sale_quarter	quarterly_amount
P1	2022	1	100
P1	2022	2	150
P1	2023	1	120
P1	2023	2	180
P2	2022	1	200
P2	2023	1	250

product_id	year_quarter	quarterly_amount	prev_year_amount	yoy_growth_rate
P1	2022-Q1	100	NULL	NULL
P1	2022-Q2	150	NULL	NULL
P1	2023-Q1	120	100	0.2000
P1	2023-Q2	180	150	0.2000
P2	2022-Q1	200	NULL	NULL
P2	2023-Q1	250	200	0.2500

多种解法

解法 1：使用窗口函数 `LAG` (推荐)

这是最现代、最高效、最易读的解法。

思路：
1. 使用 CTE quarterly_sales 按商品和季度聚合销售额。
2. 在第二个 CTE 或最终 SELECT 中，使用 LAG 函数。关键在于 PARTITION BY product_id 和 QUARTER(sale_date)，并 ORDER BY YEAR(sale_date)，这样就能精确地找到去年同季度的数据。
3. 计算增长率，并用 NULLIF 处理分母为0的情况。
版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 不支持窗口函数。
SQL 实现：

sql

 1WITH quarterly_sales AS (
 2    -- 步骤1: 按商品和季度聚合销售额
 3    SELECT
 4        product_id,
 5        YEAR(sale_date) AS sale_year,
 6        QUARTER(sale_date) AS sale_quarter,
 7        SUM(amount) AS quarterly_amount
 8    FROM
 9        sales
10    GROUP BY
11        product_id, sale_year, sale_quarter
12)
13-- 步骤2 & 3: 使用LAG计算同比增长率
14SELECT
15    product_id,
16    CONCAT(sale_year, '-Q', sale_quarter) AS year_quarter,
17    quarterly_amount,
18    -- 核心：按商品和季度分区，按年排序，取上一年的数据
19    LAG(quarterly_amount, 1) OVER (
20        PARTITION BY product_id, sale_quarter
21        ORDER BY sale_year
22    ) AS prev_year_amount,
23    -- (当期值 - 同期值) / 同期值
24    (quarterly_amount - LAG(quarterly_amount, 1) OVER (
25        PARTITION BY product_id, sale_quarter
26        ORDER BY sale_year
27    )) / NULLIF(LAG(quarterly_amount, 1) OVER (
28        PARTITION BY product_id, sale_quarter
29        ORDER BY sale_year
30    ), 0) AS yoy_growth_rate
31FROM
32    quarterly_sales
33ORDER BY
34    product_id, year_quarter;

复杂度：
- 时间复杂度： $O(N \log N)$ 。其中 $N$ 是 sales 表的行数。主要开销在于初始的 GROUP BY 和窗口函数内部的 PARTITION BY ... ORDER BY 排序。
- 空间复杂度： $O(M)$ ，其中 $M$ 是聚合后的季度数据行数，用于存储 CTE 的中间结果和窗口计算。
索引依赖：在 sales 表上建立复合索引 (product_id, sale_date) 可以极大地加速 GROUP BY 聚合过程。如果 amount 也包含在内，即 (product_id, sale_date, amount)，则可以实现覆盖索引，性能更佳。

解法 2：使用自连接 `SELF JOIN` (兼容老版本)

在不支持窗口函数的旧版数据库（如 MySQL 5.7）中，可以通过自连接模拟 LAG 的行为。

思路：
1. 同样，先用 CTE quarterly_sales 聚合季度销售额。
2. 将 quarterly_sales 表自连接。连接条件是 product_id 和 sale_quarter 相同，且当前年份 c1.sale_year 等于上一年份 c2.sale_year + 1。
3. 使用 LEFT JOIN 以确保没有去年同期数据的季度（如第一年）也能出现在结果中。
版本：✅ 所有主流数据库版本。
SQL 实现：

sql

 1WITH quarterly_sales AS (
 2    -- 步骤1: 按商品和季度聚合销售额
 3    SELECT
 4        product_id,
 5        YEAR(sale_date) AS sale_year,
 6        QUARTER(sale_date) AS sale_quarter,
 7        SUM(amount) AS quarterly_amount
 8    FROM
 9        sales
10    GROUP BY
11        product_id, sale_year, sale_quarter
12)
13-- 步骤2: 自连接来匹配去年同期数据
14SELECT
15    c1.product_id,
16    CONCAT(c1.sale_year, '-Q', c1.sale_quarter) AS year_quarter,
17    c1.quarterly_amount,
18    c2.quarterly_amount AS prev_year_amount,
19    (c1.quarterly_amount - c2.quarterly_amount) / NULLIF(c2.quarterly_amount, 0) AS yoy_growth_rate
20FROM
21    quarterly_sales c1
22LEFT JOIN
23    quarterly_sales c2 ON c1.product_id = c2.product_id      -- 商品相同
24                       AND c1.sale_quarter = c2.sale_quarter  -- 季度相同
25                       AND c1.sale_year = c2.sale_year + 1    -- 年份差1
26ORDER BY
27    c1.product_id, year_quarter;

复杂度：
- 时间复杂度： $O(N \log N + M^2)$ 或 $O(N \log N + M \log M)$ 。 $O(N \log N)$ 来自初始聚合。 $JOIN$ 的复杂度取决于中间结果集 $M$ 的大小和连接策略。如果没有合适的索引（在 CTE 上无法直接创建），可能是嵌套循环连接，趋向于 $O(M^2)$ 。如果数据库优化器能够使用哈希连接或排序合并连接，则趋向于 $O(M \log M)$ 。通常劣于窗口函数方案。
- 空间复杂度： $O(M)$ ，用于存储 CTE 结果。
索引依赖：同解法1，sales 表上的 (product_id, sale_date) 索引至关重要。

常见低分回答

混淆同比和环比：将 PARTITION BY product_id ORDER BY sale_year, sale_quarter 用于 LAG。这计算的是环比（与上个季度比），而不是同比（与去年同季度比），完全曲解了题意。
忘记处理除零错误：直接使用 (curr - prev) / prev 进行计算。当 prev 为 0 或 NULL 时，SQL 会报错或返回 NULL，不够健壮。应使用 NULLIF(prev, 0)。
在 GROUP BY 之前使用函数：在 WHERE 子句中对 sale_date 使用 QUARTER() 或 YEAR() 等函数，如 WHERE YEAR(sale_date) = 2023。这会导致 sale_date 列上的索引失效，在大数据量下引发全表扫描，性能极差。应改为范围查询 WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01'。
自连接条件错误：在解法2中，JOIN 条件写错，例如忘记 c1.product_id = c2.product_id，导致数据错误关联。或者年份关系写成 c1.sale_year > c2.sale_year，会匹配到多个历史年份，造成数据重复和逻辑错误。

工程实践与优化

物化视图/汇总表：对于报表、BI 等需要频繁查询同比增长率的场景，直接查询原始 sales 表（可能是百亿、千亿级别）是不可行的。最佳实践是创建一个 product_quarterly_sales 的汇总表，通过离线任务（如每日或每小时的 Spark/Flink 作业）进行预聚合。这样，线上查询只需在小得多的汇总表上进行，响应速度可以从分钟级提升到毫秒级。
执行计划分析 (EXPLAIN)：
- 对于解法1，关注 GROUP BY 是否利用了索引（Extra 字段没有 Using filesort; Using temporary）。窗口函数部分会显示为 Window aggregate。
- 对于解法2，重点关注 JOIN 的 type。理想情况下是 eq_ref 或 ref。如果 type 是 ALL 并且 rows 很大，说明连接效率低下。Extra 字段可能会显示 Using join buffer，表明连接条件没有有效利用索引。
索引选择：sales 表上的 (product_id, sale_date, amount) 覆盖索引是性能的关键。product_id 在前，因为它是高基数的等值查询/分组字段。sale_date 在后，用于范围过滤和排序。amount 放在最后，使得聚合操作可以直接从索引中获取数据，无需回表查询主键。
避免大事务：这种分析型查询（AP）应在只读副本上执行，避免对主库（OLTP）造成长时间的读锁或资源争抢，影响线上交易。

常见追问与延伸题目

追问：如果数据量增长100倍，达到百亿级别，你的SQL还能跑吗？怎么优化？
- 回答要点1（SQL层面已达极限）：直接在百亿级原始表上执行此SQL是不可行的。即使有索引，GROUP BY 扫描和聚合的数据量依然巨大，会导致数据库 I/O、CPU 资源耗尽，查询可能要数小时甚至失败。
- 回答要点2（架构层面优化）：必须引入数据仓库或大数据处理架构。
  - 预聚合：如上所述，创建汇总表是核心方案。通过 Spark/Flink/Hive 等大数据引擎，以分布式方式高效地完成从原始日志到季度汇总表的ETL过程。查询直接作用于这个小得多的汇总表。
  - 列式存储：将数据存储在像 Parquet 或 ORC 这样的列式存储格式中，并使用 Presto/ClickHouse/Doris 等面向分析的查询引擎。列存只读取查询涉及的列（product_id, sale_date, amount），极大减少I/O。这些引擎的分布式计算能力也能轻松处理百亿级数据的聚合和窗口计算。
追问：刚才计算的是同比增长率，现在请计算“滚动年销售额”（Rolling 12-month sales）。
- 回答要点：这是一个很好的延伸，考察对窗口函数框架（ROWS / RANGE）的理解。
- 思路：首先需要按月聚合 monthly_sales。然后使用窗口函数 SUM(...) OVER (...)。
- 窗口定义：PARTITION BY product_id ORDER BY sale_month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW。这个窗口精确定义了“包括当前月在内的过去12个月”，SUM(monthly_amount) 在这个窗口上计算即可得到滚动12个月的销售总额。
追问：如果我想看每个商品，在所有季度中，同比增长率最高的那个季度是哪个？
- 回答要点：这是在刚才结果基础上的一个 Top-N 问题，再次考察窗口函数或子查询。
- 思路1 (窗口函数 RANK/ROW_NUMBER)：在已经计算出 yoy_growth_rate 的结果集上，再套一层 CTE。使用 RANK() OVER (PARTITION BY product_id ORDER BY yoy_growth_rate DESC) as rnk。最后筛选 WHERE rnk = 1。
- 思路2 (关联子查询)：也可以用一个子查询找到每个 product_id 最大的 yoy_growth_rate，然后 JOIN 回原始结果表。但窗口函数通常更简洁高效。