如何用SQL计算随时间变化的产品组合COGS？

问题描述 #

我司销售由多种产品组成的产品组合（Bundle），单个产品（product_sku）的COGS（销货成本）随时间变化，因此组合的总COGS也会变动。需要编写SQL查询，基于产品COGS的历史数据，计算组合在不同时间段的总COGS——总COGS为对应时间段内各产品COGS乘以数量的总和。

组合可包含2种及以上不同产品，各产品数量不同（例如：1xA|1xB、1xA|1xB|2xC等），且产品COGS变动无规律。

输入示例 #

期望输出 #

解决思路 #

核心是拆分所有产品的有效日期节点，生成组合的有效时间区间，再匹配各产品对应区间的COGS计算总和：

1. 提取所有时间节点：收集每个Bundle下所有产品的valid_from和valid_to，去重后排序，生成相邻的日期对作为候选区间。
2. 匹配产品有效COGS：对每个候选区间，筛选出该Bundle下所有产品中，COGS有效期完全覆盖候选区间的记录（即产品的valid_from ≤ 候选区间起始日，valid_to ≥ 候选区间结束日）。
3. 计算组合总COGS：按Bundle和候选区间分组，求和COGS * quantity得到总COGS。
4. 过滤无效区间：确保每个区间内组合的所有产品都有有效COGS记录（避免组合缺失产品的无效区间）。

SQL实现示例 #

以下代码适用于支持CTE（公共表表达式）的数据库（如PostgreSQL、MySQL 8.0+、SQL Server等）：

WITH date_nodes AS (
    -- 提取每个Bundle的所有时间节点并去重
    SELECT Bundle, valid_from AS date_point
    FROM your_table_name
    UNION
    SELECT Bundle, valid_to AS date_point
    FROM your_table_name
),
candidate_intervals AS (
    -- 生成每个Bundle的连续时间区间
    SELECT 
        Bundle,
        date_point AS valid_from,
        LEAD(date_point) OVER (PARTITION BY Bundle ORDER BY date_point) AS valid_to
    FROM date_nodes
    WHERE LEAD(date_point) OVER (PARTITION BY Bundle ORDER BY date_point) IS NOT NULL
),
bundle_product_counts AS (
    -- 统计每个Bundle包含的产品数量（用于过滤无效区间）
    SELECT Bundle, COUNT(DISTINCT product_sku) AS product_count
    FROM your_table_name
    GROUP BY Bundle
)
SELECT 
    ci.Bundle,
    SUM(t.COGS * t.quantity) AS COGS,
    ci.valid_from,
    ci.valid_to
FROM candidate_intervals ci
JOIN your_table_name t 
    ON ci.Bundle = t.Bundle
    AND t.valid_from <= ci.valid_from
    AND t.valid_to >= ci.valid_to
JOIN bundle_product_counts bpc 
    ON ci.Bundle = bpc.Bundle
GROUP BY ci.Bundle, ci.valid_from, ci.valid_to, bpc.product_count
-- 确保区间内所有产品都有有效记录
HAVING COUNT(DISTINCT t.product_sku) = bpc.product_count
ORDER BY ci.Bundle, ci.valid_from DESC;

代码说明 #

• date_nodes：收集每个Bundle的所有时间节点，避免重复。
• candidate_intervals：用LEAD()函数生成相邻日期的区间，这些区间是组合可能的有效时间段。
• bundle_product_counts：统计每个Bundle的产品数量，确保后续分组时每个区间都包含所有产品的COGS记录。
• 主查询：匹配候选区间与产品COGS记录，计算总和并过滤无效区间，最后按Bundle和时间倒序排序。

注意事项 #

• 如果数据库不支持LEAD()函数，可以用自连接的方式生成候选区间。
• 若存在日期重叠或间隙，该逻辑依然适用，因为它基于所有产品的时间节点拆分区间。
• 确保日期字段类型为DATE或兼容类型，避免时间格式错误。

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