SQL：会员复购率

-- 方案1使用LAG窗口函数推荐 WITH user_purchase AS ( -- 1. 先去重同一天多次购买只算一次 SELECT vip, doc_date FROM ods.ods_t_lsdjb WHERE vip IS NOT NULL GROUP BY vip, doc_date ), user_purchase_with_prev AS ( -- 2. 用LAG取上一次购买日期 SELECT vip, doc_date, LAG(doc_date) OVER (PARTITION BY vip ORDER BY doc_date) AS prev_doc_date, -- 计算与上一次购买的天数差 DATEDIFF( doc_date, LAG(doc_date) OVER (PARTITION BY vip ORDER BY doc_date) ) AS days_diff FROM user_purchase ) -- 3. 统计复购情况 SELECT vip, COUNT(*) AS total_purchase_days, -- 总购买天数 SUM(CASE WHEN days_diff 365 THEN 1 ELSE 0 END) AS repurchase_count, -- 一年内复购次数 -- 是否算复购用户只要有任意一次间隔365天 CASE WHEN MAX(CASE WHEN days_diff 365 THEN 1 ELSE 0 END) 1 THEN 是 ELSE 否 END AS is_repurchase_user, -- 平均复购间隔只算一年内的 AVG(CASE WHEN days_diff 365 THEN days_diff END) AS avg_repurchase_interval FROM user_purchase_with_prev WHERE prev_doc_date IS NOT NULL -- 过滤掉首次购买 GROUP BY vip ORDER BY repurchase_count DESC;