PostgreSQL LATERAL 每组前 N

示例说明

每组前 N 的两条路线：窗口函数先把全部行排好名再过滤——数据量大时全量开窗成本高；LATERAL 则把循环翻到外层——每个客户驱动一次内层查询，配合索引内层的 LIMIT 三条是即查即回。筛选后的客户集合小、每组要的条数少时 LATERAL 占优；要全表每组的完整排名时窗口函数更直接。

技术要点

• LATERAL 外层驱动内层逐行求值
• 索引支撑内层 LIMIT 秒回
• 窗口函数版适合全量排名场景

代码解读

主查询的 LEFT JOIN LATERAL 保证没有订单的客户也出现在结果里（订单字段为 NULL）；内层按放置时间倒序取三条，索引 (customer_id, placed_at DESC) 让它免排序。对照版本演示窗口函数的全量开窗形态。ON true 是 LATERAL 的标准联结条件写法。

使用建议

外层行数乘内层成本是 LATERAL 的总账，外层必须先收敛。N 很大时两者差距缩小。内层子查询务必核对索引命中否则逐行退化。

代码示例

-- PostgreSQL LATERAL：外层每行驱动内层子查询一次
SELECT
  c.id AS customer_id,
  c.email,
  latest.order_id,
  latest.total_cents,
  latest.placed_at
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.id AS order_id, o.total_cents, o.placed_at
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.placed_at DESC
  LIMIT 3
) latest ON true
WHERE c.region = 'eu'
ORDER BY c.id, latest.placed_at DESC;

-- 对照窗口函数版本：全量开窗后过滤
SELECT customer_id, order_id, total_cents, placed_at
FROM (
  SELECT
    customer_id, id AS order_id, total_cents, placed_at,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY placed_at DESC
    ) AS rn
  FROM orders
) ranked
WHERE rn <= 3;

-- 支撑索引：内层子查询的 LIMIT 才能秒回
CREATE INDEX idx_orders_customer_time
  ON orders (customer_id, placed_at DESC);