How to Efficiently Perform Conditional Counts in PostgreSQL Using CASE Statements, FILTER, or Crosstab()?

PostgreSQL condition counting: Efficiency comparison between CASE statement and FILTER

The frequency of data occurrence in efficient statistical tables is crucial. PostgreSQL commonly uses the CASE statement for conditional counting, but this method becomes cumbersome when the number of possible values ​​increases.

Use SUM(CASE WHEN) for counting:

<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>

Use COUNT(CASE WHEN) for counting:

<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>

Limitations of the CASE statement:

Although these methods can implement conditional counting, they have disadvantages:

• Long code: Writing multiple CASE statements is error-prone and inefficient.
• Performance issue: Using CASE expressions and ELSE 0 may affect performance.

Use FILTER to optimize counting in PostgreSQL 9.4 version:

For PostgreSQL 9.4 and above, the FILTER aggregation option provides an efficient solution:

<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>

This method uses the FILTER clause to apply different filters, avoiding the overhead of additional CASE statements.

Use OR NULL to simplify the syntax:

For more conciseness, you can use 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 query for complex counts:

The crosstab() function provides the best performance and simplicity when dealing with large numbers of options:

<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>

In short, PostgreSQL's FILTER option and OR NULL syntax provide efficient and convenient methods for conditional counting, and the crosstab() function performs well in complex counting scenarios.

The above is the detailed content of How to Efficiently Perform Conditional Counts in PostgreSQL Using CASE Statements, FILTER, or Crosstab()?. For more information, please follow other related articles on the PHP Chinese website!