查找item_sub_id每个item_id下一个30的和

Question

我需要找出sum_of_item_id_for_next_30_days，它计算的总和Item_sub_id，根据日期字段查询未来30天。提前感谢

输入

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

输出

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 

Answer 1

您正在寻找SUM OVER。按日期分组行，并对不同的item_sub_ids进行计数。然后获得30天窗口内的计数总和。

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 
range between current row and 29 following) as cnt30
from test
group by item_id, datum
order by item_id, datum;

我不知道item_id是如何发挥作用的。根据您的需要调整此查询。

Answer 2

一种选择是使用相关子查询:

样本数据:

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    )

查询从这里开始:

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>