SQL:检索每个客户的最新订单

Question

我有两个具有一对多关系的表(例如客户表和订单表；每个客户可能下了多个订单)。对于每个客户，我只需要检索一个订单，按照特定标准定义(例如，最近的订单)。

我需要在 PostgreSQL 9.1 和 Microsoft SQL Server 2014 中执行此操作(两个系统中的数据不同，但问题非常相同)。这些表大约有 250 万条记录(客户)和 1000 万条记录(订单)。

我曾考虑在 SQL Server 中使用外部应用并在 PostgreSQL 中对分区或横向连接进行排名，但我不太确定语法。

一个简单的 max(order_date) ... group by clientid 是不够的，因为我需要订单表中的所有字段。例如，我需要这样的输出

c.clientid, c.client_name, o.order_id, o.order_date, o.order_amount, o.product_ordered
where c = the clients table and o = the orders table.

有什么建议吗？

Answer 1

我不明白为什么需要横向连接。这是一个很好的老式“每组最多 n 个”问题，可以通过简单的连接和窗口函数来解决:

select *
from clients c
  join (
     select clientid, order_id, order_date, order_amount, product_ordered 
            row_number() over (partition by client_id order by order_date desc) as rn
     from orders
  ) o on o.clientid = c.clientid and o.rn = 1;

对于 Postgres，这可以通过使用 distinct on 而不是窗口函数来提高效率。如果您在 client_id, order_date 上有一个索引，这应该会执行得很好。