条件SQL计数 [英] Conditional SQL count
问题描述
创建计算表中数据出现次数的列的最佳方法是什么?该表需要按一列分组吗?
我的数据库是PostgreSQL.
What is the best way to create columns which count the number of occurrences of data in a table? The table needs to be grouped by one column?
My database is 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(在问题1 = 0然后是1时的情况)
但是,随着 question1
可能值的增加,编写此代码变得更加麻烦.是否有一种方便的方式来编写此查询,可能会优化性能?
However, this becomes more cumbersome to write as the number of possible values for question1
increases. Is there a convenient way to write this query, possibly optimizing performance?
推荐答案
在Postgres 9.4或更高版本中使用汇总 FILTER
选项:
Use the aggregate FILTER
option in Postgres 9.4 or later:
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;
可能的变体概述:
crosstab()
可获得最佳性能,并且对于较长的选项列表而言较短:
crosstab()
yields the best performance and is shorter for longer lists of options:
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);
详细说明:
这篇关于条件SQL计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!