横扫SQL面试——同比、环比问题_sql 同比

横扫SQL面试

同比、环比问题🎈🎈🎈🎈🎈🎈🎈

---

一、销售数据同比环比增长率📈 📈 📈

销售记录表sales_records，记录了公司每月的产品销售数据。
编写 SQL 查询，计算每个产品每月的同比增长率（YoY）和环比增长率（MoM）。

• 同比增长率计算公式：((当前月销售金额 - 去年同月销售金额) / 去年同月销售金额) * 100
• 环比增长率计算公式为：((当前月销售金额 - 上月销售金额) / 上月销售金额) * 100。

字段名 数据类型 描述 sale_date VARCHAR(7) 销售日期，格式为YYYY-MM product_id INT 产品 ID sales_amount DECIMAL(10, 2) 销售金额

结果保留两位小数，且按product_id和sale_date升序排列。

sale_date product_id sales_amount 2022-01 1 1000.00 2022-02 1 1200.00 2023-01 1 1100.00 2023-02 1 1300.00 2022-01 2 800.00 2022-02 2 900.00 2023-01 2 850.00 2023-02 2 950.00

无敌 又 优雅的窗口函数~🌺🌅🌺 又要到它们出场啦🕶

1. monthly_data (基础数据准备)

预期结果：✅✅✅

| sale_date | product_id | sales_amount | year | month ||-----------|------------|--------------|------|-------|| 2022-01 | 1 | 1000.00 | 2022 | 01 || 2022-02 | 1 | 1200.00 | 2022 | 02 || 2023-01 | 1 | 1100.00 | 2023 | 01 || 2023-02 | 1 | 1300.00 | 2023 | 02 || 2022-01 | 2 | 800.00 | 2022 | 01 || 2022-02 | 2 | 900.00 | 2022 | 02 || 2023-01 | 2 | 850.00 | 2023 | 01 || 2023-02 | 2 | 950.00 | 2023 | 02 |

2. with_growth_rates (应用窗口函数后)

预期结果：✅✅✅

| sale_date | product_id | sales_amount | prev_month_amount | prev_year_amount ||-----------|------------|--------------|-------------------|------------------|| 2022-01 | 1 | 1000.00 | NULL  | NULL || 2022-02 | 1 | 1200.00 | 1000.00  | NULL || 2023-01 | 1 | 1100.00 | NULL  | 1000.00 || 2023-02 | 1 | 1300.00 | 1100.00  | 1200.00 || 2022-01 | 2 | 800.00 | NULL  | NULL || 2022-02 | 2 | 900.00 | 800.00 | NULL || 2023-01 | 2 | 850.00 | NULL  | 800.00  || 2023-02 | 2 | 950.00 | 850.00 | 900.00  |

最终结果：

预期结果：✅✅✅

| sale_date | product_id | sales_amount | mom_growth_rate | yoy_growth_rate ||-----------|------------|--------------|-----------------|-----------------|| 2022-01 | 1 | 1000.00 | NULL | NULL || 2022-02 | 1 | 1200.00 | 20.00  | NULL || 2023-01 | 1 | 1100.00 | NULL | 10.00  || 2023-02 | 1 | 1300.00 | 18.18  | 8.33 || 2022-01 | 2 | 800.00 | NULL | NULL || 2022-02 | 2 | 900.00 | 12.50  | NULL || 2023-01 | 2 | 850.00 | NULL | 6.25 || 2023-02 | 2 | 950.00 | 11.76  | 5.56 |

---

完整代码：

with monthly_data as ( -- 基础数据准备：提取年月信息 select sale_date, product_id, sales_amount, substr(sale_date, 1, 4) as year, -- 提取年 substr(sale_date, 6, 2) as month -- 提取月 from sales_records),with_growth_rates as ( -- 使用窗口函数计算同比环比 select sale_date, product_id, sales_amount, -- 上月销售额：按产品分区，按日期排序，取前一条记录 lag(sales_amount, 1) over (partition by product_id order by sale_date) as prev_month_amount, -- 去年同月销售额：按产品和月份分区，按年份排序，取前一条记录 lag(sales_amount, 1) over (partition by product_id, month order by year) as prev_year_amount from monthly_data)-- 最终结果计算select sale_date, product_id, sales_amount, -- 计算环比增长率（上月为基准） round( case when prev_month_amount is not null and prev_month_amount != 0 -- 分母不能为0~ then (sales_amount - prev_month_amount) / prev_month_amount * 100 else null -- （本月-上月）/上月 end, 2 ) as mom_growth_rate, -- 计算同比增长率（去年同月为基准） round( case when prev_year_amount is not null and prev_year_amount != 0 -- 分母不能为0~ then (sales_amount - prev_year_amount) / prev_year_amount * 100 else null -- （本月-上年同月）/上年同月 end, 2 ) as yoy_growth_ratefrom with_growth_ratesorder by product_id, sale_date;

