Sql 属于(日期)范围类型及其交集的总权重​

我正在使用PostgreSQL16为我们的前台/前台创建一个wine 店预订工具.要回答预订请求，我需要住宿的总价和相关的每日价格.

当前设置+虚拟数据:dbfiddle.uk

以下表格的简要介绍:

category_prices:
This table stores the base_prices for each room category. At any date there is exactly one dataset "active" for each category. => No overlaps and only one price per date and category.

price_adjustments:
This table includes unexpected price increases, primarily due to increased occupancy. In this table there can (theoretically) be any number of overlaps between the intervals/dateranges. The values/prices belonging to these intervals must be summed up accordingly.

我现在try 计算每个停留/间隔的总数base_price和总数price_adjustment，并将它们相加，但我不知道如何计算daily prices(甚至不知道从哪里开始):

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;

我对数据库还很陌生(这是我的第一个子查询).

我试图找出一种方法来生成一个对象/数据集/数组/ Select ，它包含/返回如下内容:

{ 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)}

其中间隔没有重叠，并且它们的并集等于初始间隔/日期范围，但我每次try 都失败.

编辑: 按照要求，表和虚拟数据(数据库复制):

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

--the data:

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