如何计算从订阅到首次付款的平均时间

2026-10-24支付老李

结论

这个需求需要分两层计算：先按 user_id 使用 MIN() 取得每位 subscriber 的首次付款时间，再按 category 使用 AVG() 计算平均间隔。

SELECT
    s.category,
    AVG(
        TIMESTAMPDIFF(
            HOUR,
            s.`timestamp`,
            fp.first_payment_at
        )
    ) AS avg_hours_to_first_payment
FROM subscribers AS s
JOIN (
    SELECT
        user_id,
        MIN(`timestamp`) AS first_payment_at
    FROM payments
    GROUP BY user_id
) AS fp
    ON fp.user_id = s.user_id
GROUP BY s.category;

如果实际表名是 subs，把查询中的 subscribers 替换为 subs 即可。

为什么原查询会报错

原查询直接计算了：

AVG(TIMESTAMPDIFF(HOUR, subs.timestamp, MIN(payments.timestamp)))

这相当于把聚合函数 MIN() 嵌套在另一个聚合函数 AVG() 中。MySQL 无法在同一聚合层级中确定：

MIN() 应针对哪位 subscriber 计算；
AVG() 应对 category 中的哪些结果求平均。

这个需求本身包含两个统计层级：

在 subscriber 层级取得每位用户最早的付款时间；
在 category 层级计算这些用户时间差的平均值。

因此，需要通过子查询、CTE 或其他方式分开完成这两层计算。

另一种写法：先计算每位 subscriber 的时间差

也可以在内层查询中直接计算每位 subscriber 到首次付款的小时数，再由外层查询求平均值：

SELECT
    category,
    AVG(hours_to_first_payment) AS avg_hours_to_first_payment
FROM (
    SELECT
        s.user_id,
        s.category,
        TIMESTAMPDIFF(
            HOUR,
            s.`timestamp`,
            MIN(p.`timestamp`)
        ) AS hours_to_first_payment
    FROM subscribers AS s
    JOIN payments AS p
        ON p.user_id = s.user_id
    GROUP BY
        s.user_id,
        s.category,
        s.`timestamp`
) AS subscriber_first_payments
GROUP BY category;

这种写法的计算过程更直观：内层查询为每位 subscriber 生成一行，外层再按 category 汇总。

需要注意的细节

没有付款记录的 subscriber

以上查询使用 INNER JOIN，所以从未付款的 subscriber 不会参与平均值计算。通常，这符合“从订阅到首次付款”的统计口径。

如果需要保留未付款用户，可以改用 LEFT JOIN。由于这些用户没有首次付款时间，TIMESTAMPDIFF() 会返回 NULL，而 AVG() 默认忽略 NULL，因此平均值仍只基于已付款用户计算。

整数小时与小数小时

TIMESTAMPDIFF(HOUR, ...) 返回完整小时数，不足一小时的部分会被截断。如果需要更精确的小数小时，可以先计算秒数，再除以 3600.0：

SELECT
    s.category,
    AVG(
        TIMESTAMPDIFF(
            SECOND,
            s.`timestamp`,
            fp.first_payment_at
        ) / 3600.0
    ) AS avg_hours_to_first_payment
FROM subscribers AS s
JOIN (
    SELECT
        user_id,
        MIN(`timestamp`) AS first_payment_at
    FROM payments
    GROUP BY user_id
) AS fp
    ON fp.user_id = s.user_id
GROUP BY s.category;

这样，1 小时 30 分钟会以 1.5 小时参与平均，而不是被记为 1 小时。

时间字段名称

timestamp 容易与 SQL 数据类型或关键字混淆，因此示例中使用了反引号：

s.`timestamp`

如果条件允许，可以改用含义更明确的字段名，例如：

subscribed_at
paid_at

还要确认数据中没有付款时间早于订阅时间的情况，否则计算结果可能出现负数。