---

核心套路：窗口函数YYDS🍠🍠🍠🍠

1. 窗口函数使用：

   • LAG(sales_amount, 1) 获取前一条记录的值
   • PARTITION BY product_id 确保按产品分组计算
   • ORDER BY sale_date 确保按时间顺序处理数据

2. 同比计算技巧：

   • 通过 PARTITION BY product_id, month 确保比较同一个月的数据
   • 通过 ORDER BY year 确保获取前一年的数据

3. NULL处理：

   • 当没有上月或去年数据时，结果为NULL ，计算时检查分母不为零，除零错误

---

二、玩家数据同比环比增长率⛺⛺⛺

游戏用户行为表 game_user_actions，记录了游戏玩家在不同时间的充值信息。

要求编写 SQL 查询，计算每个月所有用户的总充值金额的同比和环比增长率。

字段名 数据类型 描述 action_date VARCHAR(7) 行为日期，格式为 YYYY-MM user_id INT 用户ID recharge_amount DECIMAL(10, 2) 充值金额

结果保留两位小数，按 action_date 升序排列。如果上一年同月或上一个月没有充值数据，对应的同比或环比增长率记为 NULL。

---

action_date user_id recharge_amount 2022-01 1 200.00 2022-01 2 300.00 2022-02 1 250.00 2022-02 2 350.00 2023-01 1 220.00 2023-01 2 330.00 2023-02 1 280.00 2023-02 2 380.00

窗口函数YYDS😎😎😎

1.提取年月、预聚合、开窗

2.计算同比 环比

完整代码：🎈🎈🎈

with monthly_recharge as ( -- 第一步：计算每月总充值金额 select action_date, sum(recharge_amount) as total_amount, substr(action_date, 1, 4) as year, substr(action_date, 6, 2) as month from game_user_actions group by action_date),with_growth_rates as ( -- 第二步：使用窗口函数计算上月和去年同月数据 select action_date, total_amount, -- 上月总充值金额 lag(total_amount, 1) over ( order by action_date ) as prev_month_amount, -- 去年同月总充值金额 lag(total_amount, 1) over ( partition by month order by year ) as prev_year_amount from monthly_recharge)-- 第三步：计算增长率select action_date, total_amount as monthly_recharge, -- 环比增长率计算 round( case when prev_month_amount is not null and prev_month_amount != 0 then (total_amount - prev_month_amount) / prev_month_amount * 100 else null end, 2 ) as mom_growth_rate, -- 同比增长率计算 round( case when prev_year_amount is not null and prev_year_amount != 0 then (total_amount - prev_year_amount) / prev_year_amount * 100 else null end, 2 ) as yoy_growth_ratefrom with_growth_ratesorder by action_date;

---

三、网站流量数据同比环比🌏🌏🌏

网站流量记录表 website_traffic，记录了网站每天的访问情况。

请编写 SQL 查询，计算每个页面每月的访问次数同比和环比增长率。

字段名 数据类型 描述 visit_date VARCHAR(10) 访问日期，格式为 YYYY-MM-DD page_id INT 页面 ID visit_count INT 访问次数 visit_date page_id visit_count 2022-01-10 1 100 2022-02-15 1 120 2023-01-20 1 110 2023-02-25 1 130 2022-01-05 2 80 2022-02-12 2 90 2023-01-18 2 85 2023-02-22 2 95

---

1.数据预处理

• 提取 年、月等字段，聚合每月总访问量

预期结果：✅✅✅

month page_id total_visits year month_num 2022-01 1 100 2022 01 2022-02 1 120 2022 02 2023-01 1 110 2023 01 2023-02 1 130 2023 02 2022-01 2 80 2022 01 2022-02 2 90 2022 02 2023-01 2 85 2023 01 2023-02 2 95 2023 02

2.应用窗口函数

• lag(total_visits, 1)：按month排序,获取前一条记录的值（上月数据）
• lag(total_visits, 1)：按year排序，获取前一条记录的值（去年同月数据）
  
  预期结果：✅✅✅

month page_id total_visits prev_month_visits prev_year_visits 2022-01 1 100 NULL NULL 2022-02 1 120 100 NULL 2023-01 1 110 NULL 100 2023-02 1 130 110 120 2022-01 2 80 NULL NULL 2022-02 2 90 80 NULL 2023-01 2 85 NULL 80 2023-02 2 95 85 90

3.计算增长率并输出结果

• 环比增长率 = (本月访问量 - 上月访问量) / 上月访问量 * 100
• 同比增长率 = (本月访问量 - 去年同月访问量) / 去年同月访问量 * 100

