PostgreSQL销售数据拆分分配至多槽位的实现与优化咨询

多槽位拆分实现方案 #

无需使用游标即可实现任意数量的槽位拆分，相比WITH RECURSIVE，累积区间交集匹配是更简洁高效的方案，没有拆分槽位数量上限，核心逻辑是分别计算销售的占用区间和槽位的容量区间，直接匹配两个区间的重叠部分即可得到拆分数量。

实现代码 #

-- 1. 计算每条销售的累积占用区间：按销售ID顺序，得到每条记录的起始/结束占用位置
WITH sales_range AS (
    SELECT
        id AS sale_id,
        qty,
        COALESCE(SUM(qty) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS start_pos,
        SUM(qty) OVER (ORDER BY id) AS end_pos
    FROM sales
),
-- 2. 计算每个槽位的累积容量区间：按槽位ID顺序，额外增加兜底d槽承接溢出部分
slots_range AS (
    SELECT
        name,
        COALESCE(SUM(size) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS slot_start,
        SUM(size) OVER (ORDER BY id) AS slot_end
    FROM slots
    UNION ALL
    SELECT 'd' AS name, (SELECT SUM(size) FROM slots) AS slot_start, 2147483647 AS slot_end
)
-- 3. 区间匹配计算分配数量：重叠区间的长度就是当前槽位分配给对应销售的数量
SELECT
    sale_id,
    slot_name,
    allocated_qty
FROM (
    SELECT
        s.sale_id,
        sl.name AS slot_name,
        LEAST(s.end_pos, sl.slot_end) - GREATEST(s.start_pos, sl.slot_start) AS allocated_qty
    FROM sales_range s
    INNER JOIN slots_range sl
        ON sl.slot_start < s.end_pos AND sl.slot_end > s.start_pos
) t
WHERE allocated_qty > 0
ORDER BY sale_id, slot_name;

示例运行结果 #

对应测试数据的分配结果如下：

百万级数据性能优化 #

针对大数量场景下的槽位匹配性能，可从以下维度优化：

• 预计算冗余字段：如果槽位配置不常变更，提前把槽位的slot_start、slot_end预存到槽位表中；如果销售数据写入后不再修改，写入时直接预计算每条销售的start_pos、end_pos冗余存储，避免查询时实时执行窗口函数的开销。
• 索引优化：销售表保留id主键索引，如有过滤条件可加联合索引；预计算的槽位表给slot_start、slot_end加联合索引，加速区间匹配。
• 分批次处理：单次处理数据量过大时，按销售ID范围拆分成多批次执行，避免单次查询占用过多内存导致慢查询。
• 兜底槽位特殊处理：可单独判断销售的end_pos超过所有槽位总容量的部分直接分配给d槽，无需走join匹配逻辑，减少计算量。

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