PostgreSQL LATERAL Join Top N Per Group

-- 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);