预期结果：✅✅✅

month page_id total_visits mom_growth_rate yoy_growth_rate 2022-01 1 100 NULL NULL 2022-02 1 120 20.00 NULL 2023-01 1 110 NULL 10.00 2023-02 1 130 18.18 8.33 2022-01 2 80 NULL NULL 2022-02 2 90 12.50 NULL 2023-01 2 85 NULL 6.25 2023-02 2 95 11.76 5.56

---

加一下难度哈

连续问题 + 环比问题 🎢🎢🎢

1.表包含三个字段：user_id（用户 ID）、record_date（日期，格式为 YYYYMMDD）和 amt（金额）。

• 统计出 amt 连续 3 个月环比增长超过 50% 的用户明细
• 即找出那些在连续 3 个月内，每个月的 amt 相较于上一个月的 amt 增长幅度都大于 50% 的用户。

user_id record_date amt 1 20170101 100 3 20170101 20 4 20170101 30 1 20170102 200 2 20170102 240 3 20170102 30 4 20170102 2 1 20170101 180 2 20170101 250 3 20170101 30 4 20170101 260

1.预处理数据

- 提取年月，分组聚合 （老套路了 bro😋）

2.应用窗口函数

•  -- 获取下个月金额（相对当前月份） lead(sum_amt, 1) over (partition by user_id order by month) as next_month_amt

•  -- 获取下下个月金额 lead(sum_amt, 2) over (partition by user_id order by month) as next_two_month_amt,

•  -- 获取下下下个月金额 lead(sum_amt, 3) over (partition by user_id order by month) as next_three_month_amt

3.计算增长率

4.筛选最终结果

完整代码：🎈🎈🎈

/* * 目标：找出连续三个月环比增长率均超过50%的用户 * 实现思路： * 1. 先按用户和月份聚合金额数据 * 2. 使用窗口函数获取后续月份数据 * 3. 计算连续三个月的环比增长率 * 4. 筛选符合增长率条件的用户 */-- 第一阶段：按月聚合用户金额数据with monthly_summary as ( select user_id, -- 用户ID substring(record_date, 1, 7) as month, -- 从日期中提取年月部分 sum(amt) as sum_amt -- 计算每个用户每月的总金额 from tableA group by user_id, substring(month, 1, 7) -- 按用户和月份分组),/* * 第二阶段：获取后续月份数据 * 使用LEAD窗口函数获取每个用户下1/2/3个月的数据 * 注意：这里不设置默认值，保留NULL以便后续处理 */monthly_lead_amount as ( select user_id, month, sum_amt, -- 获取下个月金额（相对当前月份） lead(sum_amt, 1) over (partition by user_id order by month) as next_month_amt, -- 获取下下个月金额 lead(sum_amt, 2) over (partition by user_id order by month) as next_two_month_amt, -- 获取下下下个月金额 lead(sum_amt, 3) over (partition by user_id order by month) as next_three_month_amt from monthly_summary),/* * 第三阶段：计算环比增长率 * 公式：(下月金额-本月金额)/本月金额 * 添加了除零保护和NULL值处理 */monthly_growth_rates as ( select user_id, month, -- 计算第一个月到第二个月的增长率 case when sum_amt > 0 then (next_month_amt - sum_amt) / sum_amt else null -- 本月金额为0时返回NULL end as first_rate, -- 计算第二个月到第三个月的增长率 case when next_month_amt > 0 then (next_two_month_amt - next_month_amt) / next_month_amt else null -- 下月金额为0时返回NULL end as second_rate, -- 计算第三个月到第四个月的增长率 case when next_two_month_amt > 0 then (next_three_month_amt - next_two_month_amt) / next_two_month_amt else null -- 下下月金额为0时返回NULL end as third_rate from monthly_lead_amount where -- 只保留有完整连续三个月数据的记录 next_month_amt is not null and next_two_month_amt is not null and next_three_month_amt is not null)/* * 最终查询：筛选符合条件的用户 * 1. 使用DISTINCT确保用户不重复 * 2. 筛选三个月增长率均>50%的记录 * 3. 注意：增长率可能为NULL，NULL比较结果为unknown，不会被包含 */select distinct user_id -- 去重后的用户IDfrom monthly_growth_rateswhere first_rate > 0.5 and -- 第一个月增长率>50% second_rate > 0.5 and -- 第二个月增长率>50% third_rate > 0.5; -- 第三个月增长率>50%

---

2.股票交易记录表 stock_trades

字段名 数据类型 描述 stock_id INT 股票 ID trade_date VARCHAR(10) 交易日期，格式为 YYYY-MM-DD closing_price DECIMAL(10, 2) 收盘价

