postgresql - 从不同的表添加 COUNT(*)，包括 WHERE 语句和 GROUP BY

Question

我有三个这样的查询:

选择 count(*) 作为客户，created_at::date 作为表 1 的日期，其中 subscription_ends_at IS NOT NULL 按 created_at::date 分组，按 created_at::date ASC 排序

选择 count(*) 作为客户，created_at::date 作为表 2 中的日期，其中 subscription_ends_at IS NOT NULL group by created_at::date order by created_at::date ASC

选择 count(*) 作为客户，created_at::date 作为表 3 中的日期，其中 subscription_ends_at IS NOT NULL 按 created_at::date 分组，按 created_at::date ASC 排序

我需要以两列结尾:一个日期和三个表中的客户 总和。我尝试使用子查询，但不确定如何在不出错的情况下定位 GROUP BY。我试过这样的事情:

select ((select count(*) as customers, created_at::date as date from table1 where subscription_ends_at IS NOT NULL group by created_at::date order by created_at::date ASC)+(select count(* ) 作为客户，created_at::date 作为来自 table2 的日期，其中 subscription_ends_at IS NOT NULL group by created_at::date order by created_at::date ASC)+(select count(*) 作为客户，created_at::date 作为来自 table3 的日期，其中subscription_ends_at IS NOT NULL group by created_at::date order by created_at::date ASC)) AS SumCount

Answer 1

我想您可以使用union all 来组合表中的数据，然后执行聚合

select t.date, count(*)
from (
    select  created_at::date as date from table1 where subscription_ends_at IS NOT NULL 
    union all
    select  created_at::date as date from table2 where subscription_ends_at IS NOT NULL
    union all
    select created_at::date as date from table3 where subscription_ends_at IS NOT NULL
) t
group by t.date
order by t.date