sql - 检索当前和最近的先前值 (Oracle)

Question

我面临着一个问题，我花了很多时间试图解决这个问题，虽然我有一个解决方案，但它很笨重并且涉及 pl/sql 处理，我想知道其他人可能会想出什么。

我正在使用一个数据集，每次更改记录时都会创建一个新行，从而维护历史记录。然后，最新版本将显示在我们的应用程序中。考虑一个包含以下数据的表:

Person ID  Last_Name  Address_line1       Effective_Start_Date  Effective_End_Date4913       Jones      1 First Street      03-aug-02             31-dec-124913       Cross      1 First Street      01-feb-02             02-aug-024913       Cross      86 Green Avenue     01-mar-01             31-jan-024913       Cross      87 Devonshire Road  01-jan-90             28-feb-02

作为报告的一部分，我需要提取给定日期集之间发生更改的详细信息。例如，假设我想提取当前的 address_line1 和之前的 address_line1 以及更改日期(添加新地址时的 effective_start_date ) )。需要注意的是，如果其他列数据发生更改，这也会创建一个新行。例如，在上面的示例中，last_name 在地址更改后发生更改。

不幸的是，查询必须是通用的，以便它可以作为报告的一部分运行，即不必明确指定有效的开始和结束日期。

希望一切都有意义。希望你们都还在我身边。因此，考虑到上面的数据集，我希望在我的报告中看到以下结果:

Person ID  Surname  Address_line1   Prev_Address_line1  Effective Start date of New Address Line 14913       Jones    1 First Street  86 Green Avenue     01-feb-02

我的方法涉及使用 pl/sql 进行处理并循环访问大量记录，但我想知道这是否可以在单个 SQL 查询中完成。

有人知道是否可以仅使用 sql 来完成此操作吗？

Answer 1

SELECT  personID, surname, address_line1,
        LAG(address_line1) OVER (PARTITION BY personID ORDER BY effectiveDate) AS prev_address_line1
FROM    mytable
WHERE   personID = :myid
ORDER BY
        effectiveDate

要返回最后一个有效值，请使用:

SELECT  *
FROM    (
        SELECT  personID, surname, address_line1,
                LAG(address_line1) OVER (PARTITION BY personID ORDER BY effectiveDate) AS prev_address_line1,
                ROW_NUMBER() OVER (PARTITION BY personID ORDER BY effectiveDate DESC) AS rn,
        FROM    mytable
        WHERE   personID = :myid
        )
WHERE   rn = 1