SQL 窗口函數怎麼寫提示詞（分組排名、取每組前 N、累計求和、同比環比、移動平均）
遇到「每個類目銷量前三」「按月累計銷售額」「環比增長率」「連續登入天數」這類需求、用 GROUP BY 寫不出來時用：說明表結構和需求，AI 給出窗口函數寫法、講清分區與排序、窗口範圍的含義，並提示不同數據庫的差異和結果核對方法。
通用大模型 對話模型通用
你是一名精通 SQL 窗口函數的數據工程師。請根據我的需求寫查詢，並講清楚窗口函數各部分的作用。 - 數據庫：[數據庫]（例：MySQL 8、PostgreSQL、Hive、ClickHouse） - 表結構（建表語句或字段說明，註明主鍵和時間字段的類型）： [粘貼表結構] - 需求：[需求]（例：每個城市每月銷售額排名前 3 的門店，並計算環比） - 幾行樣例數據（可選）： [樣例數據] 請輸出： 1. 先複述需求的口徑：排名依據什麼、並列時怎麼處理、時間按自然月還是滾動 30 天、缺失月份（某月沒有數據）怎麼算。有歧義的先列出來，並說明你採用的預設處理。 2. SQL： - 用 CTE 分步寫，每一步加中文註釋； - 窗口函數部分說明：PARTITION BY 按什麼分組、ORDER BY 按什麼排序、窗口範圍（ROWS 還是 RANGE，從哪到哪）。 3. 排名函數的選擇：ROW_NUMBER、RANK、DENSE_RANK 在並列時的區別，以及本需求該用哪個。 4. 涉及「上一期」的計算（LAG、LEAD）時，處理好第一期沒有上一期、分母為零、中間缺月的情況。 5. 注意事項： - 窗口函數的結果不能直接寫在 WHERE 裏過濾，要放到外層查詢； - 我的數據庫版本是否支持窗口函數，不支持時的替代寫法； - 數據量大時的性能提示（分區字段與排序字段的索引、先過濾再開窗）。 6. 核對方法：給出一兩條簡單查詢，用來手工抽查結果是否正確。 如果用到了某個數據庫特有的函數或語法，明確標註，並給出通用寫法。常用函數速查也附上：SUM、AVG、COUNT 加 OVER 子句做累計與移動平均，FIRST_VALUE、NTILE 分桶，PERCENT_RANK 求百分位。
高亮處換成你自己的內容：[數據庫]、[粘貼表結構]、[需求]、[樣例數據]
ChatGPT Plus 充值
已被複製 0 次
使用說明
怎麼填變量：[表結構] 一定要寫清時間字段的類型（日期、時間戳還是字符串）和數據粒度（一行是一筆訂單還是一天的彙總），這決定了是否需要先聚合再開窗。[需求] 盡量寫成「對每個……，計算……」的形式。
常見坑：
- 想直接寫
WHERE ROW_NUMBER() OVER (...) <= 3，這在 SQL 中是不允許的，要先在子查詢或 CTE 裏算出排名，再在外層過濾。 - 用 LAG 算環比時，如果某個月沒有數據，上一行其實是兩個月前的數據，環比就算錯了。需要先用日曆表補全月份。
- 累計求和時，ORDER BY 後不寫窗口範圍，部分數據庫預設的範圍在排序值相同時會把並列行一起累加，結果和預期不同。需要時明確寫
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
追問技巧：追問「用連續登入天數（同一用戶日期減去行號相等即為同一段連續）的思路，幫我算每個用戶最長連續登入天數」，這是窗口函數的經典面試題和分析場景。
示例輸出
示例，僅供參考（MySQL 8：每個城市每月銷售額前 3 的門店及環比）
sql
WITH monthly AS ( -- 第 1 步：按門店、月份彙總
SELECT city, store_id,
DATE_FORMAT(order_time, '%Y-%m') AS ym,
SUM(amount) AS sales
FROM orders
GROUP BY city, store_id, DATE_FORMAT(order_time, '%Y-%m')
),
ranked AS ( -- 第 2 步：城市內按月排名，並取上月銷售額
SELECT *,
DENSE_RANK() OVER (PARTITION BY city, ym ORDER BY sales DESC) AS rk,
LAG(sales) OVER (PARTITION BY store_id ORDER BY ym) AS prev_sales
FROM monthly
)
SELECT city, ym, store_id, sales, rk,
ROUND((sales - prev_sales) / NULLIF(prev_sales, 0) * 100, 1) AS mom_pct
FROM ranked
WHERE rk <= 3 -- 第 3 步：在外層過濾排名
ORDER BY city, ym, rk;說明：DENSE_RANK 允許並列且名次連續，並列第 2 的兩家門店都會保留。NULLIF 防止上月銷售額為 0 時除零。注意：如果某門店中間有月份沒有訂單，LAG 取到的將不是上一個自然月，需先補全月份。
同款作品
用這條提示詞做出來的作品；原作者會因此獲得積分
還沒有同款，來做第一個。
0 條評論
還沒有評論，來搶沙發～