如何将free_activity数据合并到paid_activity衍生的休眠表中？

要构建与paid_activity行一一对应的dormancy表，核心是以已有的paid_dormancy中间表为主表，通过左连接（LEFT JOIN）关联free_activity的聚合计算结果，既能保留主表所有行，又能补充免费活动的休眠字段。

具体实现步骤 #

假设两张表的关联键为user_id（如果是其他维度，比如活动ID，替换成对应字段即可），以下是可直接复用的SQL逻辑：

1. 先计算免费活动的休眠字段
   用CTE（公共表表达式）对free_activity按用户分组，计算所需的两个休眠指标：

WITH free_dormancy_stats AS (
    SELECT
        user_id,
        -- 计算免费活动整体休眠天数（最后一次活动距当前的天数）
        DATEDIFF(CURRENT_DATE, MAX(activity_timestamp)) AS free_dormancy,
        -- 计算免费产品相关活动的休眠天数（仅统计产品类活动的最后一次时间）
        DATEDIFF(
            CURRENT_DATE,
            MAX(CASE WHEN activity_type = 'product' THEN activity_timestamp ELSE NULL END)
        ) AS free_product_dormancy
    FROM free_activity
    GROUP BY user_id
)

2. 合并paid与free的休眠数据
   以paid_dormancy为主表，左连接上面的CTE，确保主表每一行都被保留，无对应免费数据时用默认值填充：

SELECT
    pd.user_id,
    pd.paid_dormancy,
    pd.paid_product_dormancy,
    -- 无免费活动数据时用999（或业务约定的默认值）表示永久休眠
    COALESCE(fds.free_dormancy, 999) AS free_dormancy,
    COALESCE(fds.free_product_dormancy, 999) AS free_product_dormancy
FROM paid_dormancy pd
LEFT JOIN free_dormancy_stats fds 
    ON pd.user_id = fds.user_id;

关键注意事项 #

• 避免行数膨胀：必须先对free_activity做聚合再关联，不能直接用paid_dormancy连原始free_activity，否则会因为一个用户多条免费活动记录导致主表行被重复。
• 关联键准确性：如果你的业务是按单条活动而非用户维度对应，需要将关联键换成activity_id或其他能唯一匹配paid_activity与free_activity的字段。
• NULL值处理：用COALESCE替换NULL为业务认可的默认值，比如从未有免费活动时设为999或0，根据需求调整。

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