postgresql - Count Distinct with Answer 并排而不是在下面

Question

这是我的查询:

SELECT substring(date,1,10), count(distinct id),
CASE WHEN name IS NOT NULL THEN 1 ELSE 0 END
FROM table
WHERE (date >= '2015-09-01')
GROUP BY substring(date,1,10), CASE WHEN name IS NOT NULL THEN 1 ELSE 0 END
ORDER BY substring(date,1,10)

这是我的结果:

substring     count     case
2015-09-01     20472      0
2015-09-01         7      1
2015-09-02     20465      0
2015-09-02       470      1

我希望它看起来像这样:

substring     count    count
2015-09-01    20472        7
2015-09-02    20465      470

谢谢!

Answer 1

使用 PostgreSQL 9.4 或更新版本，我们可以使用新的 FILTER 子句直接过滤聚合:

SELECT substring(date,1,10),
  count(distinct id),
  count(*) FILTER (WHERE name IS NOT NULL)
FROM table
WHERE (date >= '2015-09-01')
GROUP BY 1
ORDER BY 1