如何通過考慮PostgreSQL中的重疊天數來擴展日期范圍？

鑒于使用以下 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 .

結果應如下所示：

我的方法如下：

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

這給出了以下結果：

結果不正確，因為我的方法沒有考慮到房間總是可以在退房日期的同一天提供這一事實。如何改進我的查詢以考慮到這一點？

此外，在性能方面是否有更好的方法來實作我想要做的事情？

uj5u.com熱心網友回復：

你可以做：

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

請參閱db<>fiddle的運行示例。

uj5u.com熱心網友回復：

這樣的事情會起作用，它會顯示您每天需要多少個房間以及房間的預訂情況。這只是一個示例，可以通過延遲退房、不同型別的房間等進行擴展。

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;

然后您檢查 18: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 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;

標籤：sql PostgreSQL

上一篇：jsonb計算列sum()并選擇最大值/從(select(sum()))中選擇

下一篇：Ubuntu系統-FFmpeg安裝及環境配置