Snowflake SQL实现已发布单元的全量购买日期归因

业务约束 #

• 单日仅能进行单元发布（Released Units）或购买（Bought Units）操作，其中一列值恒为0；
• 当日发布的单元可从次日起被购买；
• 单日发布的单元可分多日分批购买，无需在单日全部购完；
• 允许超卖场景（购买量超过已发布总量）。

输入输出示例 #

示例1：正常购买场景 #

输入数据

期望输出

示例2：超卖场景 #

输入数据

期望输出

以下SQL通过累积计算发布和购买的单元量，匹配每条发布记录对应的完成购买日期：

WITH 
-- 1. 统一处理日期格式，计算发布/购买的累积量
base_data AS (
    SELECT 
        TO_DATE("Event Date", 'MM/DD/YYYY') AS event_date,
        "Released Units" AS released_units,
        "Bought Units" AS bought_units,
        -- 计算截至当前日期的累计发布量（排除当日，因为当日发布次日才可购买）
        SUM("Released Units") OVER (ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS cumulative_released,
        -- 计算截至当前日期的累计购买量
        SUM("Bought Units") OVER (ORDER BY event_date) AS cumulative_bought
    FROM your_table_name
),
-- 2. 提取所有发布记录和购买记录，构建匹配关系
releases AS (
    SELECT 
        event_date AS release_date,
        released_units,
        -- 计算该发布记录的累积发布区间（起始、结束）
        SUM(released_units) OVER (ORDER BY event_date) AS release_cum_end,
        COALESCE(SUM(released_units) OVER (ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS release_cum_start
    FROM base_data
    WHERE released_units > 0
),
purchases AS (
    SELECT 
        event_date AS purchase_date,
        bought_units,
        -- 计算该购买记录的累积购买区间（起始、结束）
        cumulative_bought AS purchase_cum_end,
        COALESCE(cumulative_bought - bought_units, 0) AS purchase_cum_start
    FROM base_data
    WHERE bought_units > 0
),
-- 3. 匹配发布与购买区间，找到覆盖发布区间的最早购买日期
release_purchase_matches AS (
    SELECT 
        r.release_date,
        r.released_units,
        -- 找到第一个使得累计购买量覆盖该发布记录的累计区间的购买日期
        MIN(p.purchase_date) AS fully_bought_date
    FROM releases r
    LEFT JOIN purchases p
    -- 购买的累积区间需与发布的累积区间有重叠，且购买日期晚于发布日期
    ON p.purchase_date > r.release_date
        AND p.purchase_cum_end > r.release_cum_start
        AND p.purchase_cum_start < r.release_cum_end
    GROUP BY r.release_date, r.released_units
)
-- 4. 关联回原始数据，输出最终结果
SELECT 
    TO_CHAR(bd.event_date, 'MM/DD/YYYY') AS "事件日期（Event Date）",
    bd.released_units AS "发布单元数（Released Units）",
    TO_CHAR(rpm.fully_bought_date, 'MM/DD/YYYY') AS "全量购买完成日期（Units fully bought by）"
FROM base_data bd
LEFT JOIN release_purchase_matches rpm
    ON bd.event_date = rpm.release_date
ORDER BY bd.event_date;

关键逻辑说明 #

1. 累积量计算：通过窗口函数计算截至每个日期的累计发布量（排除当日，因为当日发布次日才可购买）和累计购买量，为后续区间匹配做准备；
2. 区间构建：将每条发布记录转化为累计发布的区间，每条购买记录转化为累计购买的区间；
3. 区间匹配：通过区间重叠逻辑，找到能覆盖该发布记录累计区间的最早购买日期，确保购买记录被合理分配，不会重复使用；
4. 结果关联：将匹配结果关联回原始数据，输出符合要求的最终格式。

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