SQL计数列

发布时间：2020-12-12 05:56:28 所属栏目：MsSql教程来源：网络整理

导读：创建用于计算表中数据出现次数的列的最佳方法是什么？该表需要按一列分组. 我见过 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)

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

我见过

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.

我也看过一个使用计数的版本(CASE WHEN question1 = 0 THEN 1)

然而,随着question1的可能值的数量增加,这变得更加麻烦.是否有一种方便的方法来编写此查询,可能会优化性能？

PS.我的数据库是PostgreSQL

解决方法

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

> How can I simplify this game statistics query?

如果你想要它简短：

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(*)::int 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!