两个日期范围之间的数量之和

Question

我有两个表 - tab1和tab2。

TAB1包含
项目和日期

|Item|Dates     |
|I1  |06-30-2022|
|I1  |07-02-2022|
|I1  |07-05-2022|

TAB2包含项目，QTY和ITEMAVAILDATE

   |Item|Qty|ItemAvailDate|
   |I1  |10 |06-30-2022|
   |I1  |20 |07-01-2022|
   |I1  |40 |07-02-2022|
   |I1  |30 |07-03-2022|
   |I1  |40 |07-04-2022|
   |I1  |50 |07-05-2022|

我希望Tab2的数量在TAB1中的日期范围内求和，

|Item|Dates     |Total(Qty)|
|I1  |06-30-2022|30        |
|I1  |07-02-2022|110       |
|I1  |07-05-2022|50        | 

即从表格中的第一次日期开始-06-30-2022到下一个日期，TAB1-07-02-2022（不包括07-02）
第2行的总和为07-02-2022到07-05-2022（不包括07-05）
row3的数量总和为07-05-2022，因为此之后没有其他行

tab1中第一个日期的所有tab2的所有QTY都应求和到TAB1中的下一个日期（不包括该约会日期）。
日期应由TAB1中的可用项目划分

我不想使用循环。使用Oracle的分析函数？
任何简单的逻辑 提前致谢！

Answer 1

使用LEAD和一个相关的子查询：

WITH next_dates (item, dates, next_date) AS (
  SELECT item,
         dates,
         LEAD(dates, 1, dates + 1) OVER (PARTITION BY item ORDER BY dates)
  FROM   tab1
)
SELECT item,
       dates,
       (
         SELECT SUM(qty)
         FROM   tab2 t2
         WHERE  n.item = t2.item
         AND    n.dates <= t2.itemavaildate
         AND    t2.itemavaildate < n.next_date
       ) AS total
FROM   next_dates n

对于示例数据：

CREATE TABLE Tab1 (Item, Dates) AS
SELECT 'I1', DATE '2022-06-30' FROM DUAL UNION ALL
SELECT 'I1', DATE '2022-07-02' FROM DUAL UNION ALL
SELECT 'I1', DATE '2022-07-05' FROM DUAL;

CREATE TABLE Tab2 (Item, Qty, ItemAvailDate) AS
SELECT 'I1', 10, DATE '2022-06-30' FROM DUAL UNION ALL
SELECT 'I1', 20, DATE '2022-07-01' FROM DUAL UNION ALL
SELECT 'I1', 40, DATE '2022-07-02' FROM DUAL UNION ALL
SELECT 'I1', 30, DATE '2022-07-03' FROM DUAL UNION ALL
SELECT 'I1', 40, DATE '2022-07-04' FROM DUAL UNION ALL
SELECT 'I1', 50, DATE '2022-07-05' FROM DUAL;

输出：

item	日期	total
i1	30-jun-22	30
i1	02-jul-22	110
i1	05-jul-22	50

db＆lt;小提琴= AFB2E6B7A8E4CA768B8AF95C53AB897B“ rel =“ nofollow noreferrer”>此处

Answer 2

在Oracle 12.1及更高版本中，match_recognize可以快速完成此类作业的工作。第一个联合所有来自两个表的行，分配null从表1的行中分配数量，并跟踪该行来自哪个表， 。然后，一切都准备好用于match_recognize的微不足道应用：

select item, dates, total_qty
from   (
         select item, null as qty, dates, 1 as tbl from tab1
         union all
         select item, qty, itemavaildate, 2 as tbl from tab2
       )
match_recognize (
  partition by item
  order     by dates, tbl
  measures  t1.dates as dates, sum(qty) as total_qty
  pattern   ( t1 t2*)
  define    t1 as tbl = 1, t2 as tbl = 2
);