postgresql - 如何在 postgresql 中使用不同的 WHERE 语句按组进行两个单独的计数？

Question

我有两个单独的查询，它们仅在“WHERE”语句中有所不同:

SELECT concat(extract(MONTH from created_at),'-', extract(year from created_at)) AS "Month", 
count(email) AS "Total AB Signups"
from users
where is_accountant='t'
group by 1,extract(month from created_at),extract(year from created_at)
order by extract(year from created_at),extract(month from created_at);

然后:

SELECT concat(extract(MONTH from created_at),'-', extract(year from created_at)) AS "Month", 
count(email) AS "Total AB Signups"
from users
where is_accountant='f'
group by 1,extract(month from created_at),extract(year from created_at)
order by extract(year from created_at),extract(month from created_at);

我如何将它合并到一个看起来像这样的表中:

Month | Total AB Signups | Total SMB Signups
---------------------------------------------

Answer 1

您可以将 where 条件移动到 count 函数内的 case 表达式:

SELECT concat(extract(MONTH from created_at),'-', extract(year from created_at)) AS "Month", 
count(case when is_accountant='t' then email end) AS "Total AB Signups",
count(case when is_accountant='f' then email end) AS "Total SMB Signups"
from users
group by 1,extract(month from created_at),extract(year from created_at)
order by extract(year from created_at),extract(month from created_at);

或者，如果您使用的是 Postgresql 9.4+，则可以使用新的 filter 语法:

SELECT concat(extract(MONTH from created_at),'-', extract(year from created_at)) AS "Month", 
count(email) FILTER (where is_accountant='t') AS "Total AB Signups",
count(email) FILTER (where is_accountant='f') AS "Total SMB Signups"
from users
group by 1,extract(month from created_at),extract(year from created_at)
order by extract(year from created_at),extract(month from created_at);