PostgreSQL中计算客户各次订单平均价值及订单间隔平均天数

我有一张记录商店客户订单的orders表，数据如下：

SELECT * FROM orders;

需求说明 #

需要在2022-01-01至2022-12-31的时间范围内完成以下计算：

• 第1至第4次订单的平均订单价值（AOV）
• 第1到2次、第2到3次、第3到4次订单的平均间隔天数

最终结果需符合如下格式：

注意：第1次订单的平均间隔天数固定为0，因为这是首次购买。

解决方案 #

通过窗口函数先对每个客户的订单按时间排序并标记订单次数，同时计算订单间隔天数，再按订单次数分组聚合得到最终结果。

完整SQL代码

WITH customer_order_sequence AS (
    SELECT
        customer_id,
        value,
        created_at,
        -- 按客户分组，按订单创建时间排序，标记订单次数
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS order_number,
        -- 计算当前订单与上一次订单的间隔天数
        created_at - LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS days_since_last_order
    FROM orders
    WHERE created_at BETWEEN '2022-01-01' AND '2022-12-31'
),
order_metrics AS (
    SELECT
        order_number,
        -- 计算平均订单价值，保留2位小数
        ROUND(AVG(value), 2) AS AOV,
        -- 计算平均间隔天数，首次订单设为0
        ROUND(AVG(COALESCE(days_since_last_order, 0)), 0) AS av_days_since_last_order
    FROM customer_order_sequence
    WHERE order_number <= 4 -- 只取前4次订单
    GROUP BY order_number
    ORDER BY order_number
)
SELECT * FROM order_metrics;

代码解释

1. customer_order_sequence CTE：

   • 用ROW_NUMBER()窗口函数按customer_id分组、created_at升序排序，给每个客户的订单标记顺序号order_number。
   • 用LAG(created_at)获取同一客户的上一次订单时间，计算当前订单与上一次的间隔天数days_since_last_order。
   • 过滤出指定时间范围内的订单。

2. order_metrics CTE：

   • 按order_number分组，计算每组的平均订单价值并保留2位小数。
   • 用COALESCE将首次订单的间隔天数设为0，再计算平均间隔天数并取整。
   • 只保留前4次订单的结果，按订单次数排序。

结果说明

注意需求指定的是2022年全年的订单，原示例中customer_id1的order_id8（2020年）不在统计范围内，2022年该客户的首次订单为order_id7（2022-01-16）。执行SQL后得到的结果会基于指定时间范围内的订单数据计算，例如第1次订单AOV为(200.00 + 386.72 + 357.80)/3 = 314.84，后续的平均间隔天数也会根据2022年内的订单时间差计算得出。

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