描述

現有試卷信息表examination_info（exam_id試卷ID, tag試卷類別, difficulty試卷難度, duration考試時長, release_time發布時間）：

試卷作答記錄表exam_record（uid用戶ID, exam_id試卷ID, start_time開始作答時間, submit_time交卷時間, score得分）：

請計算2021年上半年各類試卷的做完次數相比2020年上半年同期的增長率（百分比格式，保留1位小數），以及做完次數排名變化，按增長率和21年排名降序輸出。

由示例數據結果輸出如下：

解釋：2020年上半年有3個tag有作答完成的記錄，分別是C++、SQL、PYTHON，它們被做完的次數分別是3、3、2，做完次數排名為1、1（并列）、3；

2021年上半年有2個tag有作答完成的記錄，分別是算法、SQL，它們被做完的次數分別是3、2，做完次數排名為1、2；具體如下：

因此能輸出同比結果的tag只有SQL，從2020到2021年，做完次數3=>2，減少33.3%（保留1位小數）；排名1=>2，后退1名。

WITHt2 AS (SELECTexam_id,IF(start_year = '2020', exam_cnt, NULL) exam_cnt_20, -- 2020年的完成次數LEAD(exam_cnt, 1) OVER (PARTITION BYexam_idORDER BYstart_year) exam_cnt_21, -- 2021年的完成次數IF(start_year = '2020', rk, NULL) exam_cnt_rank_20, -- 2020年的排名LEAD(rk, 1) OVER (PARTITION BYexam_idORDER BYstart_year) exam_cnt_rank_21 -- 2021年的排名FROM(SELECTexam_id,YEAR(submit_time) start_year,COUNT(score) exam_cnt,RANK() OVER (PARTITION BYYEAR(submit_time)ORDER BYCOUNT(score) DESC) rk/*分別對2021和2020的做完情況進行排名*/FROMexam_recordWHEREMONTH(submit_time) BETWEEN 1 AND 6 -- 選取上半年數據AND submit_time BETWEEN '2020-01-00 00:00:00' AND '2022-01-01 00:00:00' -- 選取2020和2021年的數據GROUP BYYEAR(submit_time),exam_id/*對年份和類別進行聚類*/) t1)
SELECTa.tag,exam_cnt_20,exam_cnt_21,CONCAT(ROUND((exam_cnt_21 - exam_cnt_20) * 100 / exam_cnt_20,1),'%') AS growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,CAST(exam_cnt_rank_21 AS SIGNED) - CAST(exam_cnt_rank_20 AS SIGNED) AS rank_delta -- 需要轉換格式，否則會報錯
FROMt2LEFT JOIN examination_info AS a ON a.exam_id = t2.exam_id
WHEREexam_cnt_21 IS NOT NULL
ORDER BYgrowth_rate DESC,exam_cnt_rank_21 DESC;

🔍 代碼逐層解析

🧱 1. 內層查詢?t1?—— 按年份+試卷分組并排名

SELECTexam_id,YEAR(submit_time) AS start_year,COUNT(score) AS exam_cnt,RANK() OVER (PARTITION BY YEAR(submit_time)ORDER BY COUNT(score) DESC) AS rk
FROM exam_record
WHEREMONTH(submit_time) BETWEEN 1 AND 6AND submit_time >= '2020-01-01' AND submit_time < '2022-01-01'
GROUP BY YEAR(submit_time), exam_id

? 做了什么？

• 篩選?2020 和 2021 年上半年?的數據
• 按?年份 + 試卷 ID?分組
• 統計每類試卷每年的完成次數（COUNT(score)）
• 使用?RANK()?計算每年內的完成次數排名（降序）

?? 注意：

• BETWEEN 1 AND 6：精確篩選上半年
• submit_time < '2022-01-01'：避免包含 2022 年數據
• RANK()?處理并列情況（如 3,3 → 排名 1,1，下一名為 3）

---

🧱 2. 中層查詢?t2?—— 使用?LEAD()?對齊兩年數據

