PostgreSQL如何计算不同合作伙伴的平均采购周期？

现有一张存储合作伙伴及其订单日期的PostgreSQL表，数据如下：

需要获取每个合作伙伴的平均采购周期，预期结果如下：

计算逻辑：

• 仅有1条订单的合作伙伴，周期为0
• 多条订单的合作伙伴：
  • 2条订单：取两笔订单的间隔天数
  • 3条及以上：计算相邻订单间隔天数的平均值

实现方案 #

可以借助PostgreSQL的窗口函数LAG()来获取同一合作伙伴的上一笔订单日期，再通过聚合计算平均间隔：

WITH partner_order_intervals AS (
    SELECT
        partner_id,
        order_date,
        -- 按合作伙伴分组、订单日期排序，获取上一笔订单日期
        LAG(order_date) OVER (PARTITION BY partner_id ORDER BY order_date) AS prev_order_date
    FROM your_table_name -- 替换为实际表名
)
SELECT
    partner_id,
    -- 计算平均间隔，无间隔时返回0，结果取整贴合预期
    COALESCE(ROUND(AVG((order_date - prev_order_date)::NUMERIC)), 0) AS period
FROM partner_order_intervals
GROUP BY partner_id
ORDER BY partner_id;

代码说明 #

1. CTE部分：partner_order_intervals通过LAG()窗口函数，为每条订单匹配同一合作伙伴的上一笔订单日期，第一条订单的prev_order_date会是NULL。
2. 主查询部分：
   • order_date - prev_order_date直接得到日期间隔的天数（PostgreSQL中日期相减返回天数差值）
   • AVG()计算同一合作伙伴的所有间隔的平均值，ROUND()将结果取整（对应示例中partner_id=4的7.5取整为8）
   • COALESCE()把平均值为NULL的情况（即只有1条订单的合作伙伴）替换为0

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