我需要从重叠日期列表中获取彼此不重叠的日期范围列表,并获取重叠期间的硬币总和。我尝试过用谷歌搜索一个例子,但到目前为止还没有运气。我可能没有使用正确的关键词?
我有一个重叠日期的列表
1.1.2018 - 31.1.2018 80
7.1.2018 - 10.1.2018 10
7.1.2018 - 31.1.2018 10
11.1.2018 - 31.1.2018 5
25.1.2018 - 27.1.2018 5
2.2.2018 - 23.2.2018 100
期望的结果是
1.1.2018 - 6.7.2018 80 coins
7.1.2018 - 10.1.2018 100 coins
11.1.2018 - 24.1.2018 95 coins
25.1.2018 - 27.1.2018 100 coins
28.1.2018 - 31.1.2018 95 coins
2.2.2018 - 23.2.2018 100 coins
这是它应该如何工作的图
|------------------------------|
|---|
|-----------------------|
|-------------------|
|---|
|----------------------|
Outcome
|------|---|----------|---|----| |----------------------|
80 100 95 100 95 100
这是我的测试数据
drop table coinsonperiod2;
create table coinsonperiod2(
id serial,
startdate date,
enddate date,
coins integer,
userid integer
);
insert into coinsonperiod2 (startdate, enddate, coins,userid) values
('2018-01-01','2018-01-31', 80,1)
, ('2018-01-07','2018-01-10', 10,1)
, ('2018-01-07','2018-01-31', 10,1)
, ('2018-01-11','2018-01-31', 5,1)
, ('2018-01-25','2018-01-27', 5,1)
, ('2018-02-02','2018-02-23', 100,2)
, ('2018-01-01','2018-01-31', 80,2)
, ('2018-01-07','2018-01-10', 10,2)
, ('2018-01-07','2018-01-31', 10,2)
, ('2018-01-11','2018-01-31', 5,2)
, ('2018-01-25','2018-01-27', 5,2)
, ('2018-02-02','2018-02-23', 100,3)
;
更新:
事实上斯蒂芬和乔普的回答并没有达到我想要的结果。两个答案都显示结束日期错误。
当一个周期结束时,下一个周期应在第二天开始(如果有间隙,则稍后开始)。在我期望的结果中,2018年1月1日至2018年1月6日包括第6天。第六名和第七名之间没有差距,因为第七名包含在 7.1.2018-10.1.2018 中。
更新2:
现在我明白了开区间、半开区间和闭区间之间的区别。在乔普解决方案中,必须针对半开区间进行计算,但我期望的结果是闭区间。这就是为什么必须减少结束日期以使结果成为闭区间的原因。如果我错了请纠正我。
我还在示例数据中添加了 userid 并进一步修改了 joops 解决方案。
这是给我想要的结果的查询。
with changes AS (
SELECT
userid,
startdate AS tickdate,
coins,
1 AS cover
FROM coinsonperiod2
UNION ALL
-- add 1 day to correct intervals into half open intervals, so the calculation is correct
SELECT
userid,
1 + enddate AS tickdate,
-1 * coins,
-1 AS cover
FROM coinsonperiod2
)
, sumchanges AS (
SELECT
userid,
tickdate,
SUM(coins) AS change,
SUM(cover) AS cover
FROM changes
GROUP BY tickdate, userid
)
, aggregated AS (
SELECT
userid AS userid,
tickdate AS startdate,
lead(tickdate)
over www AS enddate,
sum(change)
OVER www AS cash,
sum(cover)
OVER www AS cover
FROM sumchanges
WINDOW www AS (
partition by userid
ORDER BY tickdate )
)
-- reduce 1 day from the enddate to make closed interval
SELECT
userid
, startdate
, enddate-1 as enddate
, cash
, cover
FROM aggregated
WHERE cover > 0
ORDER BY userid, startdate
;
Outcome: