如何计算客户订单间平均间隔天数？MySQL技术求助

你用的第二个参考SQL逻辑有问题：它通过(MAX(order_date) - MIN(order_date)) / (COUNT(*) - 1)计算平均间隔，本质是把订单的总时间跨度除以间隔数，这只有在订单完全均匀分布时才勉强适用。如果订单间隔差异大（比如两笔订单隔了3年），这个算法会完全失真——它算的是总跨度的“平均”，不是实际相邻订单间隔的平均。

下面是正确的解决方案，分两种场景：

场景1：仅计算订单之间的平均间隔 #

用MySQL的LAG()窗口函数，快速获取每个客户的上一笔订单日期，计算相邻订单的间隔天数，最后对每个客户的所有间隔求平均：

WITH customer_order_lag AS (
    SELECT
        customer_id,
        customer_name,
        order_date,
        -- 按客户分组、订单日期排序，取出上一笔订单的日期
        LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
    FROM orders
),
order_intervals AS (
    SELECT
        customer_id,
        customer_name,
        -- 计算当前订单与上一笔的间隔天数
        DATEDIFF(order_date, prev_order_date) AS interval_days
    FROM customer_order_lag
    WHERE prev_order_date IS NOT NULL -- 排除每个客户的第一笔订单（没有前置订单）
)
SELECT
    customer_id,
    customer_name,
    ROUND(AVG(interval_days), 2) AS average_days_between_orders
FROM order_intervals
GROUP BY customer_id, customer_name;

场景2：纳入截止日期2018-01-01的间隔 #

如果需要把最后一笔订单到截止日的间隔也计入平均（比如客户最后下单后到截止日的等待时间也算作一个间隔），可以在上面的基础上，额外计算这个间隔并合并：

WITH customer_order_lag AS (
    SELECT
        customer_id,
        customer_name,
        order_date,
        LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
    FROM orders
),
order_intervals AS (
    SELECT
        customer_id,
        customer_name,
        DATEDIFF(order_date, prev_order_date) AS interval_days
    FROM customer_order_lag
    WHERE prev_order_date IS NOT NULL
),
-- 计算每个客户最后一笔订单到截止日的间隔
cutoff_intervals AS (
    SELECT
        customer_id,
        customer_name,
        DATEDIFF('2018-01-01', MAX(order_date)) AS interval_days
    FROM orders
    GROUP BY customer_id, customer_name
    -- 只有最后一笔订单早于截止日时才计入
    HAVING MAX(order_date) < '2018-01-01'
),
-- 合并所有间隔数据
all_intervals AS (
    SELECT customer_id, customer_name, interval_days FROM order_intervals
    UNION ALL
    SELECT customer_id, customer_name, interval_days FROM cutoff_intervals
)
SELECT
    customer_id,
    customer_name,
    ROUND(AVG(interval_days), 2) AS average_days_between_orders
FROM all_intervals
GROUP BY customer_id, customer_name
-- 单独处理只有1笔订单的客户
UNION ALL
SELECT
    customer_id,
    customer_name,
    DATEDIFF('2018-01-01', order_date) AS average_days_between_orders
FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) = 1
) AND order_date < '2018-01-01'
UNION ALL
SELECT
    customer_id,
    customer_name,
    NULL AS average_days_between_orders
FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) = 1
) AND order_date >= '2018-01-01';

要点说明： #

• LAG()是核心：它能高效获取同一客户的上一笔订单日期，完全不需要递归查询。
• DATEDIFF()在MySQL中返回两个日期的天数差（注意参数顺序：DATEDIFF(晚日期, 早日期)得到正数）。
• 对于仅下单1次的客户：如果订单早于截止日，平均间隔就是到截止日的天数；如果订单在截止日或之后，没有后续间隔，返回NULL。

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