PostgreSQL分组统计如何单独扣减internal_review状态的计数

原始查询逻辑 #

原有查询实现了当年上月、job_status非expired的申请按status分组统计数量，语句如下：

select application.status as status, count(*) as "current_month" from application
where to_char(application.created, 'mon') = to_char('now'::timestamp - '1 month'::interval, 'mon') 
    and date_part('year',application.created) = date_part('year', CURRENT_DATE) 
    and application.job_status != 'expired'
group by application.status

需求说明 #

仅调整internal_review状态的统计值：从该状态的原始计数里，扣除相同时间范围、同表中type字段等于abc的总行数，其余状态的统计值保持不变。

最终实现代码 #

写法1：CTE简化重复逻辑（可读性更高） #

先把符合时间范围的数据集提取出来，避免重复写时间判断条件：

with last_month_valid_data as (
  select status, type
  from application
  where to_char(created, 'mon') = to_char('now'::timestamp - '1 month'::interval, 'mon') 
    and date_part('year',created) = date_part('year', CURRENT_DATE) 
    and job_status != 'expired'
)
select 
  status,
  case 
    when status = 'internal_review'
    -- 只有internal_review状态扣除对应type=abc的总行数
    then count(*) - (select count(*) from last_month_valid_data where type = 'abc')
    else count(*)
  end as current_month
from last_month_valid_data
group by status

写法2：单查询直接修改（无需CTE） #

如果不习惯用CTE，也可以直接在原有查询基础上调整聚合逻辑：

select 
  application.status as status,
  case 
    when application.status = 'internal_review' 
    then count(*) - (select count(*) from application 
                      where to_char(created, 'mon') = to_char('now'::timestamp - '1 month'::interval, 'mon') 
                        and date_part('year',created) = date_part('year', CURRENT_DATE) 
                        and job_status != 'expired'
                        and type = 'abc')
    else count(*) 
  end as "current_month"
from application
where to_char(application.created, 'mon') = to_char('now'::timestamp - '1 month'::interval, 'mon') 
    and date_part('year',application.created) = date_part('year', CURRENT_DATE) 
    and application.job_status != 'expired'
group by application.status

效果说明 #

两种写法执行后都能得到预期结果：fail、pass状态的计数和原始查询完全一致，internal_review的计数会自动扣除所有符合条件的type='abc'的行数，无符合条件的type='abc'数据时则返回原始计数。

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