Postgres 嵌套 WHEN 聚合函式

January 6, 2021

在 PostgreSQL（9.4 版）中，我正在嘗試建構一個查詢，以確定在各個部分中不同數字在範圍之間的數據集中出現的次數。當我按函式查詢分組時，"SettlementPointPrice"按count()我的預期正確地將價格分成單獨的部分。但是，這會創建數百行。我正在尋找將每個儲存桶中 count() 的總和聚合到一行中的能力（子查詢？）。在 SQL 中管理此問題的最佳方法是什麼？
我為每個儲存桶/批次使用這樣的語句（下面的完整 SQL）：
CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 0 AND 10
    THEN count(*) ELSE 0 END AS "DA $0 - $10",
當我按“SettlementPointPrice”（本範例中的兩個價格）分組以確認數據正確計算儲存桶時。如下表所示。
個人兩天的原始數據：
Row | "SettlementPointPrice" | 0-10 | 11-20 | 21-30
1   | 18                     | 0    | 1     | 0
2   | 22                     | 0    | 0     | 1
但是，我無法通過將它們全部組合在一起來獲得匯總的總和。我假設這是一個子查詢？
我希望結果是這樣的：
Row | 0-10 | 11-20 | 21-30
1   | 0    | 1     | 1
完整的 SQL 程式碼：
SELECT
 "DA-A"."SettlementPointPrice",
 CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 0 AND 10
      THEN count(*) ELSE 0 END AS "DA $0 - $10",
 CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 11 AND 20
      THEN COUNT(*) ELSE 0 END AS "DA $11 - $20",
 CASE WHEN (round(sum("DA-A"."SettlementPointPrice"),2)) BETWEEN 21 AND 30
      THEN COUNT(*) ELSE 0 END AS "DA $21 - $30"
FROM 
 public.da "DA-A", 
 public.rt_aggregate "RT-A"
WHERE 
 "RT-A"."DeliveryDate" = "DA-A"."DeliveryDate" AND
 "RT-A"."SettlementPointName" = "DA-A"."SettlementPointName" AND
 "DA-A"."SettlementPointName" = 'John' AND 
 "DA-A"."DeliveryDate" &gt;= '2015-02-01' AND
 "DA-A"."DeliveryDate" &lt;= '2015-02-20' AND
("RT-A"."DeliveryHour" = 14) and 
 date_part('hour', "DA-A"."DeliveryHour") = "RT-A"."DeliveryHour"
GROUP BY
 "DA-A"."SettlementPointPrice",
 "DA-A"."SettlementPointName"

經過一些處理，這歸結為：

雖然您的謂詞無論如何d."SettlementPointName" = 'John'都在過濾“SettlementPointName”的單個值，但請簡化為：

SELECT count(                                     d."SettlementPointPrice" &lt; 10.5 OR NULL) AS da_00_10
    , count(d."SettlementPointPrice" &gt;= 10.5 AND d."SettlementPointPrice" &lt; 20.5 OR NULL) AS da_11_20
    , count(d."SettlementPointPrice" &gt;= 20.5 AND d."SettlementPointPrice" &lt; 30.5 OR NULL) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."SettlementPointName" = 'John'
AND    d."DeliveryDate" &gt;= '2015-02-01'
AND    d."DeliveryDate" &lt;= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour";

關於計數技術：

對於絕對性能，SUM 更快還是 COUNT 更快？

或者更好的是，使用 pg 9.4中的新聚合過濾器技術：

SELECT d."SettlementPointName"
    , count(*) FILTER (WHERE d."SettlementPointPrice" &lt;  10.5) AS da_00_10
    , count(*) FILTER (WHERE d."SettlementPointPrice" &gt;= 10.5
                       AND   d."SettlementPointPrice" &lt;  20.5) AS da_11_20
    , count(*) FILTER (WHERE d."SettlementPointPrice" &gt;= 20.5
                       AND   d."SettlementPointPrice" &lt;  30.5) AS da_21_30
FROM   public.da d
JOIN   public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName")
WHERE  d."DeliveryDate" &gt;= '2015-02-01'
AND    d."DeliveryDate" &lt;= '2015-02-20'
AND    r."DeliveryHour" = 14
AND    date_part('hour', d."DeliveryHour") = r."DeliveryHour"
GROUP  BY 1;

這一次，像您在評論中詢問的那樣，選擇所有名稱並為每個名稱返回一行。

詳細資訊FILTER：