在PostgreSQL中汇总连接表中的 case 值

我正在为我们的服务台/接待处创建一个wine 店预订工具.我当前的SELECT不计算"最终"sum()/CASE，这意味着只要每booking_ID有一个以上的重叠(hb.booking_period && cp.valid_period)，数据输出中的CASE值只代表总价格的份额.如果你把这些股份加起来，就等于(住宿)的总价.我如何计算所有这CASE个结果/份额的总和(符合相关的booking_id个结果/份额)？

SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    cp.base_price,
    CASE
        WHEN upper(hb.booking_period * cp.valid_period) = upper(hb.booking_period) THEN
            (upper(hb.booking_period * cp.valid_period) - lower(hb.booking_period * cp.valid_period)) * cp.base_price
        ELSE
            (upper(hb.booking_period * cp.valid_period) - lower(hb.booking_period * cp.valid_period) + 1) * cp.base_price
    END

FROM
    hotel_bookings hb
JOIN
    category_prices cp ON hb.room_category_id = cp.room_category_id AND hb.booking_period && cp.valid_period
GROUP BY
    hb.booking_id, hb.guest_name, hb.room_category_id, hb.booking_period, cp.base_price, cp.valid_period;

booking_period和valid_period是日期范围数据类型.

CASE可确保正确计算intersection * base_price，否则每个交叉口将丢失1个停留时间.

以下是表格定义:

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(10, 2)
);

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

数据:

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

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

INSERT INTO hotel_bookings (guest_name, room_category_id, booking_period) VALUES
    ('John Doe', 1, '[2023-01-15, 2023-01-20)'), --correct calced, 1 intersection
    ('Jane Smith', 1, '[2023-01-30, 2023-02-02)'), -- 2 shares (need/want a sumup)
    ('Jane Smith', 1, '[2023-02-25, 2023-03-03)'),
    ('Jordan Miller', 2, '[2023-01-30, 2023-03-02)'); -- 3 shares (need/want a sumup)