有条件的SQL计数

Question

如何在 PostgreSQL 数据库中按照某一列进行分组，并创建一个计算数据出现次数的列？
我正在使用 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 可能的取值数量增加，编写此查询变得更加麻烦。是否有一种方便的方法来编写此查询，可能还能优化性能？

- user3542327

---

你的版本相当合理，尽管如果你想让它在语法上正确，应该包括 end。另一种选择是Postgres的 crosstab 功能。我不知道哪个在性能方面更好。 - Gordon Linoff

sorry, forgot to type the end - user3542327

为什么不按类别和问题1分组，并计算每个组中的行数？ - Giorgi Nakeuri

@GiorgiNakeuri 如果OP想要每个question1值的列名，那么您需要转置结果以获得所需的结果。 - Ram

@Ram，啊哈，好的，我现在明白了。在MSSQL中，它将按类别和问题进行分组，然后对分组结果进行透视。 - Giorgi Nakeuri

@GiorgiNakeuri 确实。透视表是我首先想到的，但这是postgresql。 - Ram

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 子句的详细信息：

• 带有额外（不同）过滤器的聚合列

如果您想要简短：

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;

更多语法变体：

• 对于绝对性能，SUM比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 交叉表查询

- Erwin Brandstetter

Answer 2

对我来说，“最好”的方法是编写这样的查询：

SELECT
    category,
    question1,
    count(*)
FROM reviews
GROUP BY category, question1

然后我使用这些数据在应用逻辑中绘制表格。

另一种选项是为所有分组结果使用一个JSON列。这将导致类似于：

category1 | {"zero": 1, "one": 3, "two": 5}
category2 | {"one": 7, "two": 4}

等等。

您可以使用json_build_object和json_agg从前一个选项中构建查询。这个选项最好的一点是，您不需要提前知道可能的question1值的数量。

- Ihor Romanchenko