如何在 PostgreSQL 中使用 CASE 語句、FILTER 或 Crosstab() 高效率執行條件計數？

PostgreSQL條件計數：CASE語句與FILTER的效率對比

高效統計表中資料出現的頻率至關重要。 PostgreSQL常用CASE語句進行條件計數，但當可能值數量增加時，此方法變得繁瑣。

使用SUM(CASE WHEN)進行計數：

<code class="language-sql">SELECT
    sum(CASE WHEN question1 = 0 THEN 1 ELSE 0 END) AS ZERO,
    sum(CASE WHEN question1 = 1 THEN 1 ELSE 0 END) AS ONE,
    sum(CASE WHEN question1 = 2 THEN 1 ELSE 0 END) AS TWO,
    category
FROM reviews
GROUP BY category</code>

使用COUNT(CASE WHEN)計數：

<code class="language-sql">SELECT
    count(CASE WHEN question1 = 0 THEN 1 END) AS ZERO,
    count(CASE WHEN question1 = 1 THEN 1 END) AS ONE,
    count(CASE WHEN question1 = 2 THEN 1 END) AS TWO,
    category
FROM reviews
GROUP BY category</code>

CASE語句的限制：

雖然這些方法可以實現條件計數，但存在缺點：

• 程式碼冗長：寫多個CASE語句容易出錯且效率低。
• 效能問題：使用CASE表達式和ELSE 0可能會影響效能。

PostgreSQL 9.4 版本中使用FILTER最佳化計數：

對於PostgreSQL 9.4及更高版本，FILTER聚合選項提供了一個高效的解決方案：

<code class="language-sql">SELECT category
     , count(*) FILTER (WHERE question1 = 0) AS zero
     , count(*) FILTER (WHERE question1 = 1) AS one
     , count(*) FILTER (WHERE question1 = 2) AS two
FROM   reviews
GROUP  BY 1;</code>

此方法使用FILTER子句應用不同的過濾器，避免了額外CASE語句的開銷。

使用OR NULL簡化語法：

為了更簡潔，可以使用OR NULL：

<code class="language-sql">SELECT category
     , count(question1 = 0 OR NULL) AS zero
     , count(question1 = 1 OR NULL) AS one
     , count(question1 = 2 OR NULL) AS two
FROM   reviews
GROUP  BY 1;</code>

針對複雜計數的Crosstab查詢：

當處理大量的選項時，crosstab()函數提供了最佳的效能和簡潔性：

<code class="language-sql">SELECT * FROM crosstab(
     'SELECT category, question1, count(*) AS ct
      FROM   reviews
      GROUP  BY 1, 2
      ORDER  BY 1, 2'
   , 'VALUES (0), (1), (2)'
   ) AS ct (category text, zero int, one int, two int);</code>

總之，PostgreSQL的FILTER選項和OR NULL語法為條件計數提供了高效便捷的方法，而crosstab()函數則在複雜計數場景中表現出色。

以上是如何在 PostgreSQL 中使用 CASE 語句、FILTER 或 Crosstab() 高效率執行條件計數？的詳細內容。更多資訊請關注PHP中文網其他相關文章！