时间戳

首页 > 解决方案 > 从多个日期范围中提取每周的天数

sql - 从多个日期范围中提取每周的天数

问题描述

我trips在 PostgreSQL 10.5 中有一张表：

id  start_date    end_date
----------------------------
1   02/01/2019    02/03/2019
2   02/02/2019    02/03/2019
3   02/06/2019    02/07/2019
4   02/06/2019    02/14/2019
5   02/06/2019    02/06/2019

我想计算与给定周重叠的旅行的天数。表中的行程具有包容性界限。周从星期一开始，到星期日结束。预期的结果是：

week_of    days_utilized
------------------------
01/28/19    5
02/04/19    8
02/11/19    4

对于日历参考：

Monday 01/28/19 - Sunday 02/03/19
Monday 02/04/19 - Sunday 02/10/19
Monday 02/11/19 - Sunday 02/17/19

我知道如何用我使用的编程语言编写它，但我更喜欢在 Postgres 中执行此操作，我不清楚从哪里开始......

标签： sqlpostgresqldate-range

解决方案

你似乎想要generate_series()and a joinand group by。要计算涵盖的周数：

select gs.wk, count(t.id) as num_trips
from generate_series('2019-01-28'::date, '2019-02-11'::date, interval '1 week') gs(wk) left join
     trips t
     on gs.wk <= t.end_date and
        gs.wk + interval '6 day' >= t.start_date
group by gs.wk
order by gs.wk;

编辑：

我看到你想要涵盖的日子。这在聚合中需要做更多的工作：

select gs.wk, count(t.id) as num_trips,
       sum( 1 +
            extract(day from (least(gs.wk + interval '6 day', t.end_date) - greatest(gs.wk, t.start_date)))
          ) as days_utilized
from generate_series('2019-01-28'::date, '2019-02-11'::date, interval '1 week') gs(wk) left join
     trips t
     on gs.wk <= t.end_date and
        gs.wk + interval '6 day' >= t.start_date
group by gs.wk
order by gs.wk;

注意：这不会返回您拥有的确切结果。我认为这些都是正确的。

推荐阅读