PostgreSQL中重叠日期时段的可用库存计算方案咨询

核心思路 #

将每个时段（包括永久生效的Start/End为null的行）拆分为单日记录，优先取特定时段的可用量，无特定时段时使用基础可用量，最后按日期聚合得到每日可用数，再统计指定时段的最小可用量（确保整个时段都能满足请求）。

实现代码（PostgreSQL 13兼容） #

WITH product_avail AS (
    -- 替换为生成你当前结果的实际查询语句
    SELECT 
        "Product ID" AS product_id,
        "Start" AS start_date,
        "End" AS end_date,
        "AvailableAmount" AS available_amount
    FROM your_source_query
),
date_ranges AS (
    -- 生成需要覆盖的日期范围，按需调整起止日期
    SELECT generate_series(
        '2022-07-20'::DATE,
        '2022-07-30'::DATE,
        '1 day'::INTERVAL
    )::DATE AS single_date
),
daily_records AS (
    -- 处理基础行（永久生效的可用量）
    SELECT
        pa.product_id,
        dr.single_date,
        -- 优先取当日对应的特定时段可用量，无则用基础量
        COALESCE(
            (SELECT available_amount 
             FROM product_avail pa2 
             WHERE pa2.product_id = pa.product_id
               AND pa2.start_date <= dr.single_date
               AND (pa2.end_date >= dr.single_date OR pa2.end_date IS NULL)
               AND pa2.start_date IS NOT NULL),
            pa.available_amount
        ) AS daily_amount
    FROM product_avail pa
    CROSS JOIN date_ranges dr
    WHERE pa.start_date IS NULL

    UNION ALL

    -- 拆分特定时段行为单日记录
    SELECT
        pa.product_id,
        generate_series(
            pa.start_date::DATE,
            pa.end_date::DATE,
            '1 day'::INTERVAL
        )::DATE AS single_date,
        pa.available_amount AS daily_amount
    FROM product_avail pa
    WHERE pa.start_date IS NOT NULL
),
final_daily AS (
    -- 去重，取每日最小可用量（特定时段量优先覆盖基础量）
    SELECT
        product_id,
        single_date,
        MIN(daily_amount) AS daily_amount
    FROM daily_records
    GROUP BY product_id, single_date
)
-- 查询指定时段的可用量（取时段内每日可用量的最小值）
SELECT
    product_id,
    MIN(daily_amount) AS available_for_period
FROM final_daily
WHERE single_date BETWEEN '2022-07-20' AND '2022-07-25'
GROUP BY product_id;

PostgreSQL 14优化方案（可选） #

升级到14后可使用daterange类型简化时段判断，代码更简洁：

WITH product_avail AS (
    SELECT 
        "Product ID" AS product_id,
        -- 将时段转为daterange，永久时段设为超大范围
        CASE 
            WHEN "Start" IS NULL AND "End" IS NULL THEN daterange('1900-01-01', '2100-01-01', '[]')
            ELSE daterange("Start", "End", '[]')
        END AS avail_range,
        "AvailableAmount" AS available_amount
    FROM your_source_query
),
date_ranges AS (
    SELECT generate_series('2022-07-20'::DATE, '2022-07-30'::DATE, '1 day')::DATE AS single_date
),
daily_avail AS (
    SELECT
        pa.product_id,
        dr.single_date,
        pa.available_amount
    FROM product_avail pa
    CROSS JOIN date_ranges dr
    WHERE dr.single_date <@ pa.avail_range -- 判断日期是否在时段内
)
SELECT
    product_id,
    single_date,
    MIN(available_amount) AS daily_amount
FROM daily_avail
GROUP BY product_id, single_date;

结果验证 #

执行上述代码后，产品ID=1的2022-07-20至2022-07-25时段每日可用量将与你给出的示例完全一致，最终该时段的可用量为1，符合预期。

内容的提问来源于stack exchange，提问作者user7820960