PostgreSQL中如何对关联表的CASE计算结果求和

问题描述 #

我正在开发酒店预订前台工具，当前的SELECT语句无法计算最终总价：当单个booking_id对应的预订区间与多个价格区间重叠时，CASE语句只会输出每个重叠区间的金额份额，需要将这些份额求和才能得到该预订的总费用。请问如何按booking_id对这些份额进行求和？

当前SQL语句：

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;

表定义 #

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)'), -- 1个交集，计算正确
    ('Jane Smith', 1, '[2023-01-30, 2023-02-02)'), -- 2个份额，需求和
    ('Jane Smith', 1, '[2023-02-25, 2023-03-03)'), -- 2个份额，需求和
    ('Jordan Miller', 2, '[2023-01-30, 2023-03-02)'); -- 3个份额，需求和

解决方案 #

要按booking_id聚合总费用，需将CASE语句的计算结果用SUM()函数包裹，同时调整GROUP BY子句，只保留每个预订唯一的字段（即hotel_bookings表中每个booking_id对应的唯一字段），去掉与category_prices相关的分组字段——因为我们需要将同一个预订下的所有价格区间份额求和。

修改后的SQL语句：

SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    SUM(
        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
    ) AS total_stay_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
GROUP BY
    hb.booking_id, hb.guest_name, hb.room_category_id, hb.booking_period;

结果说明 #

执行上述SQL后，会得到每个预订的总费用：

• John Doe的预订：(20-15)*80 = 400.00（交集upper等于预订upper，无需加1）
• Jane Smith的第一个跨月预订：(31-30+1)*80 + (2-1)*85 = 160 + 85 = 245.00
• Jane Smith的第二个跨月预订：(28-25+1)*85 + (3-1)*88 = 340 + 176 = 516.00
• Jordan Miller的跨三个月预订：(31-30+1)*100 + (28-1+1)*105 + (2-1)*108 = 200 + 2940 + 108 = 3248.00

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