合并日期列：按日期统计合同签订与终止数量的SQL优化

问题背景 #

现有services表包含date_signing（合同签订日期）和date_cancellation（合同终止日期）字段，需要按日期分组统计每日的合同签订数量与终止数量。原查询使用两个CTE分别统计后做全连接，结果日期分为两列，需合并为单列日期，对应展示签订、终止数量（无数据时显示null）。

解决思路与实现 #

方案1：基于原查询的日期合并

在原有全连接结果上，用COALESCE函数合并两列日期，得到统一的date字段，同时保留原统计的signing和cancelling值：

with conn as (
    select date_signing::date as dt_sign, count(date_signing) as signing 
    from services 
    group by date_signing::date
),
disconn as (
    select date_cancellation::date as dt_canc, count(date_cancellation) as cancelling 
    from services 
    group by date_cancellation::date
)
select 
    coalesce(conn.dt_sign, disconn.dt_canc) as date,
    conn.signing,
    disconn.cancelling
from conn 
full join disconn on conn.dt_sign = disconn.dt_canc
order by date;

方案2：统一聚合更高效

通过UNION ALL将签订、终止日期数据整合为同一维度，再用条件聚合一次性统计，避免两次分组与全连接，性能更优：

select 
    dt as date,
    count(case when type = 'sign' then 1 end) as signing,
    count(case when type = 'cancel' then 1 end) as cancelling
from (
    select date_signing::date as dt, 'sign' as type from services where date_signing is not null
    union all
    select date_cancellation::date as dt, 'cancel' as type from services where date_cancellation is not null
) t
group by dt
order by date;

注：添加非空判断是为了过滤无效的空日期行，避免干扰统计结果。

内容的提问来源于stack exchange，提问作者Artur Olenberg