基于Presto实现电商用户7日留存率统计的技术咨询

看起来你已经搭好了核心的关联思路，我来帮你把代码补全，完美实现你的需求——统计每日（Day0）下单用户中，在Day1到Day7期间再次购物的用户数：

SELECT 
    bp1.purchase_date AS day0,
    COUNT(DISTINCT bp1.customer_ID) AS total_day0_customers, -- 当日下单的总用户量
    COUNT(DISTINCT bp2.customer_ID) AS retained_customers, -- 7日内复购的用户量
    ROUND(COUNT(DISTINCT bp2.customer_ID) / NULLIF(COUNT(DISTINCT bp1.customer_ID), 0), 4) AS retention_rate -- 可选：直接算出留存率
FROM business bp1
LEFT JOIN business bp2 
    ON bp1.customer_ID = bp2.customer_ID
    AND DATE_DIFF('day', bp1.purchase_date, bp2.purchase_date) BETWEEN 1 AND 7
GROUP BY bp1.purchase_date
ORDER BY bp1.purchase_date;

关键逻辑说明： #

• 用LEFT JOIN替代隐式内连接：这样哪怕某天没有任何用户留存，也能保留当天的记录（留存数为0），不会漏掉日期维度
• DATE_DIFF('day', bp1.purchase_date, bp2.purchase_date)精准计算两次下单的天数差，限定在1到7之间，正好匹配Day1到Day7的复购范围
• 额外加了total_day0_customers和retention_rate列：前者是当日总下单用户数，后者直接算出留存率（用NULLIF避免除数为0的报错）
• 如果你的purchase_date是带时分秒的时间戳类型，记得把所有purchase_date替换成DATE(purchase_date)，确保按自然日统计，不会被时间细节干扰

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