如何计算按客户分组的最近3笔订单平均间隔天数及订单量总和

我来帮你搞定这个需求！要实现按客户分组计算最近3笔订单的平均间隔天数和订单量总和，我们可以借助窗口函数来精准处理，具体步骤和代码如下：

思路拆解 #

1. 筛选最近3笔订单：给每个客户的订单按日期倒序排名，只保留排名前3的记录（最新的3笔）。
2. 计算相邻订单的日期差：对筛选后的订单，获取每笔订单的上一笔订单日期，用DATEDIFF()计算间隔天数。
3. 聚合统计结果：按客户分组，计算间隔天数的平均值，同时求和这3笔订单的总量。

完整SQL代码 #

WITH ranked_orders AS (
    -- 给每个客户的订单按日期倒序排名，标记最近3笔
    SELECT 
        customerid,
        orderdate,
        orderqty,
        ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY orderdate DESC) AS rn
    FROM Orders
),
orders_with_prev_date AS (
    -- 获取每笔订单的上一笔订单日期
    SELECT 
        customerid,
        orderdate,
        orderqty,
        LAG(orderdate) OVER (PARTITION BY customerid ORDER BY orderdate DESC) AS prev_orderdate
    FROM ranked_orders
    WHERE rn <= 3 -- 只保留最近3笔订单
)
SELECT 
    customerid,
    -- 计算间隔天数的平均值，取整数
    ROUND(AVG(DATEDIFF(orderdate, prev_orderdate)), 0) AS `last 3 orders average days between orders`,
    -- 计算最近3笔订单的总量
    SUM(orderqty) AS `sum of orderqty for last 3 order`
FROM orders_with_prev_date
GROUP BY customerid;

代码说明 #

• ROW_NUMBER()：按客户分组，订单日期降序排列，给每个客户的订单编号，确保我们只拿到最新的3笔。
• LAG()：用于获取同一客户中，当前订单的上一笔（更早的）订单日期，这样就能计算相邻订单的间隔。
• DATEDIFF()：计算两个日期之间的天数差，这里用当前订单日期减去上一笔订单日期，得到间隔天数。
• AVG()：对所有有效的间隔天数求平均（最新的订单没有上一笔，会自动被忽略），用ROUND()取整和示例结果一致。
• SUM()：直接对筛选出的3笔订单的数量求和。

运行这段代码后，就能得到你期望的结果：

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