sql - 限制在Windows WINDOW分区定义中吗？

假设你有两张桌子：
命令

order_id , ....
1, ...
2, ...

每个订单都有“状态历史”。它存储在历史记录表中：

serial, date, order_id , order_state
10,     2012-01-01, 1,  'INIT'
11,     2012-01-02, 2,  'INIT'
12,     2012-02-03, 1,  'COMPLETED'
13,     2012-02-04, 1,  'DISPATCHED'
14,     2012-02-05, 2,  'COMPLETED'
15,     2012-02-06, 2,  'DISPATCHED'

现在我想知道过去任何时候我所有命令的状态。例如，我想知道2012-02-05的订单状态：

order_id, order_state, state_date
1 ,      'DISPATCHED' , 2012-02-04
2,       'COMPLETED'  , 2012-02-05

（state_date列是可选的）。
有没有一种方法可以编写一个查询，比在订单中循环并在应用程序代码中获取其“给定日期的状态”更有效？
我在Postgresql的rank（）窗口函数中做得很好，但是我不太高兴它必须获取所有历史（在给定日期之前）来选择rank 1中的一个。在我看来，这并不比在应用程序代码中做得更好。

SELECT *  FROM ( SELECT o.order_id,
                       h.order_state,
                       h.state_date,
                       rank() ON (PARTITION BY order_id ORDER BY h.serial DESC)
                              AS hrank
                FROM order o, history h
                WHERE
                     h.order_id = o.order_id AND
                     h.date < given_date
               ) AS rh
WHERE
   rh.hrank = 1;

我真正想要的是分区定义中的某种限制1，但我不知道它是否可能。

最佳答案

SQL Fiddle

select distinct on (o.order_id) o.order_id, h.order_state, h.date
from
    orders o
    inner join
    history h on h.order_id = o.order_id
where
    h.date <= '2012-02-05'
order by o.order_id, h.date desc

distinct on将根据声明的顺序返回第一个。在这种情况下是最近的日期。