How to Optimally Count Occurrences in Grouped Data Using PostgreSQL?

Efficiently count the number of occurrences in PostgreSQL grouped data

PostgreSQL provides a variety of methods to count the occurrences of data in grouped tables. For example, you can use the CASE WHEN statement to count specific values:

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

However, when the number of possible values ​​increases, using the CASE WHEN statement becomes increasingly cumbersome. In PostgreSQL 9.4 and later, the aggregate FILTER option provides a cleaner and potentially faster solution:

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;

For brevity, a simplified syntax can be used:

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;

For large option lists, the crosstab() function provides a more performant alternative:

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

In summary, PostgreSQL provides multiple methods for conditional SQL counting. The FILTER option provides a concise and efficient solution, while the crosstab() function provides the best performance for handling a large number of options.

The above is the detailed content of How to Optimally Count Occurrences in Grouped Data Using PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!