SQL实现：将附加产品交易匹配至主产品最早符合时间窗的记录

现有两张结构一致的表t1和t2，字段包含client_id、transaction_date、price、product：

• t1存储主产品交易数据
• t2存储附加产品交易数据

需求：判断同一客户的附加产品是否属于追加销售（附加产品交易发生在对应主产品交易的10天前后），并将附加产品价格匹配至主产品交易记录，需满足两个核心规则：

1. 附加产品仅分配给时间窗内最早的主交易记录，避免重复统计；例如客户445在2024-04-05购买的附加产品，仅匹配至2024-04-01的主交易记录（2024-03-01的记录超出10天范围）。
2. 同一客户多次购买附加产品时，需将附加产品总价汇总至最早符合条件的主交易记录，避免主交易记录重复；例如客户339的两次附加产品交易总价需仅匹配至2024-04-05的主交易记录。

用户尝试的SQL代码（仅解决第一个问题，无法处理第二个问题）：

with tmp as (select t1.*, 
    t2.price as xsell_price, t2.transaction_date as transaction_date_xsell 
from t1 
left join t2 on t1.client_id = t2.client_id and t2.transaction_date between (t1.transaction_date - 5) and (t1.transaction_date + 5)), 
tmp2 as (select t1.*, 
    row_number() over (partition by client_id, transaction_date_xsell order by transaction_date_xsell desc) as row_num 
from tmp) 
select t1.*, 
    case when row_num = 1 then xsell_price end as xsell_price_calc 
from tmp2;

示例数据 #

t1表（主产品交易） #

t2表（附加产品交易） #

期望输出 #

解决方案 #

修正后的SQL代码 #

-- 第一步：为每个附加产品找到符合时间窗的最早主交易记录
WITH t2_matched AS (
    SELECT 
        t2.client_id,
        t2.transaction_date AS xsell_date,
        t2.price AS xsell_price,
        -- 找到该附加产品对应的最早主交易记录
        FIRST_VALUE(t1.transaction_date) OVER (
            PARTITION BY t2.client_id, t2.transaction_date
            ORDER BY t1.transaction_date ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS matched_main_date
    FROM t2
    LEFT JOIN t1 
        ON t1.client_id = t2.client_id
        AND t2.transaction_date BETWEEN t1.transaction_date - INTERVAL '10 days' 
                                    AND t1.transaction_date + INTERVAL '10 days'
    WHERE t1.transaction_date IS NOT NULL -- 过滤掉无匹配主交易的附加产品
),
-- 第二步：按客户+匹配的主交易日期汇总附加产品总价
xsell_summary AS (
    SELECT 
        client_id,
        matched_main_date,
        SUM(xsell_price) AS total_xsell_price
    FROM t2_matched
    GROUP BY client_id, matched_main_date
)
-- 第三步：将汇总后的附加价格关联回主交易表
SELECT 
    t1.*,
    xsell_summary.total_xsell_price AS xsell_price
FROM t1
LEFT JOIN xsell_summary
    ON t1.client_id = xsell_summary.client_id
    AND t1.transaction_date = xsell_summary.matched_main_date
ORDER BY t1.client_id, t1.transaction_date;

逻辑说明 #

1. t2_matched CTE：为每一条附加产品交易，关联所有符合10天时间窗的主交易记录，然后通过FIRST_VALUE窗口函数筛选出该附加产品对应的最早主交易日期，确保每个附加产品只匹配到最早的符合条件的主交易。
2. xsell_summary CTE：按客户和匹配的主交易日期分组，汇总该主交易对应的所有附加产品总价，解决同一客户多次附加产品的汇总问题。
3. 最后将汇总结果关联回t1表，得到最终的匹配结果，未匹配到附加产品的主交易记录xsell_price为null。

注：代码中使用INTERVAL '10 days'是标准SQL写法，若使用MySQL等数据库，可替换为DATE_ADD(t1.transaction_date, INTERVAL -10 DAY)和DATE_ADD(t1.transaction_date, INTERVAL 10 DAY)。

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