代码之家 › 专栏 › 技术社区 › user908094

计算与重叠日期范围相关的值的总和

date-range postgresql

user908094 · 技术社区 · 10 年前

我有一个简单的日期范围表,每个日期范围都有相关的每周小时数:

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

以及一些值,我希望根据这些值计算(即聚合)重叠/交叉日期范围的每周小时数:

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

从图形上看(希望这能揭示更多而不是模糊),解决方案如下:

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

最终日期范围故意与其他记录不连续,但仍将包含在最终记录集中。。。
显然,解决方案需要从原始的4条记录中生成6条记录,我很确定答案是使用窗口函数,但我完全不知所措。。。

有没有办法做到这一点?

提前感谢!

1 回复 | 直到 10 年前

krokodilko 10 年前

以下是我解决这个问题的尝试:

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

最里面的子查询使用 union ,然后对它们进行排序。
然后,外部子查询使用 lead 作用
最后,这些新范围将连接到主查询中的源表,并进行聚合 sum 计算。

编辑
这个 order by 最里面的查询中的子句是多余的,可以跳过,因为 lead(x) over caluse按日期排序记录,最里面的子查询的结果集不必排序。

推荐文章

Ishaan Adarsh · 未知的META规范,无法验证。[规范v1.0.1]

1 年前

tggtsed · PostgreSQL从平均值中排除值

1 年前

Dawid · 为什么我不能在子查询中使用表别名?

2 年前

LAZ · ORDER BY id DESC LIMIT 100返回第一行而不是最后一行

2 年前

Mohammed Shaheer · 查找列中的数据是否满足sql中的数据类型条件

2 年前

AKang123. · Postgres:重复行,但更新json中的字段

2 年前

CraZyCoDer · 在PostgreSQL中锁定潜在事务

2 年前

ranebec · 计数时如何返回0而不是null?

2 年前

Ramón J Romero y Vigil · 将没有时区列的postgres时间戳转换为日期

2 年前

Flo · 分组依据中的SQL大小写

2 年前