PostgreSQL 合并 COUNT 和 GROUP BY 结果

Question

select type, created_at::date , count(type)
from txns
group by created_at::date, type
order by created_at::date;

上面的 SQL 查询给出了以下输出：

type   | created_at | count 
---------+------------+-------
full    | 2017-05-20 |     2
virtual | 2017-05-20 |     2
full    | 2017-05-21 |     1
virtual | 2017-05-21 |     1
full    | 2017-05-22 |     3

如何按created_at对上述结果进行分组以获得以下输出：

created_at | full_count | virtual_count
------------+-----------------
2017-05-20 |     2     |  2
2017-05-21 |     1     |  1
2017-05-22 |     3     |  0

我想在一行中按created_at获取full和virtual类型计数。

Answer 1

这应该有效：

with so as (
select type, created_at::date , count(type) from txns group by created_at::date, type order by created_at::date
)
select
created_at
, sum(case when type = 'full' then count else 0 end) full_count
, sum(case when type = 'virtual' then count else 0 end) virtual_count
from so
group by created_at;
created_at | full_count | virtual_count
------------+------------+---------------
2017-05-20 |          2 |             2
2017-05-21 |          1 |             1
2017-05-22 |          3 |             0
(3 rows)

Answer 2

我会在没有 CTE/子查询的情况下执行此操作，如下所示：

select created_at::date,
sum( (type = 'Full')::int ) as cnt_full,
sum( (type = 'Virtual')::int ) as cnt_virtual
from txns
group by created_at::date
order by created_at::date;

Postgres 通常会实现 CTE，因此这甚至可能具有轻微的性能优势。

注意：您也可以在较新版本的 Postgres 中使用filter：

select created_at::date,
count(*) filter (where type = 'Full') as cnt_full,
count(*) filter (where type = 'Virtual') as cnt_Virtual
from txns
group by created_at::date
order by created_at::date;

Answer 3

如果你有两个以上的类型，并且在进一步的处理中你可以处理hstore：

WITH q AS ( SELECT type, created_at::date , count(type) FROM txns GROUP BY 1,2)
SELECT created_at, hstore(array_agg(type), array_agg(count::text))
FROM q GROUP BY 1;