實用的 SQL 謎題，讓你的技能提升

有一些 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 聯繫我。

所有圖片均由作者提供，除非另有說明。