有一些 SQL 模式，一旦你知道了，就會發現它們無處不在。今天我將展示的這些謎題的解決方案，其實都是非常簡單的 SQL 查詢，但理解它們背後的概念，肯定會幫助你在日常寫查詢時找到新的解決方案。
這些挑戰都是基於真實世界的情境，因為在過去的幾個月裡，我特意記下了每一個我需要構建的謎題查詢。我也鼓勵你自己嘗試這些挑戰，這樣你可以先挑戰自己，這會提高你的學習效果！
所有生成數據集的查詢將以 PostgreSQL 和 DuckDB 友好的語法提供，這樣你可以輕鬆地複製並進行實驗。最後，我還會提供一個鏈接，指向一個 GitHub 倉庫，裡面包含所有代碼，以及我將留給你的額外挑戰的答案！
我將這些謎題按難度逐漸增加的順序進行了整理，所以如果你覺得前面的題目太簡單，至少可以看看最後一個，因為它使用了一種我相信你之前沒有見過的技術。
好了，讓我們開始吧。
我喜歡這個謎題，因為最終的查詢既短又簡單，雖然它涉及了許多邊緣情況。這個挑戰的數據顯示了票據在看板階段之間的移動，目標是找出票據在「進行中」階段的平均停留時間。
數據包含票據的 ID、票據創建的日期、移動的日期，以及移動的「從」階段和「到」階段。存在的階段有「新建」、「進行中」、「審核」和「完成」。
你需要知道的一些事情（邊緣情況）：
- 票據可以向後移動，這意味著票據可以回到「進行中」階段。
- 你不應該包括仍然卡在「進行中」階段的票據，因為無法知道它們會停留多久。
- 票據不一定總是在「新建」階段創建。
CREATE TABLE ticket_moves (
ticket_id INT NOT NULL,
create_date DATE NOT NULL,
move_date DATE NOT NULL,
from_stage TEXT NOT NULL,
to_stage TEXT NOT NULL
);
INSERT INTO ticket_moves (ticket_id, create_date, move_date, from_stage, to_stage)
VALUES
-- 票據 1：在「新建」階段創建，然後移動到進行中、審核、完成。
(1, '2024-09-01', '2024-09-03', 'New', 'Doing'),
(1, '2024-09-01', '2024-09-07', 'Doing', 'Review'),
(1, '2024-09-01', '2024-09-10', 'Review', 'Done'),
-- 票據 2：在「新建」階段創建，然後移動：新建 → 進行中 → 審核 → 再次進行中 → 審核。
(2, '2024-09-05', '2024-09-08', 'New', 'Doing'),
(2, '2024-09-05', '2024-09-12', 'Doing', 'Review'),
(2, '2024-09-05', '2024-09-15', 'Review', 'Doing'),
(2, '2024-09-05', '2024-09-20', 'Doing', 'Review'),
-- 票據 3：在「新建」階段創建，然後移動到進行中。（邊緣情況：沒有後續的移動）
(3, '2024-09-10', '2024-09-16', 'New', 'Doing'),
-- 票據 4：已經在「進行中」階段創建，然後移動到審核。
(4, '2024-09-15', '2024-09-22', 'Doing', 'Review');
數據的摘要：
- 票據 1：在「新建」階段創建，正常移動到「進行中」，然後是「審核」，最後到「完成」。
- 票據 2：在「新建」階段創建，然後移動：新建 → 進行中 → 審核 → 再次進行中 → 審核。
- 票據 3：在「新建」階段創建，移動到「進行中」，但仍然卡在那裡。
- 票據 4：在「進行中」階段創建，然後移動到「審核」。
停下來想一想，你會如何處理這個問題。你能找出票據在單一階段停留多久嗎？
老實說，這一開始聽起來很嚇人，看起來處理所有邊緣情況會是一場噩夢。讓我給你展示完整的解決方案，然後我會解釋發生了什麼。
WITH stage_intervals AS (
SELECT
ticket_id,
from_stage,
move_date
- COALESCE(
LAG(move_date) OVER (
PARTITION BY ticket_id
ORDER BY move_date
),
create_date
) AS days_in_stage
FROM
ticket_moves
)
SELECT
SUM(days_in_stage) / COUNT(DISTINCT ticket_id) as avg_days_in_doing
FROM
stage_intervals
WHERE
from_stage = 'Doing';
第一個 CTE 使用 LAG 函數來找到票據的上一次移動，這將是票據進入該階段的時間。計算持續時間就像從移動日期中減去上一次日期一樣簡單。
你應該注意到的是在上一次移動日期中使用了 COALESCE。這樣做的目的是，如果票據沒有上一次移動，那麼就使用票據的創建日期。這樣就能處理票據直接在「進行中」階段創建的情況，因為它仍然會正確計算離開該階段所需的時間。
這是第一個 CTE 的結果，顯示每個階段的停留時間。注意票據 2 有兩個條目，因為它在兩次不同的時間訪問了「進行中」階段。
完成這一步後，只需將「進行中」階段的總天數相加，然後除以曾經離開該階段的票據的數量。這樣做，而不是簡單地使用 AVG，可以確保票據 2 的兩行正確計算為一個票據。
還不錯吧？
第二個挑戰的目標是找出每位員工最近的合同序列。當兩個合同之間的間隔超過一天時，就會發生序列中斷。
在這個數據集中，沒有合同重疊，這意味著同一員工的合同要麼有間隔，要麼在新合同開始的前一天結束。
CREATE TABLE contracts (
contract_id integer PRIMARY KEY,
employee_id integer NOT NULL,
start_date date NOT NULL,
end_date date NOT NULL
);
INSERT INTO contracts (contract_id, employee_id, start_date, end_date)
VALUES
-- 員工 1：兩個連續的合同
(1, 1, '2024-01-01', '2024-03-31'),
(2, 1, '2024-04-01', '2024-06-30'),
-- 員工 2：一個合同，然後間隔三天，然後兩個合同
(3, 2, '2024-01-01', '2024-02-15'),
(4, 2, '2024-02-19', '2024-04-30'),
(5, 2, '2024-05-01', '2024-07-31'),
-- 員工 3：一個合同
(6, 3, '2024-03-01', '2024-08-31');
數據的摘要：
- 員工 1：有兩個連續的合同。
- 員工 2：一個合同，然後間隔三天，然後兩個合同。
- 員工 3：一個合同。
根據數據集，預期的結果是所有合同都應該包括在內，除了員工 2 的第一個合同，因為這是唯一一個有間隔的合同。
在解釋解決方案的邏輯之前，我希望你思考一下，什麼操作可以用來連接屬於同一序列的合同。只專注於第二行數據，你需要知道什麼信息來判斷這個合同是否中斷？
我希望這對你來說很清楚，這正是使用窗口函數的完美情況。它們對於解決這類問題非常有用，理解何時使用它們有助於找到乾淨的解決方案。
那麼，第一步是使用 LAG 函數獲取同一員工的上個合同的結束日期。這樣做後，簡單地比較兩個日期，檢查是否存在序列中斷。
WITH ordered_contracts AS (
SELECT
*,
LAG(end_date) OVER (PARTITION BY employee_id ORDER BY start_date) AS previous_end_date
FROM
contracts
),
gapped_contracts AS (
SELECT
*,
-- 處理第一個合同的情況，因為它不會有
-- 上一個結束日期。在這種情況下，它仍然是新
-- 序列的開始。
CASE WHEN previous_end_date IS NULL
OR previous_end_date < start_date - INTERVAL '1 day' THEN
1
ELSE
0
END AS is_new_sequence
FROM
ordered_contracts
)
SELECT * FROM gapped_contracts ORDER BY employee_id ASC;
一個直觀的方式來繼續查詢是為每位員工編號序列。例如，沒有間隔的員工將始終在他的第一個序列中，但有 5 次合同中斷的員工將在他的第 5 個序列中。搞笑的是，這是由另一個窗口函數完成的。
-- 之前的 CTE
sequences AS (
SELECT
*,
SUM(is_new_sequence) OVER (PARTITION BY employee_id ORDER BY start_date) AS sequence_id
FROM
gapped_contracts
)
SELECT * FROM sequences ORDER BY employee_id ASC;
注意，對於員工 2，他在第一個間隔值之後開始了他的序列 #2。為了完成這個查詢，我將數據按員工分組，獲取他們最近序列的值，然後與序列進行內部連接，只保留最近的那一個。
-- 之前的 CTE
max_sequence AS (
SELECT
employee_id,
MAX(sequence_id) AS max_sequence_id
FROM
sequences
GROUP BY
employee_id
),
latest_contract_sequence AS (
SELECT
c.contract_id,
c.employee_id,
c.start_date,
c.end_date
FROM
sequences c
JOIN max_sequence m ON c.sequence_id = m.max_sequence_id
AND c.employee_id = m.employee_id
ORDER BY
c.employee_id,
c.start_date
)
SELECT
*
FROM
latest_contract_sequence;
如預期，我們的最終結果基本上是我們的起始查詢，只是缺少了員工 2 的第一個合同！
最後，最後一個謎題——我很高興你能堅持到這裡。
對我來說，這是最令人驚訝的，因為當我第一次遇到這個問題時，我想到了完全不同的解決方案，這在 SQL 中實現起來會很麻煩。
對於這個謎題，我改變了上下文，與我在工作中處理的情況不同，因為我認為這樣會更容易解釋。
想像一下，你是一個活動場地的數據分析師，你正在分析即將舉行的活動的演講安排。你想找出一天中同時進行最多演講的時間。
這是你需要知道的關於日程安排的信息：
- 會議室的預訂以 30 分鐘為增量，例如從 9 點到 10 點 30 分。
- 數據是乾淨的，沒有會議室的超額預訂。
- 同一會議室可以有連續的會議。
CREATE TABLE meetings (
room TEXT NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL
);
INSERT INTO meetings (room, start_time, end_time) VALUES
-- A 房間的會議
('Room A', '2024-10-01 09:00', '2024-10-01 10:00'),
('Room A', '2024-10-01 10:00', '2024-10-01 11:00'),
('Room A', '2024-10-01 11:00', '2024-10-01 12:00'),
-- B 房間的會議
('Room B', '2024-10-01 09:30', '2024-10-01 11:30'),
-- C 房間的會議
('Room C', '2024-10-01 09:00', '2024-10-01 10:00'),
('Room C', '2024-10-01 11:30', '2024-10-01 12:00');
會議日程可視化（這是實際數據）。
解決這個問題的方法是使用所謂的掃描線算法，或稱為基於事件的解決方案。這個名字實際上有助於理解將要做的事情，因為這個想法是，與其處理區間，不如處理事件。
為此，我們需要將每一行轉換為兩個單獨的事件。第一個事件將是會議的開始，第二個事件將是會議的結束。
WITH events AS (
-- 為每個會議的開始創建一個事件（+1）
SELECT
start_time AS event_time,
1 AS delta
FROM meetings
UNION ALL
-- 為每個會議的結束創建一個事件（-1）
SELECT
-- 小技巧以處理連續會議（稍後解釋）
end_time - interval '1 minute' as end_time,
-1 AS delta
FROM meetings
)
SELECT * FROM events;
花點時間理解這裡發生了什麼。要從單一數據行創建兩個事件，我們只是將數據集與自身聯合；第一部分使用開始時間作為時間戳，第二部分使用結束時間。
你可能已經注意到創建的 delta 列，並看到這將如何進行。當事件開始時，我們將其計算為 +1，當它結束時，我們將其計算為 -1。你甚至可能已經想到另一個窗口函數來解決這個問題，實際上你是對的！
但在此之前，讓我解釋一下我在結束日期中使用的技巧。因為我不想讓連續會議計算為同時進行的兩個會議，所以我從每個結束日期中減去了一分鐘。這樣，如果一個會議結束，而另一個會議在 10 點 30 分開始，就不會假設在 10 點 30 分同時有兩個會議進行。
好了，回到查詢，還有另一個窗口函數。這一次，選擇的函數是滾動 SUM。
-- 之前的 CTE
ordered_events AS (
SELECT
event_time,
delta,
SUM(delta) OVER (ORDER BY event_time, delta DESC) AS concurrent_meetings
FROM events
)
SELECT * FROM ordered_events ORDER BY event_time DESC;
在 Delta 列上的滾動 SUM 基本上是逐條記錄向下走，找出當時有多少事件在進行。例如，在早上 9 點整，它看到兩個事件開始，所以它將同時進行的會議數量標記為兩個！
當第三個會議開始時，計數增加到三。但當它到達 9 點 59 分（10 點）時，兩個會議結束，計數回到一。使用這些數據，唯一缺少的就是找出同時進行會議數量最高的時候。
-- 之前的 CTE
max_events AS (
-- 找到最大同時進行會議的值
SELECT
event_time,
concurrent_meetings,
RANK() OVER (ORDER BY concurrent_meetings DESC) AS rnk
FROM ordered_events
)
SELECT event_time, concurrent_meetings
FROM max_events
WHERE rnk = 1;
就是這樣！9 點 30 分到 10 點的區間是同時進行會議數量最多的，這與上面的日程可視化相符！
我認為這個解決方案看起來非常簡單，並且適用於許多情況。每當你處理區間時，應該考慮一下，如果從事件的角度來看，查詢是否會更簡單。
但在你繼續之前，為了真正掌握這個概念，我想留給你一個額外的挑戰，這也是掃描線算法的一個常見應用。我希望你能試試！
額外挑戰
這個挑戰的上下文與上個謎題相同，但現在，目標不是找出同時進行會議最多的時間段，而是找出不良排程。似乎會議室中存在重疊，需要列出以便盡快修正。
你如何找出同一會議室在同一時間預訂了兩個或更多會議？這裡有一些解決方法的提示：
- 仍然是相同的算法。
- 這意味著你仍然會進行 UNION，但它會稍微不同。
- 你應該從每個會議室的角度來思考。
你可以使用這些數據來進行挑戰：
CREATE TABLE meetings_overlap (
room TEXT NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL
);
INSERT INTO meetings_overlap (room, start_time, end_time) VALUES
-- A 房間的會議
('Room A', '2024-10-01 09:00', '2024-10-01 10:00'),
('Room A', '2024-10-01 10:00', '2024-10-01 11:00'),
('Room A', '2024-10-01 11:00', '2024-10-01 12:00'),
-- B 房間的會議
('Room B', '2024-10-01 09:30', '2024-10-01 11:30'),
-- C 房間的會議
('Room C', '2024-10-01 09:00', '2024-10-01 10:00'),
-- 與之前的會議重疊。
('Room C', '2024-10-01 09:30', '2024-10-01 12:00');
如果你對這個謎題的解決方案以及其他查詢感興趣，可以查看這個 GitHub 倉庫。
這篇文章的第一個要點是窗口函數非常強大。自從我對使用它們更熟悉以來，我覺得我的查詢變得簡單多了，易於閱讀，我希望你也能有同樣的感受。
如果你想了解更多關於它們的內容，你可能會喜歡閱讀我寫的另一篇文章，裡面介紹了如何有效理解和使用它們。
第二個要點是，這些挑戰中使用的模式確實在許多其他地方發生。你可能需要找到訂閱的序列、客戶保留，或者你可能需要找到任務的重疊。在許多情況下，你需要以非常相似的方式使用窗口函數，正如在謎題中所做的那樣。
第三點我希望你記住的是，使用事件而不是處理區間的這個解決方案。我回顧了一些我很久以前解決的問題，我本可以使用這個模式來簡化我的工作，但不幸的是，我當時並不知道。
我真的希望你喜歡這篇文章，並嘗試了這些謎題。如果你能堅持到這裡，我相信你要麼學到了新的 SQL 知識，要麼加強了你對窗口函數的理解！
非常感謝你的閱讀。如果你有問題或想與我聯繫，請隨時通過 mtrentz.com 聯繫我。
所有圖片均由作者提供，除非另有說明。
本文由 AI 台灣 運用 AI 技術編撰，內容僅供參考，請自行核實相關資訊。
歡迎加入我們的 AI TAIWAN 台灣人工智慧中心 FB 社團，
隨時掌握最新 AI 動態與實用資訊！
