如何用Vertica SQL计算客户入会后按30天递增的复购周期数据

表结构说明 #

假设两张表的结构如下：

• customer_onboarding（T1）：存储客户入会信息，包含customer_id（唯一客户ID）、onboard_date（入会日期）
• customer_activity（T2）：存储客户活动/购买记录，包含customer_id（关联客户ID）、activity_date（活动/购买日期）

实现SQL #

以下SQL将按入会年月分组，统计当月新增客户数，以及入会30天后每个30天区间内的活跃客户数（每个区间间隔严格超过30天）：

SELECT
  TO_CHAR(o.onboard_date, 'fmMonYYYY') AS "Month-Year",
  COUNT(DISTINCT o.customer_id) AS Total_Acquired_Customer,
  COALESCE(COUNT(DISTINCT CASE WHEN a.activity_date > o.onboard_date + INTERVAL '30 days' 
                                AND a.activity_date <= o.onboard_date + INTERVAL '60 days' 
                                THEN o.customer_id END), 0) AS "Month1 Activity",
  COALESCE(COUNT(DISTINCT CASE WHEN a.activity_date > o.onboard_date + INTERVAL '60 days' 
                                AND a.activity_date <= o.onboard_date + INTERVAL '90 days' 
                                THEN o.customer_id END), 0) AS "Month2 Activity",
  COALESCE(COUNT(DISTINCT CASE WHEN a.activity_date > o.onboard_date + INTERVAL '90 days' 
                                AND a.activity_date <= o.onboard_date + INTERVAL '120 days' 
                                THEN o.customer_id END), 0) AS "Month3 Activity",
  COALESCE(COUNT(DISTINCT CASE WHEN a.activity_date > o.onboard_date + INTERVAL '120 days' 
                                AND a.activity_date <= o.onboard_date + INTERVAL '150 days' 
                                THEN o.customer_id END), 0) AS "Month4 Activity"
FROM customer_onboarding o
LEFT JOIN customer_activity a 
  ON o.customer_id = a.customer_id
GROUP BY TO_CHAR(o.onboard_date, 'fmMonYYYY')
ORDER BY MIN(o.onboard_date);

逻辑说明 #

1. 年月格式转换：用TO_CHAR(o.onboard_date, 'fmMonYYYY')生成jan2020格式的年月标识，fm修饰符用于去除默认的空格填充。
2. 新增客户统计：COUNT(DISTINCT o.customer_id)统计每个入会月的新增客户总数，确保去重。
3. 区间活跃客户统计：
   • Month1 Activity：统计入会日30天后到60天内有活动的客户数
   • Month2 Activity：统计入会日60天后到90天内有活动的客户数
   • 以此类推，每个区间为连续30天，严格符合“超过30天递增”的要求
   • COALESCE将无活动的NULL值转为0，结果更直观
4. 关联逻辑：使用LEFT JOIN确保即使客户无任何活动，也会被计入新增客户总数。

调整说明 #

• 如果需要统计订单数而非活跃客户数，去掉每个COUNT里的DISTINCT即可。
• 如果需要调整区间逻辑（比如将Month1定义为入会日30天后的所有活动），移除对应CASE语句中的AND a.activity_date <= ...条件。

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