计算与重叠的日期范围相关联的值的总和 [英] compute sum of values associated with overlapping date ranges

问题描述

我有一个简单的日期范围表，每个日期范围都有相关的每周工作小时数：

I have a simple table of date ranges each with an associated number of hours per week:

CREATE TABLE tmp_ranges (
  id SERIAL PRIMARY KEY,
  rng daterange,
  hrs_per_week INT
 );

还有一些我想据以计算（即合计）每周工作时间总和的值重叠/相交的日期范围：

And some values from which I would like to compute (ie aggregate) the sum of hours per week for the overlapping/intersecting date ranges:

INSERT INTO tmp_ranges (rng, hrs_per_week) VALUES
   ('[2014-03-15, 2014-06-28]', 9),
   ('[2014-04-18, 2014-07-15]', 2),
   ('[2014-06-03, 2014-09-12]', 9),
   ('[2014-10-03, 2014-11-14]', 6);

用图形表示（希望这能揭示的内容多于模糊），解决方案如下：

Graphically (and hopefully this reveals more than it obscures), the solution looks as follows:

hrs/wk      T                                                 T`
  9         |  }-----|--------|-------->                      |
            |                                                 |
  2         |        }--------|--------|----->                |
            |                                                 |
  9         |                 }--------|------|---->          |
            |                                                 |
  6         |                                          }--->  |
            |                                                 |
 agg.hrs/wk     --9-- ---11--- ---20--- --11-- --9--    -6- 

最终日期范围故意与其他记录不连续，但仍将包含在最终记录集中...

显然，该解决方案需要从原始4条记录中生成6条记录，我很确定答案涉及使用窗口函数，但我完全不知所措...

The final date range is deliberately non-contiguous with the other records but would still be included in the final recordset...
Clearly the solution entails generating 6 records from the original 4 and I'm pretty sure that the answer involves using window functions but I'm completely at a loss...

有没有办法做到这一点？

Is there a way to accomplish this?

非常感谢！

推荐答案

这是我的尝试解决此问题：

Here is my attempt to solve this problem:

select y,
     sum( hrs_per_week )
from tmp_ranges t
join(
  select daterange( x,
         lead(x) over (order by x) ) As y
  from (
    select lower( rng ) As x
    from tmp_ranges
    union 
    select upper( rng )
    from tmp_ranges
    order by x
  ) y
) y
on t.rng && y.y
group by y
order by y

演示： http://sqlfiddle.com/#!15/ef6cb/13

< br>
最里面的子查询使用 union 将所有边界日期收集到一组中，然后对其进行排序。

然后，外部子查询建立新的范围使用 lead 函数从相邻日期开始。

最后，这些新范围将加入到主查询中的源表中，并进行汇总和 sum 是计算出来的。

Demo: http://sqlfiddle.com/#!15/ef6cb/13

The innermost subquery collects all boundary dates into one set using union, then sorts them.
Then the outer subquery builds new ranges from adjacent dates using lead function.
In the end, these new ranges are joined to the source table in the main query, aggregated, and sum is calculated.

EDIT

最里面的查询中的 order by 子句是多余的，可以跳过，因为 lead（x）超过按日期对记录进行排序，而不必对最里面的子查询的结果集进行排序。

EDIT
The order by clause in the innermost query is redundant and can be skipped, because lead(x) over caluse orders records by dates, and a resultset from the innermost subquery doesn't have to be sorted.

这篇关于计算与重叠的日期范围相关联的值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！