sql - 取小于当前行数据的最大数据

Question

我有一个包含 3 列的表格:Id、Number 和 Date，其中 ID 和Number 一起构成一个主键。该表如下所示:

    +----+--------+------------------+--+
    | ID | Number | Date             |  |
    +----+--------+------------------+--+
    | 1  | 1234   | 10-12-13 1:05:33 |  |
    +----+--------+------------------+--+
    | 1  | 2314   | 10-12-13 1:10:32 |  |
    +----+--------+------------------+--+
    | 2  | 1234   | 10-13-14 3:04:55 |  |
    +----+--------+------------------+--+
    | 3  | 4312   | 05-12-13 5:05:00 |  |
    +----+--------+------------------+--+
    | 3  | 1234   | 04-13-12 3:12:53 |  |
    +----+--------+------------------+--+
    | 4  | 4312   | 05-12-11 5:05:00 |  |
    +----+--------+------------------+--+

现在，无论 ID 是什么，我都希望数字的最大日期小于每行的日期。因此，对于 ID = 1 和 Number = 1234，我想要 1234 的最大值但小于该行中的日期的日期。结果应该是 04-13-12 3:12:53。同样，对于 ID = 2 和 Number = 1234，结果应为 10-12-13 1:05:33。

所以输出应该是这样的:

    +----+--------+------------------+------------------+
    | ID | Number | Date             | Prev Date        |
    +----+--------+------------------+------------------+
    | 1  | 1234   | 10-12-13 1:05:33 | 04-13-12 3:12:53 |
    +----+--------+------------------+------------------+
    | 1  | 2314   | 10-12-13 1:10:32 | 10-12-13 1:10:32 |
    +----+--------+------------------+------------------+
    | 2  | 1234   | 10-13-14 3:04:55 | 10-12-13 1:05:33 |
    +----+--------+------------------+------------------+
    | 3  | 4312   | 05-12-13 5:05:00 | 05-12-11 5:05:00 |
    +----+--------+------------------+------------------+
    | 3  | 1234   | 04-13-12 3:12:53 | 04-13-12 3:12:53 |
    +----+--------+------------------+------------------+
    | 4  | 4312   | 05-12-11 5:05:00 | 05-12-11 5:05:00 |
    +----+--------+------------------+------------------+

希望这是清楚的。关于应该如何构建查询，我完全是一片空白。谢谢。

Answer 1

我想你只想要一个累积最大值:

select t.*,
       max(date) over (partition by id
                       order by date
                       rows between unbounded preceding and 1 preceding
                      ) as prev_date
from t;

编辑:

根据问题中的示例，您可能需要:

select t.*,
       max(date) over (partition by number
                       order by date
                       rows between unbounded preceding and 1 preceding
                      ) as prev_date
from t;