SQL计数如果列 [英] SQL count if columns

问题描述

创建列计数表中数据出现次数的最佳方法是什么？

我看过

  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 WHEN question1 = 2 THEN 1 ELSE 0 END）AS TWO，
 category 
 FROM reviews 
 GROUP BY类别
  

其中question1可以有0,1或2的值。

我也看过一个使用 count（CASE WHEN question1 = 0 THEN 1）

但是，写作可能值的次数变得更加麻烦问题1增加。是否有一种方便的方式来编写此查询，可能会优化性能？

PS。我的数据库是PostgreSQL。

解决方案

在Postgres 9.4中有一个新的，更干净的聚合 FILTER 选项：

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

• 如何简化这个游戏统计查询？

em> short ：

，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？

正确的交叉表查询

最佳性能，并且对于更长的选项列表更短：

  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查询

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.

I have seen

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

where question1 can have a value of either 0, 1 or 2.

I have also seen a version of that using count(CASE WHEN question1 = 0 THEN 1)

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?

PS. My database is PostgreSQL

解决方案

In Postgres 9.4 there is new, cleaner aggregate FILTER option:

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;

Details for the new FILTER clause:

• How can I simplify this game statistics query?

If you want it 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;

Overview over possible variants:

• For absolute performance, is SUM faster or COUNT?

Proper crosstab query

crosstab() yields the best performance and is shorter for longer lists of options:

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

Detailed explanation:

• PostgreSQL Crosstab Query

这篇关于SQL计数如果列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！