logo标签列表

从重叠的日期范围中获取不重复的连续日期范围

sqlpostgresqloverlap
11
我需要从一组重叠的日期中获取不重叠的日期范围列表，并计算在此重叠期间内硬币的总和。我尝试通过谷歌搜索示例，但目前为止没有找到合适的结果。可能是我使用的关键词不正确？
我有一组重叠的日期。
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)
;

更新： 实际上，StephenM和joops的答案都不符合我的期望结果。两个答案都显示enddate错误。

当一个周期结束时，下一个周期应该从第二天开始（如果有间隔，则稍后开始）。在我的期望结果中，1.1.2018-6.1.2018包括第6天。第6天和第7天之间没有间隔，因为第7天包括在7.1.2018-10.1.2018中。

更新2： 现在我明白了开放、半开放和闭合区间之间的区别。在joops的解决方案中，必须根据半开放区间进行计算，但我的期望结果是闭合区间。这就是为什么必须减少enddate以使结果成为闭合区间。如果我错了，请纠正我。

我还在示例数据中添加了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
;

结果： 结果
5
非常好的问题陈述，提供了示例数据和可视化。我希望所有首次发布者都能像这样在他们的问题中付出这么多努力。 - user330315
诀窍是将电平触发表示转换为边沿触发，对它们进行累加，然后再将它们转换回来。 - joop
非重叠部分实际上相当容易：https://rextester.com/JMZC97931 - user330315
查看标题，这应该会有所帮助 https://stackoverflow.com/questions/52383964/determine-if-a-range-is-completely-covered-by-a-set-of-ranges - Salman A
如果数据库中的范围包括日期和时间，那么在1 + enddate AS tickdate的位置上，请使用DATEADD(SECOND, 1, ToDateTime) AS tickdate。毫秒更好。 - Dipon Roy
显示剩余3条评论
4个回答
3
逻辑如下：
  • 在一个时间段的开始时，将其值添加到累积总和中
  • 在一个时间段的结束时，从该总和中减去其值
  • 但是为了扫描日期线，我们需要收集所有（唯一的）日期/时间戳，无论是开始还是停止。
所以重点是：将数据从一系列时间间隔转换为一系列（开始/停止）事件，并对这些事件进行聚合。
-- \i tmp.sql

create table coinsonperiod(
  id serial,
  startdate date,
  enddate date,
  coins integer
);
insert into coinsonperiod (startdate, enddate, coins) values
  ('2018-01-01','2018-01-31', 80)
, ('2018-01-07','2018-01-10', 10)
, ('2018-01-07','2018-01-31', 10)
, ('2018-01-11','2018-01-31', 5)
, ('2018-01-25','2018-01-27', 5)
, ('2018-02-02','2018-02-23', 100)
        ;

WITH changes AS (
    SELECT startdate AS tickdate , coins
            , 1 AS cover
    FROM coinsonperiod
    UNION ALL
    -- add 1 day to convert to half-open intervals
    SELECT 1+enddate AS tickdate, -1* coins
            , -1 AS cover
    FROM coinsonperiod
    )
, sumchanges  AS (
        SELECT tickdate, SUM(coins) AS change, SUM(cover) AS cover
        FROM changes
        GROUP BY tickdate
        )
, aggregated AS (
        SELECT
        tickdate AS startdate
        , lead(tickdate) over www AS enddate
        , sum(change) OVER www AS cash
          -- number of covered intervals
        , sum(cover) OVER www AS cover
        FROM sumchanges
        WINDOW www AS (ORDER BY tickdate)
        )
             -- substract one day from enddate to correct back to closed intervals
SELECT startdate, enddate-1 AS enddate, cash, cover
FROM aggregated
WHERE cover > 0
ORDER BY startdate
        ;
从聚合中选择开始日期、结束日期减去1天、现金和覆盖范围，以开始日期为顺序，其中覆盖范围大于0。最终结果需要减去1天来修正结束日期。 - Matti Ilvonen
正确，但我仍然更喜欢半开区间;-) - joop
1

看起来我找到了一个丑陋但可用的代码

select t1.dt, t1.enddt, sum(coins)
from (
    select distinct cp1.dt, min(cp2.dt) enddt
    from ( select startdate as dt from coinsonperiod union all select enddate as dt from coinsonperiod ) cp1, 
         ( select startdate as dt from coinsonperiod union all select enddate as dt from coinsonperiod ) cp2
    where cp2.dt > cp1.dt
    group by cp1.dt
    order by cp1.dt ) t1, coinsonperiod t2
where t1.dt between t2.startdate and t2.enddate
and t1.enddt between t2.startdate and t2.enddate
group by t1.dt, t1.enddt

输出：

dt         |enddt      |sum |
-----------|-----------|----|
2018-01-01 |2018-01-07 |80  |
2018-01-07 |2018-01-10 |100 |
2018-01-10 |2018-01-11 |90  |
2018-01-11 |2018-01-25 |95  |
2018-01-25 |2018-01-27 |100 |
2018-01-27 |2018-01-31 |95  |
2018-02-02 |2018-02-23 |100 |

你的输出与我的唯一区别在于，我认为你忘记了01/10和01/11之间的间隔。
是的，我忘记了01/10和01/11之间的间隔。你的解决方案几乎是我正在寻找的。如果有一个与enddt相等的startdt，则enddt应该为“-1天”。例如： 2018-01-01 |2018-01-07 |80 | 2018-01-07 |2018-01-10 |100 | 应该变成 2018-01-01 |2018-01-06 |80 | 2018-01-07 |2018-01-10 |100 | - Matti Ilvonen
2
@MattiIlvonen - 我特别推荐在处理datetime时，但通常也要考虑date的情况，将这些时间段建模为半开放间隔，即具有包含式起始日期和排除式结束日期。这样往往更容易理解（例如，在原始数据中出现的所有日期也会出现在您的去重叠间隔中，并且对于每个这些去重叠间隔，每个结束日期对应着另一个间隔的开始日期（除了最开始/结束的部分）。 - Damien_The_Unbeliever
@Damien_The_Unbeliever：Postgres的range非常适合这种情况，如果你将示例中的startdate/enddate值转换为daterange，Postgres实际上会将其变成一个半开区间。 - user330315
0

好的，我将帮助你理解逻辑部分，语法可以在线找到。

你可以创建一个临时表并将数据移动到其中，然后选择每行数据，并为每列数据存储声明变量中的值。

接着简单地使用游标，从源表中选择所有数据，再次使用正常的大于或小于运算符来计算总数。

简单来说，获取第1行第1列，与所有其他第1列和第2列数据进行比较。

3
RDBMS的优势在于处理数据集以及能够重新组织/重新排序操作以实现最佳结果。这些概念会因使用游标进行过程逻辑而受到破坏。有时需要使用临时表和/或游标，但通常首先应该尝试使用基于集合的方法。 - Damien_The_Unbeliever
-1

正确答案：

这是我的测试数据

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)
;

更新2： 现在我明白了开放、半开放和闭合区间之间的区别。在joops的解决方案中，计算必须针对半开放区间进行，但我的期望结果是闭合区间。这就是为什么必须减少enddate以使结果成为闭合区间。如果我错了，请纠正我。

我还在样本数据中添加了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
;

结果： 结果

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接