【SQL】晨光咖啡館，過濾聚合的微妙碰撞

lanedm發表於2024-07-15
SQL

這天，小悅懶洋洋地步入辦公樓下的咖啡館，意外地與一位男子不期而遇。他顯然因前一晚的辛勤工作而略顯疲憊，卻仍選擇早到此地，尋找一絲寧靜與放鬆。他叫逸塵，身姿挺拔，衣著簡約而不失格調，晨光下更顯英俊不凡，吸引了周遭的目光。兩人彷彿心有靈犀，不約而同地走向各自的位置。

小悅手中輕握著新出爐的拿鐵，眼睛緊緊盯著手機上的工作郵件，心思全然沉浸在工作的海洋中，對前方即將發生的“小插曲”渾然未覺。而逸塵，正欲伸手取桌上的檔案，兩人的手在不經意間悄然相遇，伴隨著一陣輕微的碰撞，小悅手中的拿鐵微微傾斜，幾滴熱燙的咖啡瞬間在逸塵潔白的襯衫上綻放，如同從樹上不經意間灑落的晨露，雖美卻略顯突兀。

“哎呀，真的非常抱歉！”小悅連忙道歉，臉頰上泛起了紅暈，手忙腳亂地在包中搜尋紙巾，希望能為這突如其來的尷尬場面做些什麼。逸塵則以他特有的紳士風度，輕輕接過紙巾，自行處理起那片不速之客。

“沒關係，下次小心些便是。”逸塵的話語中雖帶有一絲不易察覺的責備，但更多的是溫柔與寬容。他皺眉的瞬間，非但沒有減少魅力，反而增添了幾分成熟與穩重。

小悅心中五味雜陳，既有對自己疏忽的懊惱，也有對逸塵那不經意間流露出的嚴厲與溫柔交織的複雜情感。她低聲細語：“我真的不是故意的。”這句話雖輕如蚊蚋，卻清晰地傳入了逸塵的耳中，兩人的心間彷彿被一股莫名的力量輕輕觸碰，營造出一種難以言喻的微妙氛圍。

當時，小悅手機上的郵件內容深深吸引了她的注意，郵件中詳細列出了一項sql任務：要求根據公司名稱和月份進行分組，統計出2024年全年的訂單表總數量，並進一步細分出已下單數量（狀態1）、送貨中數量（狀態2）以及已收貨數量（狀態3）。這一挑戰性的任務讓小悅不禁陷入了沉思，她迅速地在腦海中構想出了初步的方案1：

SELECT
    o.company,
    EXTRACT(MONTH FROM o.order_date) AS month,
    (SELECT COUNT(*) FROM orders o2 WHERE o2.company = o.company AND EXTRACT(MONTH FROM o2.order_date) = EXTRACT(MONTH FROM o.order_date) AND EXTRACT(YEAR FROM o2.order_date) = 2024) AS total_orders,
    (SELECT COUNT(*) FROM orders o2 WHERE o2.company = o.company AND EXTRACT(MONTH FROM o2.order_date) = EXTRACT(MONTH FROM o.order_date) AND o2.order_status = 1 AND EXTRACT(YEAR FROM o2.order_date) = 2024) AS ordered_count,
    (SELECT COUNT(*) FROM orders o2 WHERE o2.company = o.company AND EXTRACT(MONTH FROM o2.order_date) = EXTRACT(MONTH FROM o.order_date) AND o2.order_status = 2 AND EXTRACT(YEAR FROM o2.order_date) = 2024) AS delivering_count,
    (SELECT COUNT(*) FROM orders o2 WHERE o2.company = o.company AND EXTRACT(MONTH FROM o2.order_date) = EXTRACT(MONTH FROM o.order_date) AND o2.order_status = 3 AND EXTRACT(YEAR FROM o2.order_date) = 2024) AS received_count
FROM
    orders o
WHERE
    EXTRACT(YEAR FROM o.order_date) = 2024
GROUP BY
    o.company,
    EXTRACT(MONTH FROM o.order_date)
ORDER BY
    o.company,
    month;

方案1查詢語句使用了多個子查詢來計算每個公司和月份的訂單數量，雖然可以實現所需的功能，但也存在一些缺點：

  1. 效能問題：

    • 每個子查詢都需要對orders表進行獨立的掃描，這會導致多次重複的資料庫查詢，增加了資料庫的負擔。
    • 對於大型資料集，這種多次掃描和查詢的方式會導致效能顯著下降。

  2. 可讀性和維護性：

    • 使用多個子查詢使得SQL語句變得複雜，難以閱讀和理解。
    • 如果需要修改或除錯，需要逐個檢查每個子查詢，增加了維護的難度。

  3. 重複程式碼：

    • 相同的條件（如公司、月份、年份）在每個子查詢中重複出現，導致程式碼冗餘。
    • 如果需要修改這些條件，必須在每個子查詢中逐一修改，容易遺漏或出錯。

  4. 索引利用：

    • 子查詢可能無法有效利用索引，尤其是在沒有合適的索引情況下，查詢效能會進一步下降。

隨後，小悅沒有放棄，反而更加專注地投入到方案一的最佳化中。她仔細分析了初步方案的可行性，並考慮到了效能最佳化和資料處理效率的問題。於是，她提出了最佳化後的方案2(Oracle/MySql/Mssql)：

SELECT
    company,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 1 THEN 1 END) AS ordered_count,
    COUNT(CASE WHEN status = 2 THEN 1 END) AS delivering_count,
    COUNT(CASE WHEN status = 3 THEN 1 END) AS received_count
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2024
GROUP BY
    company,
    EXTRACT(MONTH FROM order_date)
ORDER BY
    company,
    month;

方案2查詢語句使用了COUNT(CASE WHEN ...)語法，具有以下優點：

  1. 效能最佳化：

    • 透過在一個查詢中完成所有計算，避免了多次掃描和查詢資料庫，從而提高了查詢效能。
    • 資料庫引擎可以更好地最佳化查詢計劃，利用索引和快取來加速查詢。

  2. 簡潔性和可讀性：

    • 使用COUNT(CASE WHEN ...)語法使得SQL語句更加簡潔，減少了冗餘程式碼。
    • 查詢邏輯清晰，易於閱讀和理解，便於維護和除錯。

  3. 減少重複程式碼：

    • 相同的條件（如公司、月份、年份）只需要在WHERE子句中寫一次，避免了在多個子查詢中重複書寫相同的條件。
    • 如果需要修改查詢條件，只需在一個地方進行修改，減少了出錯的可能性。

  4. 靈活性：

    • COUNT(CASE WHEN ...)語法非常靈活，可以輕鬆地新增或修改條件，以適應不同的查詢需求。
    • 可以很容易地擴充套件到其他狀態或條件，而不需要重構整個查詢。

  5. 索引利用：

    • 這種查詢方式可以更好地利用索引，尤其是在有合適的索引情況下，查詢效能會得到進一步提升。

小悅意識到雖然方案2的CASE語法可以實現需求，但使用COUNT FILTER語法在PostgreSQL中更為簡潔高效，而且由於國產資料庫大多相容PostgreSQL，這種選擇不僅提升了查詢效能，還確保了程式碼在國產資料庫環境中的廣泛適用性。方案3(PostgreSQL語法):，

SELECT
    company,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 1) AS ordered_count,
    COUNT(*) FILTER (WHERE status = 2) AS delivering_count,
    COUNT(*) FILTER (WHERE status = 3) AS received_count
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2024
GROUP BY
    company,
    EXTRACT(MONTH FROM order_date)
ORDER BY
    company,
    month;

方案3中的COUNT(*) FILTER (WHERE status = 1)` 這種語法是 SQL:2003 標準引入的一個新特性,稱為"過濾聚合"(Filtered Aggregation)。

過濾聚合的出現是為了解決一些常見的 SQL 分析需求,例如:

1. 在統計訂單總數的同時,也統計已完成訂單的數量。
2. 在統計銷售總額的同時,也統計已付款訂單的銷售額。
3. 在統計某個商品的總銷量中,也統計該商品的正常銷量和退貨銷量。

在傳統的 SQL 中,解決這類需求通常需要使用多個子查詢或者分組之後進行過濾,程式碼會比較複雜。

過濾聚合的出現,讓這類需求的實現變得更加簡單和優雅。開發者可以在聚合函式中直接加上 `FILTER (WHERE ...)` 子句,對聚合的資料進行過濾,從而得到所需的統計結果。

比如上面的例子中,`COUNT(*) FILTER (WHERE status = 1)` 就可以直接統計狀態為 1 的訂單數量,無需再額外新增子查詢。

這種語法在 SQL:2003 標準中引入，PostgreSQL首先實現了這個語法。它極大地簡化了 SQL 的編寫,提高了程式碼的可讀性和可維護性。

Oracle /MySql/MsSql,對於這個 SQL 標準的新特性,並沒有直接支援，只能透過case when的形式實現。

示例，在Having中使用過濾聚合語法：

--case語法示例
SELECT
    company,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 1 THEN 1 END) AS ordered_count,
    COUNT(CASE WHEN status = 2 THEN 1 END) AS delivering_count,
    COUNT(CASE WHEN status = 3 THEN 1 END) AS received_count
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2024
GROUP BY
    company,
    EXTRACT(MONTH FROM order_date)
Having
    COUNT(CASE WHEN status = 1 THEN 1 END)>0
ORDER BY
    company,
    month;

--filter語法示例
SELECT
    company,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 1) AS ordered_count,
    COUNT(*) FILTER (WHERE status = 2) AS delivering_count,
    COUNT(*) FILTER (WHERE status = 3) AS received_count
FROM
    orders
WHERE
    EXTRACT(YEAR FROM order_date) = 2024
GROUP BY
    company,
    EXTRACT(MONTH FROM order_date)
Having
    COUNT(*) FILTER (WHERE status = 1)>0
ORDER BY
    company,
    month;

相關文章