FULL JOIN看起来很适合这个工作。小建议：

WITH sig AS (
   SELECT date_signing::date AS the_date, count(*) AS signing
   FROM   services
   GROUP  BY 1
   )
, can AS (
   SELECT date_cancellation::date AS the_date, count(*) AS cancelling
   FROM   services
   GROUP  BY 1
   )
SELECT *
FROM   sig
FULL   JOIN can USING (the_date)
WHERE  the_date IS NOT NULL;  -- optional to eliminate row(s) with null date

字符串
为了简化，使用相同的列别名并与USING子句联接以获得单个输出列。
此外，count(*)更快，并且在这方面是等效的（除了还获得the_date IS NULL的正确计数）。
相关：

• 我从查询中获得了不同的结果，例如：“e.id”或"e.id“

如果您不想在此报表中出现空白，特别是如果您想放大某个期间，请生成一个日历并对其进行left join计算。基于此计算也会生成一个适当的零，而不是null.Demo at db<>fiddle：

select d::date,count(*)filter(where d::date=s.date_signing::date)signing,
               count(*)filter(where d::date=s.date_cancellation::date)cancelling 
from generate_series('2023-02-22'::timestamp,'2023-03-11','1 day')_(d)
left join services s on d::date in(s.date_signing::date,s.date_cancellation::date)
group by 1 order by 1;

字符串
如果你喜欢你现在的布局，你根本不需要join。读出日期和sum()：

select the_date, sum(s)signing, sum(c)cancelling
from (select  date_cancellation::date AS the_date,0 AS s,1 AS c from services
      union all select date_signing::date,1,0 from services)_
group by 1 order by 1;

型
或具有类似的预聚合/子聚合：

select the_date, sum(s)signing, sum(c)cancelling from (
  select date_cancellation::date the_date,0 s,count(*)c from services group by 1
  union all select date_signing::date,count(*),0 from services group by 1)_
group by 1 order by 1;

型
Performance&plans的
unnest()转换为聚合体：

select the_date,sum(s)signing,sum(c)cancelling 
from services, 
unnest(array[ (date_cancellation::date, 0    ,1    )
             ,(date_signing::date     , 1    ,0    ) ] )
             _(the_date date          , s int,c int)
group by 1 order by 1;

select the_date,sum(s::int)signing,sum((not s)::int)cancelling 
from services,
unnest(array[true,false],
       array[date_signing,date_cancellation]::date[])_(s,the_date)
group by 1 order by 1;

select the_date,count(*)filter(where o='signing'   )signing
               ,count(*)filter(where o='cancelling')cancelling
from services,
unnest(array['signing'   ,'cancelling'],
       array[date_signing,date_cancellation]::date[])_(o,the_date)
group by 1 order by 1;

select the_date::date
      ,sum( extract(h from the_date))signing
      ,sum((extract(h from the_date) =0)::int)cancelling 
from services,
unnest(array[date_signing::date+interval'1h',date_cancellation::date])_(the_date)
group by 1 order by 1;