按月统计唯一客户累计总数的技术实现咨询

问题描述 #

需要按cohortMonth（购买月份）统计截至该月份的累计唯一客户数量。输入表包含customer_email和cohortMonth字段，示例数据如下：

预期输出为各月份的累计唯一客户数：

解决方案（SQL实现） #

核心思路是先确定每个客户的首次购买月份，再按时间顺序统计截至每个月份的所有首次购买客户总数。

完整SQL代码 #

-- 第一步：获取每个客户的首次购买月份
WITH first_purchase AS (
    SELECT 
        customer_email,
        MIN(cohortMonth) AS first_cohort
    FROM your_table_name  -- 替换为你的实际表名
    GROUP BY customer_email
),
-- 第二步：提取所有不重复的购买月份并按时间排序
unique_months AS (
    SELECT DISTINCT cohortMonth 
    FROM your_table_name
    ORDER BY STR_TO_DATE(cohortMonth, '%m/%Y')
)
-- 第三步：计算每个月份的累计唯一客户数
SELECT 
    um.cohortMonth,
    COUNT(fp.customer_email) AS cumulative_total_customers
FROM unique_months um
LEFT JOIN first_purchase fp 
    ON STR_TO_DATE(fp.first_cohort, '%m/%Y') <= STR_TO_DATE(um.cohortMonth, '%m/%Y')
GROUP BY um.cohortMonth
ORDER BY STR_TO_DATE(um.cohortMonth, '%m/%Y');

代码逻辑说明 #

1. first_purchase CTE：通过MIN(cohortMonth)获取每个客户的首次购买月份，确保同一客户只被统计一次。
2. unique_months CTE：提取所有不重复的购买月份，并通过STR_TO_DATE将月份字符串转为日期格式，保证排序的正确性。
3. 累计统计：将每个月份与首次购买月份做左连接，统计所有首次购买时间早于等于当前月份的客户数量，得到累计值。

注意事项 #

• 如果你的数据库不支持STR_TO_DATE（比如SQL Server），可以替换为对应日期转换函数，例如CONVERT(DATE, cohortMonth, 101)。
• 确保cohortMonth的格式统一（如mm/yyyy），避免日期转换错误。

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