日期之间的SQL总和

Question

我需要在范围日期的相交中汇总值。

源

人员	项目	start_date	end_date	值
A	苹果	08.03.2018	29.03.2018	3
A	Apple	01.01.2019	08.08.08.2021	2
A	Apple	01.01.10.10.2019	09.10.2021	5
A	PEN	​	​	​
​	​	数据	20.03.2018	8
a	cup	15.03.2018	20.03.2019	2
b	pen	10.10.2021	30.10.2021	2
b	pen	10.10.2021	30.10.2021	6
b	orange	10.11.2021	10.11.2022	3
b	orange	20.11.2021	20.12.2021	2

expected结果

人员	项目	start_date	end_date	值
A	苹果	08.03.2018	29.03.2018	3
A	Apple	01.01.2019	08.08.2021	7
A	Apple	09.08.2021	09.10.2021	5
A	PER A PER	10.10.2021	30.10.10.10.10.10.2021	2
A	CUP	08.08.08.03.2018	14.0.03.2018 14.303.2018	8
杯	10.11.2021	15.03.2018	20.03.2018	10
A	杯	21.03.2018	20.03.2019	2
B	Pen	10.10.2021	30.10.2021	8
B	橙色	Orange	19.11.2021	3
B	橙色	20.11.11.2021	20.12.12.2021	5
B	21.12	。 2021	10.11.2022	3

我使用这样的代码，但这很简单，结果不好，

  select
  person
  ,item
  ,Min([start_date]) as [start_date]
  ,Max([end_date]) as [end_date]
  ,Sum([value]) as [value]
  FROM table
  Group by   person, item

我尝试使用lag（）函数，但是我迷路了

Answer 1

我无权访问突触，但是假设它与SQL Server兼容...

内部查询构建数据范围，在需要时为重叠期创建其他日期。主查询仅总和值。

select person, item, range_from, range_to, 
       (select sum(value) from test 
            where person = r.person 
              and item = r.item 
              and range_from between start_date and end_date) value
from ( 
  select 
     be, 
     person, 
     item, 
     date range_from, 
     lead(date,1) over(partition by person, item order by date,be) range_to 
  from (      
     select 1 be, person, item, start_date date from test
     union 
     select 2, person, item, end_date from test
     union  
     select 2, person, item, dateadd(day,-1,start_date) from test a
       where exists (select * from test where a.person = person and a.item = item and a.start_date > start_date and a.start_date < end_date)
     union 
     select 1, person, item, dateadd(day,1,end_date) from test b
       where exists (select * from test where b.person = person and b.item = item and b.end_date > start_date and b.end_date < end_date)
  ) k 
) r where r.be = 1 order by r.person, r.item, r.range_from 

列 BE 包含：

• 1-对于期间开始
• 2-对于期间结束