PostgreSQL中计算单票售出后3个月内客户的总营收与票数

核心问题分析 #

你之前的代码里，子查询中的窗口函数是基于整个customer_id分区统计的，相当于计算了客户所有订单的数量、均价和营收，之后的JOIN日期过滤只是筛选了关联的行，但统计值本身并没有限制在3个月范围内，所以结果不符合预期。

下面提供两种高效的解决方法：

方法一：窗口函数+FILTER子句（推荐，性能最优） #

利用PostgreSQL的窗口函数结合FILTER子句，直接在同一个分区内筛选出当前票之后3个月的记录进行统计，无需额外JOIN：

SELECT
  customer_id,
  ticket_id,
  ticket_number,
  initial_sale_date,
  -- 当前票售出后3个月内，该客户的购票总数
  COUNT(*) FILTER (
    WHERE initial_sale_date > base.initial_sale_date
    AND initial_sale_date <= base.initial_sale_date + INTERVAL '3 months'
  ) OVER (PARTITION BY customer_id) AS post_3m_tix,
  -- 对应总营收
  SUM(final_fare_value) FILTER (
    WHERE initial_sale_date > base.initial_sale_date
    AND initial_sale_date <= base.initial_sale_date + INTERVAL '3 months'
  ) OVER (PARTITION BY customer_id) AS post_3m_rev,
  -- 平均客单价（避免除零错误）
  CASE
    WHEN COUNT(*) FILTER (
      WHERE initial_sale_date > base.initial_sale_date
      AND initial_sale_date <= base.initial_sale_date + INTERVAL '3 months'
    ) OVER (PARTITION BY customer_id) > 0
    THEN SUM(final_fare_value) FILTER (
      WHERE initial_sale_date > base.initial_sale_date
      AND initial_sale_date <= base.initial_sale_date + INTERVAL '3 months'
    ) OVER (PARTITION BY customer_id) 
    / COUNT(*) FILTER (
      WHERE initial_sale_date > base.initial_sale_date
      AND initial_sale_date <= base.initial_sale_date + INTERVAL '3 months'
    ) OVER (PARTITION BY customer_id)
    ELSE NULL
  END AS post_3m_aov
FROM reports.agg_tickets base;

方法二：优化LATERAL JOIN的执行速度 #

如果你倾向用LATERAL JOIN，通过添加索引可以大幅提升查询效率：

第一步：创建针对性索引 #

CREATE INDEX idx_agg_tickets_customer_date ON reports.agg_tickets(customer_id, initial_sale_date) INCLUDE (final_fare_value);

第二步：优化后的LATERAL JOIN查询 #

SELECT
  base.customer_id,
  base.ticket_id,
  base.ticket_number,
  base.initial_sale_date,
  agg.post_3m_tix,
  agg.post_3m_rev,
  CASE WHEN agg.post_3m_tix > 0 THEN agg.post_3m_rev / agg.post_3m_tix ELSE NULL END AS post_3m_aov
FROM reports.agg_tickets base
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) AS post_3m_tix,
    SUM(final_fare_value) AS post_3m_rev
  FROM reports.agg_tickets agg
  WHERE agg.customer_id = base.customer_id
    AND agg.initial_sale_date > base.initial_sale_date
    AND agg.initial_sale_date <= base.initial_sale_date + INTERVAL '3 months'
) agg ON TRUE;

索引会让子查询快速定位到同一客户且日期符合要求的记录，解决之前执行过慢的问题。

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