如何通过考虑PostgreSQL中的重叠天数来扩大日期范围？

Question

给定下表使用以下SQL语句在PostgreSQL数据库中创建的表格：

CREATE TABLE reservations (
    reservation_id INT PRIMARY KEY,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL
);

INSERT INTO reservations VALUES
    (1,  '2021-01-04', '2021-01-05'),
    (2,  '2021-01-05', '2021-01-07'),
    (3,  '2021-01-06', '2021-01-07'),
    (4,  '2021-01-06', '2021-01-08'),
    (5,  '2021-01-08', '2021-01-09'),
    (6,  '2021-01-08', '2021-01-09'),
    (7,  '2021-01-08', '2021-01-10'),
    (8,  '2021-01-13', '2021-01-17'),
    (9,  '2021-01-13', '2021-01-14'),
    (10, '2021-01-14', '2021-01-15'),
    (11, '2021-01-15', '2021-01-16'),
    (12, '2021-01-16', '2021-01-17');

该表表示床位的预订列表，并带有签入日期和退房日期。

要求检索每周应该设置多少个房间。为了减少维护费用，需要最大程度地减少提供的房间数量。此外，始终可以在结帐日期的同一天提供房间。例如，如果在 2021-01-08 上检查了一个房间，并且在同一天检查了另一个房间，则需要用于 2021-01的房间计数-08 只有1 ，而不是2 。

结果应为以下内容：

nek	number_of_rooms
1	3
2	2

我的方法是：

WITH reservations_with_expanded_dates AS (
    SELECT
        reservation_id,
        Generate_series(check_in_date::DATE,
            check_out_date::DATE,
            '1day')::DATE AS stay
    FROM
        reservations
)
SELECT
    week,
    max(number_of_rooms)
FROM (
    SELECT
        Date_part('week', stay) AS week,
        stay,
        count(*) AS number_of_rooms
    FROM
        reservations_with_expanded_dates
    GROUP BY
        stay) AS reservations_per_day
GROUP BY
    week

给出以下结果：

nek	number_of_rooms
1	4
2	3

结果不正确，因为我的方法没有考虑到房间的事实始终可以在结帐日期的同一天提供。如何提高查询以考虑到这一点？

此外，在绩效方面有更好的方法可以实现我要做的事情吗？

Answer 1

您可以做：

select week, max(rooms_used) as max_rooms_used
from (
  select date_part('week', d) as week, count(*) as rooms_used
  from (
    select *, 
      generate_series(check_in_date,
                      check_out_date - 1,
                      interval '1 day')::date as d
    from reservations
  ) x
  group by d
) y
group by week

结果：

week  max_rooms_used
----  --------------
1     3
2     2

请参见

Answer 2

这样的事情会起作用，它每天都会向您显示您需要多少个房间以及房间是什么房间。这只是一个可以通过延迟检查，不同类型的房间等进行扩展

CREATE TABLE reservations (
    reservation_id INT PRIMARY KEY,
    check_in_out tsrange
);

INSERT INTO reservations VALUES
    (1,  tsrange('2021-01-04 15:00', '2021-01-05 11:00','[)')),
    (2,  tsrange('2021-01-05 15:00', '2021-01-07 11:00','[)')),
    (3,  tsrange('2021-01-06 15:00', '2021-01-07 11:00','[)')),
    (4,  tsrange('2021-01-06 15:00', '2021-01-08 11:00','[)')),
    (5,  tsrange('2021-01-08 15:00', '2021-01-09 11:00','[)')),
    (6,  tsrange('2021-01-08 15:00', '2021-01-09 11:00','[)')),
    (7,  tsrange('2021-01-08 15:00', '2021-01-10 11:00','[)')),
    (8,  tsrange('2021-01-13 15:00', '2021-01-17 11:00','[)')),
    (9,  tsrange('2021-01-13 15:00', '2021-01-14 11:00','[)')),
    (10, tsrange('2021-01-14 15:00', '2021-01-15 11:00','[)')),
    (11, tsrange('2021-01-15 15:00', '2021-01-16 11:00','[)')),
    (12, tsrange('2021-01-16 15:00', '2021-01-17 11:00','[)'));
    
    
SELECT  s::date
    ,   EXTRACT(WEEK FROM s)
    ,   COUNT(DISTINCT reservation_id)
    ,   ARRAY_AGG(DISTINCT reservation_id ORDER BY reservation_id)
FROM    reservations
        RIGHT JOIN  (SELECT s FROM generate_series('2021-01-01 15:00'::timestamp, '2021-01-21'::timestamp, INTERVAL '1 DAY') g(s)) s ON s <@ check_in_out
GROUP BY 1,2
ORDER BY 1;

。

UPDATE  reservations
SET     check_in_out = tsrange('2021-01-06 15:00', '2021-01-08 17:00','[)')
WHERE   reservation_id = 4;

示例

UPDATE  reservations
SET     check_in_out = tsrange('2021-01-08 18:00', '2021-01-10 11:00','[)')
WHERE   reservation_id = 7;

的 您需要多少个房间：

SELECT  s::date
    ,   EXTRACT(WEEK FROM s)
    ,   COUNT(DISTINCT reservation_id)
    ,   ARRAY_AGG(DISTINCT reservation_id ORDER BY reservation_id)
FROM    reservations
        RIGHT JOIN  (SELECT s FROM generate_series('2021-01-01 18:00'::timestamp, '2021-01-21'::timestamp, INTERVAL '1 DAY') g(s)) s ON s <@ check_in_out
GROUP BY 1,2
ORDER BY 1;