Oracle SQL中替代PostgreSQL filter语法实现日期差分布统计

推荐方案（SUM + CASE WHEN写法，逻辑与PostgreSQL的FILTER完全对齐） #

Oracle不支持FILTER语法，最简便的替代方式是通过CASE WHEN在聚合函数内做条件判断，写法和你原有PostgreSQL逻辑一一对应，易读易维护：

select
  trunc(t.first_sent_time) as sent_day,
  sum(case when t.planning_day - trunc(t.first_sent_time) = -2 then 1 else 0 end) as "minus2",
  sum(case when t.planning_day - trunc(t.first_sent_time) = -1 then 1 else 0 end) as "minus1",
  sum(case when t.planning_day - trunc(t.first_sent_time) = 0 then 1 else 0 end) as "sameDay",
  sum(case when t.planning_day - trunc(t.first_sent_time) = 1 then 1 else 0 end) as "plus1",
  sum(case when t.planning_day - trunc(t.first_sent_time) = 2 then 1 else 0 end) as "plus2"
from
  my_table t
-- 可按需添加过滤条件，例如：where t.first_sent_time >= DATE '2021-11-01'
group by
  trunc(t.first_sent_time)
order by
  sent_day
;

原有Pivot版本问题说明 #

你编写的Pivot语句多了一层不必要的外层聚合：Pivot执行后已经自动按sent_day分组输出统计结果，不需要再套一层SUM + GROUP BY，修正后的Pivot写法如下（可选，仍推荐CASE WHEN写法更灵活）：

select 
  *
from (
  select
    trunc(b.first_sent_time) as sent_day,
    b.id,
    b.planning_day - trunc(b.first_sent_time) as day_diff
  from
    my_table b
  where
    b.first_sent_time >= DATE '2021-11-01'
)
pivot (
  count(id) for day_diff in (-2 as "minus2",-1 as "minus1",0 as "sameDay", 1 as "plus1",2 as "plus2")
)
order by
  sent_day
;

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