PostgreSQL 16酒店预订工具：每日房价计算实现问询

我正在使用PostgreSQL 16开发酒店预订工具，需要计算入住总价及对应每日房价。

表结构简述 #

• category_prices：存储各房型的base_price，任意日期下每个房型仅有一条生效数据，无重叠区间。
• price_adjustments：存储因入住率提升等产生的临时调价，区间可存在任意重叠，重叠区间的调价需累加。

当前实现情况 #

我已写出能计算每个预订总价（基础价总和+调价总和）的SQL，但不知道如何计算每日房价：

SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    SUM((
        upper(hb.booking_period * cp.valid_period) -
        lower(hb.booking_period * cp.valid_period)
    ) * cp.base_price) + subquery.booking_price_adjustment AS total_price
FROM
    hotel_bookings hb
JOIN category_prices cp ON hb.room_category_id = cp.room_category_id
    AND hb.booking_period && cp.valid_period
LEFT JOIN (
    SELECT
        hb.booking_id,
        COALESCE(SUM((
            upper(hb.booking_period * pa.valid_period) -
            lower(hb.booking_period * pa.valid_period)
        ) * pa.price_adjustment), 0) AS booking_price_adjustment
    FROM
        hotel_bookings hb
    LEFT JOIN price_adjustments pa ON hb.room_category_id = pa.room_category_id
        AND hb.booking_period && pa.valid_period
    GROUP BY
        hb.booking_id
) subquery ON hb.booking_id = subquery.booking_id
GROUP BY
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    subquery.booking_price_adjustment;

需求目标 #

我是数据库新手（这是我第一次使用子查询），尝试生成无重叠且覆盖整个预订区间的数据集，格式如下：

{ interval_1 : relevant_base_price + sum(relevant_price_adjustments)
, interval_2 : relevant_base_price + sum(relevant_price_adjustments)
, ...
, interval_n : relevant_base_price + sum(relevant_price_adjustments)}

但所有尝试均失败。

表结构及测试数据 #

CREATE TABLE room_categories 
(
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(25)
);


CREATE TABLE category_prices 
(
    category_price_id SERIAL PRIMARY KEY,
    room_category_id INTEGER REFERENCES room_categories(category_id),
    valid_period daterange,
    base_price DECIMAL(6, 2)
);

CREATE TABLE price_adjustments 
(
    adjustment_id SERIAL PRIMARY KEY,
    room_category_id INTEGER REFERENCES room_categories(category_id),
    valid_period daterange,
    price_adjustment DECIMAL(6, 2)
);

CREATE TABLE hotel_bookings 
(
    booking_id SERIAL PRIMARY KEY,
    guest_name VARCHAR(35),
    room_category_id INTEGER REFERENCES room_categories(category_id),
    booking_period daterange
);

-- 测试数据：

INSERT INTO room_categories (category_name) VALUES
    ('single room'),
    ('double room')
returning *;

INSERT INTO category_prices (room_category_id, valid_period, base_price) VALUES
    (1, '[2023-01-01, 2023-01-31]', 80.00),
    (1, '[2023-02-01, 2023-02-28]', 85.00),
    (1, '[2023-03-01, 2023-03-31]', 88.00),
    (2, '[2023-01-01, 2023-01-31]', 100.00),
    (2, '[2023-02-01, 2023-02-28]', 105.00),
    (2, '[2023-03-01, 2023-03-31]', 108.00)
returning *;

INSERT INTO price_adjustments (room_category_id, valid_period, price_adjustment) VALUES
    (1, '[2023-01-15, 2023-02-14]', 11.00),
    (1, '[2023-01-10, 2023-01-20]', 7.00),
    (1, '[2023-01-28, 2023-02-14]', 8.00),
    (1, '[2023-01-17, 2023-02-03]', 13.00)
returning *;

INSERT INTO hotel_bookings (guest_name, room_category_id, booking_period) VALUES
    ('John Doe', 1, '[2023-01-15, 2023-01-20)'), 
    ('Jane Smith', 1, '[2023-01-30, 2023-02-02)'), 
    ('Jane Smith', 1, '[2023-02-25, 2023-03-03)'),
    ('Jordan Miller', 2, '[2023-01-30, 2023-03-02)')
returning *;

内容的提问来源于stack exchange，提问作者Andreas M.