SnowflakeSQL如何计算用户注册月数并按注册月份分群统计收入

思路有效性确认 #

你的实现逻辑完全符合用户群组分析（Cohort Analysis）的标准处理流程，无需调整，直接按两步落地即可：先计算活跃日与注册日的间隔月数，再按注册月划分用户群，最终聚合不同群组在不同生命周期阶段的收入数据。

步骤1：新增months列计算日期间隔月数 #

Snowflake 内置 DATEDIFF 函数可直接满足需求，无需自定义逻辑，计算规则和你给出的示例预期完全匹配：

SELECT
    ID,
    date,
    start_date,
    amount_paid,
    last_amount_paid,
    field,
    -- 单位指定为month，传入起始日期start_date、结束日期date即可
    DATEDIFF('month', start_date, date) AS months
FROM 你的业务数据表名;

计算逻辑说明：DATEDIFF 计算月间隔时直接取两个日期的月份数值差，例如2019-11-06到2020-08-01的差值为 (2020*12+8) - (2019*12+11) =9，和你给出的示例结果完全一致。

步骤2：按注册月分群实现收入追踪 #

如果需要输出最终的群组收入分析表，可直接用以下SQL完成：

WITH cohort_base AS (
    -- 第一步：为每个用户绑定注册月群组标签，同时计算生命周期月
    SELECT
        *,
        DATEDIFF('month', start_date, date) AS lifecycle_month,
        -- 截断注册日期到月份，作为群组唯一标识
        DATE_TRUNC('month', start_date) AS register_cohort_month
    FROM 你的业务数据表名
)
-- 第二步：按群组和生命周期月聚合收入指标
SELECT
    register_cohort_month AS 注册月份,
    lifecycle_month AS 注册后第N月,
    COUNT(DISTINCT ID) AS 活跃用户数,
    SUM(amount_paid) AS 当月总收入,
    SUM(amount_paid)/COUNT(DISTINCT ID) AS 当月ARPU
FROM cohort_base
GROUP BY 1,2
ORDER BY 1,2;

结果说明 #

最终输出的结果每行对应「某月份注册的用户群，在注册后第N个月的收入、活跃、ARPU表现」，可以直接用于群组趋势分析、留存/收入变化追踪。

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