substituting "filter" in a sql query on oracle-CodePudding

We have a table with data that has one date column indicating what day the data is for ("planning_day") and another column for logging when the data was sent ("first_sent_time").

I'm trying to make a report showing how far in the past/future we've sent data on which day. So if today we sent 2 data for yesterday, 5 for today and 1 for the day after tomorrow, the result should be something like this:

sent_day   minus2 minus1 sameDay plus1 plus2
2021-11-24    0      2      5      0     1
...

I know I could do this in postgres with a query using "filter":

select
  trunc(t.first_sent_time),
  count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = -2) as "minus2",
  count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = -1) as "minus1",
  count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = 0)  as "sameDay",
  count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = 1)  as "plus1",
  count(t.id) filter (where e.planning_day - trunc(e.first_sent_time) = 2)  as "plus2"
from
  my_table t
group by
  trunc(t.first_sent_time)
;

Unfortunately, this "filter" doesn't exist in Oracle. I need help here. I tried something like following:

select 
  sent_day,
  sum(minus2),
  sum(minus1),
  sum(sameDay),
  sum(plus1),
  sum(plus2)
from (
  select 
    *
  from (
    select
      b.id,
      trunc(b.first_sent_time) as sent_day,
      b.planning_day,
      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")
  )
)
group by
  sent_day
order by
  sent_day
;

but it doesn't work and it feels like I'm going too complicated and there must be an easier solution.

CodePudding user response：

Use a CASEexpression within the aggregation function to simulate the filter.

Here a simplified example

with dt as (
select 1 id , 1 diff_days from dual union all
select 2 id , 1 diff_days from dual union all
select 3 id , -1 diff_days from dual union all
select 4 id , -1 diff_days from dual union all
select 4 id , -1 diff_days from dual)
/* query */
select 
  count(case when diff_days = 1 then id end) as cnt_1,
  count(case when diff_days = -1 then id end) as cnt_minus_1
from dt;

results in

     CNT_1 CNT_MINUS_1
---------- -----------
         2           3