我需要對范圍日期相交的值求和。
源資料樣本
| 人 | 物品 | 開始日期 | 結束日期 | 價值 |
|---|---|---|---|---|
| 一個 | 蘋果 | 08.03.2018 | 29.03.2018 | 3 |
| 一個 | 蘋果 | 01.01.2019 | 08.08.2021 | 2 |
| 一個 | 蘋果 | 01.01.2019 | 09.10.2021 | 5 |
| 一個 | 筆 | 2021 年 10 月 10 日 | 2021 年 10 月 30 日 | 2 |
| 一個 | 杯子 | 08.03.2018 | 20.03.2018 | 8 |
| 一個 | 杯子 | 15.03.2018 | 20.03.2019 | 2 |
| b | 筆 | 2021 年 10 月 10 日 | 2021 年 10 月 30 日 | 2 |
| b | 筆 | 2021 年 10 月 10 日 | 2021 年 10 月 30 日 | 6 |
| b | 橙 | 2021 年 11 月 10 日 | 2022 年 11 月 10 日 | 3 |
| b | 橙 | 20.11.2021 | 20.12.2021 | 2 |
預期結果
| 人 | 物品 | 開始日期 | 結束日期 | 價值 |
|---|---|---|---|---|
| 一個 | 蘋果 | 08.03.2018 | 29.03.2018 | 3 |
| 一個 | 蘋果 | 01.01.2019 | 08.08.2021 | 7 |
| 一個 | 蘋果 | 09.08.2021 | 09.10.2021 | 5 |
| 一個 | 筆 | 2021 年 10 月 10 日 | 2021 年 10 月 30 日 | 2 |
| 一個 | 杯子 | 08.03.2018 | 14.03.2018 | 8 |
| 一個 | 杯子 | 15.03.2018 | 20.03.2018 | 10 |
| 一個 | 杯子 | 21.03.2018 | 20.03.2019 | 2 |
| b | 筆 | 2021 年 10 月 10 日 | 2021 年 10 月 30 日 | 8 |
| b | 橙 | 2021 年 11 月 10 日 | 2021 年 11 月 19 日 | 3 |
| b | 橙 | 20.11.2021 | 20.12.2021 | 5 |
| b | 橙 | 21.12.2021 | 2022 年 11 月 10 日 | 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() 函式，但我迷路了
uj5u.com熱心網友回復：
我無法訪問 Synapse ，但假設它與 SQL 服務器兼容...
db<>小提琴
內部查詢構建資料范圍，如果需要，為重疊期間創建額外的日期。主查詢只是對值求和。
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 - 期末
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/494376.html