在 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
clause:
如果你想要的话short:
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);
详细解释: