如何编写SQL查询计算客户首次与第二次购买间隔的平均天数

通用方案（支持MySQL 8.0+/PostgreSQL/SQL Server/Oracle等所有支持窗口函数的数据库） #

核心逻辑是先给每个客户的购买记录按时间排序，筛选出首次和第二次购买记录后计算间隔，再求平均值：

WITH ranked_purchases AS (
    SELECT
        customer_id,
        `timestamp` AS purchase_time,
        -- 按客户分组，购买时间升序排序得到购买顺序
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY `timestamp` ASC) AS purchase_order
    FROM Customer_purchases
)
SELECT
    -- 计算间隔天数的平均值，不同数据库的日期差函数可按需替换
    AVG(DATEDIFF(p2.purchase_time, p1.purchase_time)) AS average_interval_days
FROM ranked_purchases p1
INNER JOIN ranked_purchases p2
    ON p1.customer_id = p2.customer_id
    AND p1.purchase_order = 1 -- 首次购买
    AND p2.purchase_order = 2; -- 第二次购买

不同数据库的日期差函数适配 #

• PostgreSQL：替换DATEDIFF(p2.purchase_time, p1.purchase_time)为(p2.purchase_time::DATE - p1.purchase_time::DATE)
• Oracle：替换为TRUNC(p2.purchase_time) - TRUNC(p1.purchase_time)
• SQL Server：替换为DATEDIFF(day, p1.purchase_time, p2.purchase_time)

旧版MySQL（5.7及以下，不支持窗口函数）兼容方案 #

用子查询分别取每个客户的首次和第二次购买时间：

SELECT
    AVG(DATEDIFF(second_purchase, first_purchase)) AS average_interval_days
FROM (
    SELECT
        customer_id,
        MIN(`timestamp`) AS first_purchase,
        -- 子查询取大于首次购买时间的最早购买记录，即第二次购买
        (
            SELECT MIN(`timestamp`)
            FROM Customer_purchases p2
            WHERE p2.customer_id = p1.customer_id
            AND p2.`timestamp` > p1.first_purchase
        ) AS second_purchase
    FROM Customer_purchases p1
    GROUP BY customer_id
) customer_purchase_times
-- 过滤只有单次购买的客户，不纳入平均值计算
WHERE second_purchase IS NOT NULL;

常见场景调整说明 #

• 如果需要把同一天的多笔购买合并为一次购买，将ROW_NUMBER()替换为DENSE_RANK()，并且排序时对timestamp按日期截断，例如ORDER BY DATE(timestamp) ASC
• 如果需要将只有单次购买的客户纳入统计，间隔按0天计算，可将INNER JOIN改为LEFT JOIN，并用IFNULL对空值做替换处理

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