按日期及客户维度计算复购间隔时长的技术实现需求

一、需求核心拆解 #

需要实现两类时间间隔计算，且支持灵活切换days/weeks/months单位：

• 复购间隔：按Customer Name（整体）或Customer_Name_Sub（子维度）分组，计算同一组内相邻订单的时间差平均值
• 最后一次订单距今日时长：按上述维度分组，取每组最新订单日期与当前时间的间隔

二、订单数据表格 #

三、SQL实现方案 #

1. 复购间隔计算（支持单位切换） #

核心逻辑：用LAG()窗口函数获取同一分组的上一次订单日期，计算时间差后取平均值，通过变量统一控制单位。

WITH ordered_orders AS (
    SELECT
        `Customer Name`,
        Customer_Name_Sub,
        -- 日期转换函数根据SQL引擎调整：MySQL用STR_TO_DATE，SQL Server用CONVERT，BigQuery用PARSE_DATE
        PARSE_DATE('%m/%d/%Y', `Order Date`) AS order_date,
        LAG(PARSE_DATE('%m/%d/%Y', `Order Date`)) OVER (
            PARTITION BY `Customer Name`, Customer_Name_Sub 
            ORDER BY PARSE_DATE('%m/%d/%Y', `Order Date`)
        ) AS prev_order_date
    FROM your_order_table
),
interval_cal AS (
    SELECT
        `Customer Name`,
        Customer_Name_Sub,
        -- 此处修改值即可切换单位：'day'/'week'/'month'
        'week' AS interval_unit,
        CASE 
            WHEN interval_unit = 'day' THEN DATEDIFF(order_date, prev_order_date)
            WHEN interval_unit = 'week' THEN DATEDIFF(order_date, prev_order_date) / 7
            WHEN interval_unit = 'month' THEN DATEDIFF(order_date, prev_order_date) / 30.4375 -- 平均月天数
        END AS repurchase_interval
    FROM ordered_orders
    WHERE prev_order_date IS NOT NULL -- 过滤首次订单（无复购间隔）
)
-- 按维度计算平均复购间隔
SELECT
    `Customer Name`,
    Customer_Name_Sub,
    interval_unit,
    ROUND(AVG(repurchase_interval), 2) AS avg_repurchase_interval
FROM interval_cal
GROUP BY `Customer Name`, Customer_Name_Sub, interval_unit
ORDER BY `Customer Name`, Customer_Name_Sub;

2. 最后一次订单距今日时长计算 #

核心逻辑：按分组取最大订单日期，与当前时间计算间隔，同样支持单位切换。

WITH latest_orders AS (
    SELECT
        `Customer Name`,
        Customer_Name_Sub,
        MAX(PARSE_DATE('%m/%d/%Y', `Order Date`)) AS last_order_date
    FROM your_order_table
    GROUP BY `Customer Name`, Customer_Name_Sub
)
SELECT
    `Customer Name`,
    Customer_Name_Sub,
    -- 修改此处切换单位
    'week' AS interval_unit,
    ROUND(CASE 
        WHEN interval_unit = 'day' THEN DATEDIFF(CURRENT_DATE(), last_order_date)
        WHEN interval_unit = 'week' THEN DATEDIFF(CURRENT_DATE(), last_order_date) / 7
        WHEN interval_unit = 'month' THEN DATEDIFF(CURRENT_DATE(), last_order_date) / 30.4375
    END, 2) AS days_since_last_order
FROM latest_orders
ORDER BY `Customer Name`, Customer_Name_Sub;

3. 合并两类计算结果（可选） #

如果需要同时展示平均复购间隔和最后一次时长，可通过JOIN合并两个结果集：

WITH ordered_orders AS (
    SELECT
        `Customer Name`,
        Customer_Name_Sub,
        PARSE_DATE('%m/%d/%Y', `Order Date`) AS order_date,
        LAG(PARSE_DATE('%m/%d/%Y', `Order Date`)) OVER (
            PARTITION BY `Customer Name`, Customer_Name_Sub 
            ORDER BY PARSE_DATE('%m/%d/%Y', `Order Date`)
        ) AS prev_order_date
    FROM your_order_table
),
avg_repurchase AS (
    SELECT
        `Customer Name`,
        Customer_Name_Sub,
        'week' AS interval_unit,
        ROUND(AVG(CASE 
            WHEN 'week' = 'day' THEN DATEDIFF(order_date, prev_order_date)
            WHEN 'week' = 'week' THEN DATEDIFF(order_date, prev_order_date) / 7
            WHEN 'week' = 'month' THEN DATEDIFF(order_date, prev_order_date) / 30.4375
        END), 2) AS avg_repurchase_interval
    FROM ordered_orders
    WHERE prev_order_date IS NOT NULL
    GROUP BY `Customer Name`, Customer_Name_Sub
),
last_order_interval AS (
    SELECT
        `Customer Name`,
        Customer_Name_Sub,
        'week' AS interval_unit,
        ROUND(CASE 
            WHEN 'week' = 'day' THEN DATEDIFF(CURRENT_DATE(), last_order_date)
            WHEN 'week' = 'week' THEN DATEDIFF(CURRENT_DATE(), last_order_date) / 7
            WHEN 'week' = 'month' THEN DATEDIFF(CURRENT_DATE(), last_order_date) / 30.4375
        END, 2) AS days_since_last_order
    FROM (
        SELECT
            `Customer Name`,
            Customer_Name_Sub,
            MAX(PARSE_DATE('%m/%d/%Y', `Order Date`)) AS last_order_date
        FROM your_order_table
        GROUP BY `Customer Name`, Customer_Name_Sub
    ) t
)
SELECT
    ar.`Customer Name`,
    ar.Customer_Name_Sub,
    ar.interval_unit,
    ar.avg_repurchase_interval,
    loi.days_since_last_order
FROM avg_repurchase ar
JOIN last_order_interval loi
ON ar.`Customer Name` = loi.`Customer Name` 
AND ar.Customer_Name_Sub = loi.Customer_Name_Sub
AND ar.interval_unit = loi.interval_unit
ORDER BY ar.`Customer Name`, ar.Customer_Name_Sub;

四、关键注意事项 #

• 日期转换适配：根据使用的SQL引擎（MySQL/BigQuery/SQL Server等）调整日期转换函数，确保字符串日期能正确转为日期类型
• 维度灵活调整：如果需要计算客户整体（不区分子维度），只需移除PARTITION BY和GROUP BY中的Customer_Name_Sub字段
• 单位切换简化：所有计算的单位由单个变量控制，修改一处即可全局切换

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