sql - 条件 SQL 计数

Question

创建用于计算表中数据出现次数的列的最佳方法是什么？表格需要按一列分组？
我的数据库是 PostgreSQL。

我见过:

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

question1 的值可以是 0、1 或 2。

我还看到了一个使用 count(CASE WHEN question1 = 0 THEN 1)

的版本

但是，随着 question1 的可能值数量的增加，编写起来会变得更加麻烦。有没有方便的方法来编写此查询，可能优化性能？

Answer 1

在 Postgres 9.4 或更高版本中，使用聚合 FILTER 选项。通常最干净和最快:

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;

FILTER 子句的详细信息:

• Aggregate columns with additional (distinct) filters

如果你想要它简短:

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 absolute performance, is SUM faster or COUNT?

正确的交叉表查询

crosstab() 产生最佳性能并且对于长选项列表更短:

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

详细解释:

• PostgreSQL Crosstab Query