• 请编写 SQL 语句，找出那些收盘价连续 4 个交易日环比增长超过 20% 的股票明细。

• 也就是说，要找出在连续 4 个交易日内，每个交易日的收盘价相较于上一个交易日的收盘价增长幅度都大于 20% 的股票。

stock_id trade_date closing_price 1 2024-01-01 100.00 1 2024-01-02 125.00 1 2024-01-03 155.00 1 2024-01-04 190.00 1 2024-01-05 230.00 2 2024-01-01 80.00 2 2024-01-02 85.00 2 2024-01-03 90.00 2 2024-01-04 92.00 2 2024-01-05 95.00

套路一样哈 大家自己看看代码 博主就不啰嗦啦😂

WITH daily_prices AS ( -- 基础数据准备：按股票ID和交易日期排序 SELECT stock_id, trade_date, closing_price FROM stock_trades ORDER BY stock_id, trade_date),price_leads AS ( -- 获取后续3天的收盘价 SELECT stock_id, trade_date, closing_price, -- 获取下1个交易日的收盘价 LEAD(closing_price, 1) OVER (PARTITION BY stock_id ORDER BY trade_date) AS next_day_price, -- 获取下2个交易日的收盘价 LEAD(closing_price, 2) OVER (PARTITION BY stock_id ORDER BY trade_date) AS next_2day_price, -- 获取下3个交易日的收盘价 LEAD(closing_price, 3) OVER (PARTITION BY stock_id ORDER BY trade_date) AS next_3day_price FROM daily_prices),growth_rates AS ( -- 计算连续3个交易日的增长率 SELECT stock_id, trade_date, closing_price, -- 第1日到第2日的增长率 CASE WHEN closing_price > 0 THEN (next_day_price - closing_price) / closing_price ELSE NULL END AS first_day_growth, -- 第2日到第3日的增长率 CASE WHEN next_day_price > 0 THEN (next_2day_price - next_day_price) / next_day_price ELSE NULL END AS second_day_growth, -- 第3日到第4日的增长率 CASE WHEN next_2day_price > 0 THEN (next_3day_price - next_2day_price) / next_2day_price ELSE NULL END AS third_day_growth FROM price_leads WHERE -- 确保有完整的4个交易日数据 next_day_price IS NOT NULL AND next_2day_price IS NOT NULL AND next_3day_price IS NOT NULL)-- 最终查询：筛选连续4日涨幅均超过20%的股票SELECT DISTINCT stock_idFROM growth_ratesWHERE first_day_growth > 0.2 AND second_day_growth > 0.2 AND third_day_growth > 0.2ORDER BY stock_id;

另一种解法：

WITH daily_prices AS ( -- 基础数据准备：按股票ID和交易日期排序 SELECT stock_id, trade_date, closing_price, -- 为每只股票的交易记录按日期排序 ROW_NUMBER() OVER (PARTITION BY stock_id ORDER BY trade_date) AS row_num FROM stock_trades),price_changes AS ( -- 计算每日环比增长率 SELECT a.stock_id, a.trade_date, a.closing_price, -- 计算每日环比增长率 CASE WHEN b.closing_price > 0 THEN (a.closing_price - b.closing_price) / b.closing_price ELSE NULL END AS daily_growth_rate, -- 为每只股票的交易记录按日期排序 ROW_NUMBER() OVER (PARTITION BY a.stock_id ORDER BY a.trade_date) AS row_num FROM daily_prices a LEFT JOIN daily_prices b ON a.stock_id = b.stock_id AND a.row_num = b.row_num + 1 -- 连接前一天的记录),consecutive_growth AS ( -- 标记连续4日满足条件的记录 SELECT stock_id, trade_date, closing_price, daily_growth_rate, -- 检查当前及后续3日的增长率是否都>20% CASE WHEN daily_growth_rate > 0.2 AND  LEAD(daily_growth_rate, 1) OVER (PARTITION BY stock_id ORDER BY trade_date) > 0.2 AND  LEAD(daily_growth_rate, 2) OVER (PARTITION BY stock_id ORDER BY trade_date) > 0.2 AND  LEAD(daily_growth_rate, 3) OVER (PARTITION BY stock_id ORDER BY trade_date) > 0.2 THEN 1 ELSE 0 END AS is_consecutive_4_days FROM price_changes)-- 最终结果：筛选出连续4日涨幅超过20%的股票记录SELECT stock_id, trade_date, closing_price, ROUND(daily_growth_rate * 100, 2) || \'%\' AS growth_rate_percentageFROM consecutive_growthWHERE is_consecutive_4_days = 1ORDER BY stock_id, trade_date;

---

最后来一道作业：

核心思路：