SELECTexam_id,IF(start_year = '2020', exam_cnt, NULL) AS exam_cnt_20,LEAD(exam_cnt, 1) OVER (PARTITION BY exam_id ORDER BY start_year) AS exam_cnt_21,IF(start_year = '2020', rk, NULL) AS exam_cnt_rank_20,LEAD(rk, 1) OVER (PARTITION BY exam_id ORDER BY start_year) AS exam_cnt_rank_21
FROM t1

? 核心技巧：LEAD()?窗口函數

函數	作用
LEAD(col, 1)	獲取當前行之后第 1 行的值
PARTITION BY exam_id	按試卷分組，確保只在同?exam_id?內查找
ORDER BY start_year	按年份升序排列（2020 → 2021）

💡 舉個例子：

原始 t1 數據：

exam_id	start_year	exam_cnt	rk
9001	2020	100	2
9001	2021	150	1

經過 LEAD() 后：

exam_id	exam_cnt_20	exam_cnt_21	rk_20	rk_21
9001	100	150	2	1

? 實現了“將兩年數據對齊到同一行”

📌?IF(start_year = '2020', ..., NULL)?的作用：

• 將 2020 年的數據保留在?exam_cnt_20?字段
• 2021 年該字段為?NULL
• 配合?LEAD()，確保?exam_cnt_21?是下一年的值

---

🧱 3. 主查詢 —— 計算增長率與排名變化

SELECTa.tag,exam_cnt_20,exam_cnt_21,CONCAT(ROUND((exam_cnt_21 - exam_cnt_20) * 100.0 / exam_cnt_20, 1), '%') AS growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,CAST(exam_cnt_rank_21 AS SIGNED) - CAST(exam_cnt_rank_20 AS SIGNED) AS rank_delta
FROM t2
LEFT JOIN examination_info AS a ON a.exam_id = t2.exam_id
WHERE exam_cnt_21 IS NOT NULL
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC;

? 關鍵計算：

指標	公式	說明
增長率	(2021 - 2020) / 2020 * 100%	* 100.0?保證浮點運算
格式化輸出	CONCAT(..., '%')	添加百分號
排名變化	rank_21 - rank_20	正數表示排名下降，負數表示上升
類型轉換	CAST(... AS SIGNED)	避免字符串減法報錯

? 過濾與排序：

• WHERE exam_cnt_21 IS NOT NULL：確保該試卷在?2020 和 2021 都存在
• ORDER BY growth_rate DESC：增長率從高到低
• exam_cnt_rank_21 DESC：2021 年排名靠后的優先（同增長率時）

---

📝 核心知識點總結

技術點	說明	應用場景
LEAD()/LAG()	獲取下一行/上一行的值	同比、環比分析
RANK()	處理并列排名	排行榜、績效排名
PARTITION BY	窗口函數分組	分組內排序、對比
CONCAT + ROUND	格式化數值輸出	百分比、金額顯示
CAST(... AS SIGNED)	類型轉換	字符串轉整數計算
WITH ... AS ()	CTE 公共表表達式	分步處理復雜邏輯

---

? 最佳實踐建議

1. 時間范圍寫法：

   • ??BETWEEN '2020-01-00'（非法日期）
   • ??submit_time >= '2020-01-01' AND submit_time < '2022-01-01'

2. 增長率計算注意除零：

   • 可加?WHERE exam_cnt_20 > 0

3. 排名函數選擇：

   • RANK()：允許并列，下一名跳過（1,1,3）
   • DENSE_RANK()：允許并列，下一名不跳過（1,1,2）
   • ROW_NUMBER()：強制唯一，無并列

4. LEAD() 的適用場景：

   • 跨行對比（如：今年 vs 去年）
   • 避免自連接，提升性能

---

🎯 一句話總結

“用 LEAD() 實現跨年數據對齊，RANK() 計算年度排名，CONCAT+ROUND 格式化增長率，完成試卷類別的同比分析”

這套模式適用于：

• 年度/季度/月度對比分析
• 排名變化監控
• 增長率計算與展示