如何用SQL计算Life Time Value(LTV)？附数据表及预期结果

现有数据表t_pay_info结构及数据 #

期望统计结果格式 #

实现方向指导 #

1. 标记用户首次支付的基准日期 #

先通过聚合获取每个用户在对应渠道下的首次支付日期，这是计算nday（距首次支付天数）的核心基准：

WITH user_first_pay AS (
    SELECT 
        user,
        channel,
        MIN(date) AS first_pay_date
    FROM t_pay_info
    GROUP BY user, channel
)

2. 关联支付数据计算nday #

将原支付表与首次支付信息关联，为每条支付记录计算对应的nday：

WITH user_first_pay AS (
    SELECT 
        user,
        channel,
        MIN(date) AS first_pay_date
    FROM t_pay_info
    GROUP BY user, channel
),
pay_with_nday AS (
    SELECT 
        u.first_pay_date AS date,
        u.channel,
        DATEDIFF(p.date, u.first_pay_date) AS nday,
        p.amount,
        p.id,
        p.user
    FROM t_pay_info p
    JOIN user_first_pay u ON p.user = u.user AND p.channel = u.channel
)

3. 按维度聚合并计算累计值 #

针对(date, channel, nday)维度聚合基础指标，再通过窗口函数计算累计金额和累计支付记录数：

WITH user_first_pay AS (
    SELECT 
        user,
        channel,
        MIN(date) AS first_pay_date
    FROM t_pay_info
    GROUP BY user, channel
),
pay_with_nday AS (
    SELECT 
        u.first_pay_date AS date,
        u.channel,
        DATEDIFF(p.date, u.first_pay_date) AS nday,
        p.amount,
        p.id,
        p.user
    FROM t_pay_info p
    JOIN user_first_pay u ON p.user = u.user AND p.channel = u.channel
),
daily_agg AS (
    SELECT 
        date,
        channel,
        nday,
        SUM(amount) AS amount,
        COUNT(DISTINCT user) AS number,
        COUNT(id) AS pay_count
    FROM pay_with_nday
    GROUP BY date, channel, nday
)
SELECT 
    date,
    channel,
    nday,
    amount,
    number,
    SUM(amount) OVER(PARTITION BY date, channel ORDER BY nday) AS accrued_amount,
    SUM(pay_count) OVER(PARTITION BY date, channel ORDER BY nday) AS accrued_pay_number
FROM daily_agg
ORDER BY date, channel, nday;

4. 补充无支付的nday空记录 #

如果需要展示示例中nday=1这类无支付的记录，需生成连续nday序列并左关联聚合结果，确保每个基准日期和渠道下的nday连续：

WITH recursive nday_series AS (
    SELECT 0 AS nday
    UNION ALL
    SELECT nday + 1 FROM nday_series WHERE nday < 30 -- 可按需调整最大统计天数
),
user_first_pay AS (
    SELECT 
        user,
        channel,
        MIN(date) AS first_pay_date
    FROM t_pay_info
    GROUP BY user, channel
),
pay_with_nday AS (
    SELECT 
        u.first_pay_date AS date,
        u.channel,
        DATEDIFF(p.date, u.first_pay_date) AS nday,
        p.amount,
        p.id,
        p.user
    FROM t_pay_info p
    JOIN user_first_pay u ON p.user = u.user AND p.channel = u.channel
),
daily_agg AS (
    SELECT 
        base.date,
        base.channel,
        s.nday,
        COALESCE(SUM(p.amount), 0) AS amount,
        COALESCE(COUNT(DISTINCT p.user), 0) AS number,
        COALESCE(COUNT(p.id), 0) AS pay_count
    FROM nday_series s
    CROSS JOIN (SELECT DISTINCT first_pay_date AS date, channel FROM user_first_pay) base
    LEFT JOIN pay_with_nday p ON base.date = p.date AND base.channel = p.channel AND s.nday = p.nday
    GROUP BY base.date, base.channel, s.nday
)
SELECT 
    date,
    channel,
    nday,
    amount,
    number,
    SUM(amount) OVER(PARTITION BY date, channel ORDER BY nday) AS accrued_amount,
    SUM(pay_count) OVER(PARTITION BY date, channel ORDER BY nday) AS accrued_pay_number
FROM daily_agg
ORDER BY date, channel, nday;

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