Find sum of item_sub_id per item_id for next 30-CodePudding

I need to find out sum_of_item_id_for_next_30_days , which calculates sum of item_sub_id for next 30 days based on date field. thanks in Advance

input

item_sub_id  item_id     date
123          20221213   7/13/2021 0:00
456          20221213   7/16/2021 0:00
789          20221213   7/21/2021 0:00
989          20221213   7/23/2021 0:00
131          20221213   7/27/2021 0:00
132          20221213   7/27/2021 0:00
133          20221213   8/3/2021 0:00
134          20221213   8/3/2021 0:00
134          20221213   8/3/2021 0:00
135          20221213   8/4/2021 0:00
135          20221213   8/4/2021 0:00
136          20221213   8/10/2021 0:00
137          20221213   8/10/2021 0:00
138          20221213   8/17/2021 0:00
139          20221213   8/17/2021 0:00
140          20221213   8/18/2021 0:00

output

count( distinct item_sub_id)  item_id     date               sum_of_item_sub_id_for_next_30_days
1                         20221213  7/13/2021 0:00                       11
1                         20221213  7/16/2021 0:00                       10     
1                         20221213  7/21/2021 0:00                       12  
1                         20221213  7/23/2021 0:00                       11
2                         20221213  7/27/2021 0:00                       10
2                         20221213  8/3/2021 0:00                         8
1                         20221213  8/4/2021 0:00                         6
2                         20221213  8/10/2021 0:00                        5
2                         20221213  8/17/2021 0:00                        3
1                         20221213  8/18/2021 0:00                        1

CodePudding user response：

One option is to use a correlated subquery:

Sample data:

SQL> with test (item_sub_id, item_id, datum) as
  2    (select 123, 2, date '2021-07-13' from dual union all
  3     select 456, 2, date '2021-07-16' from dual union all
  4     select 789, 2, date '2021-07-21' from dual union all
  5     select 989, 2, date '2021-07-23' from dual union all
  6     select 131, 2, date '2021-07-27' from dual union all
  7     select 132, 2, date '2021-07-27' from dual union all
  8     select 133, 2, date '2021-08-03' from dual union all
  9     select 134, 2, date '2021-08-03' from dual union all
 10     select 134, 2, date '2021-08-03' from dual union all
 11     select 135, 2, date '2021-08-04' from dual union all
 12     select 135, 2, date '2021-08-04' from dual union all
 13     select 136, 2, date '2021-08-10' from dual union all
 14     select 137, 2, date '2021-08-10' from dual union all
 15     select 138, 2, date '2021-08-17' from dual union all
 16     select 139, 2, date '2021-08-17' from dual union all
 17     select 140, 2, date '2021-08-18' from dual
 18    )

Query begins here:

 19  select
 20    count(distinct a.item_sub_id) cnt,
 21    a.item_id,
 22    a.datum,
 23    (select count(distinct b.item_sub_id)
 24     from test b
 25     where b.item_id = a.item_id
 26       and b.datum >= a.datum
 27    ) sum_of
 28  from test a
 29  group by a.item_id, a.datum
 30  order by a.item_id, a.datum;

       CNT    ITEM_ID DATUM          SUM_OF
---------- ---------- ---------- ----------
         1          2 13.07.2021         14
         1          2 16.07.2021         13
         1          2 21.07.2021         12
         1          2 23.07.2021         11
         2          2 27.07.2021         10
         2          2 03.08.2021          8
         1          2 04.08.2021          6
         2          2 10.08.2021          5
         2          2 17.08.2021          3
         1          2 18.08.2021          1

10 rows selected.

SQL>

CodePudding user response：

You are looking for SUM OVER. Group your rows by date and count distinct item_sub_ids. Then get the count sum in a 30 day window.

select
  count(distinct item_sub_id) as cnt,
  item_id,
  datum,
  sum(count(distinct item_sub_id)) 
    over (partition by item_id
          order by datum 
          rows between current row and 29 following) as cnt30
from test
group by item_id, datum
order by item_id, datum;

(I don't know how item_id comes into play exactly. Adjust this query according to your needs.)