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

Retrieving a current and most recent previous value (Oracle)

提问by K2J

I'm facing a problem which I've spent a great deal of time trying to address, and although I have a solution, it's clunky and involves pl/sql processing, and I was wondering what others might come up with.

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

I'm working with a dataset that creates a new row every time a record is changed, thus maintaining a history. The most up-to-date version is then displayed in our application. Consider a table with the following data:

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

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

As part of a report, I need to extract the details which have changed between a given set of dates. For instance, say I want to extract the current address_line1and the previous address_line1along with the date of change (effective_start_datewhen the new address was added). The caveat is that if other column data changes, this will create a new row too. For instance, in the example above, the last_namechanged after the address changed.

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

Unfortunatey, the query must be generic so that it can be run as part of a report,i.e. not having to specify explicitly the effective start and end dates.

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

Hope that all makes sense. Hopefully, you're all still with me. So, given the data-set above, I would expect to see the following results in my report:

希望一切都说得通。希望你们都还在我身边。因此，鉴于上述数据集，我希望在我的报告中看到以下结果：

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

My approach involves processing with pl/sql and looping over a considerable number of records but I was wondering if this can be done in a single sql query.

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

Does anyone have any ideas on whether this can be done using only sql?

有没有人知道这是否可以仅使用 sql 来完成？

回答by Quassnoi

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

To return the last effective value, use:

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

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

回答by Reuben

Don't Completely Understand your Question.

不要完全理解你的问题。

As part of a report, I need to extract the details which have changed between a given set of dates.

作为报告的一部分，我需要提取在给定日期集之间发生变化的详细信息。

Versus

相对

Unfortunatey, the query must be generic so that it can be run as part of a report,i.e. not having to specify explicitly the effective start and end dates.

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

1) Does your Report provide a start date and End date, and look for changes within the range?. 2) Or are you looking for a general report for all changes with no constraints on date?

1) 您的报告是否提供了开始日期和结束日期，并在该范围内寻找变化？。2) 或者您是否正在寻找所有更改的一般报告，而没有日期限制？

Also

还

Assuming the Last Name Changed within the set of dates for which you are running the report.

假设姓氏在您运行报告的日期集合内发生了变化。

1) In your expected result are you completely ignoring the Fact that the Last_Name Changed

1）在您的预期结果中，您完全忽略了姓氏更改的事实

2) Or will that be another row in your Report.

2) 或者那将是您报告中的另一行。

of the type

类型的

Person ID  Surname  Prev_Surname   Effective_Start_date_of_New Surname
4913       Jones    Cross          03-aug-02

3) Or is it even more complicated that you will capture all changes in one row.

3) 或者更复杂的是，您将在一行中捕获所有更改。

Person ID  Surname  Prev_Surname   Effective_Start_date_of_New Surname Address_line1   Prev_Address_line1  Effective_Start_date_of_New_Address_Line_1 
4913       Jones    Cross          03-aug-02                           1 First Street  86 Green Avenue     01-feb-02

For a Report which shows each change, with the previous values for name and address. Try this. You can pick the last change by an outer select and choosing Change_rank =1

对于显示每次更改的报告，以及名称和地址的先前值。尝试这个。您可以通过外部选择选择最后一个更改并选择 Change_rank =1

SELECT   PERSON_ID, CHANGED, NEW_VAL, OLD_VAL, EFFECTIVE_START_DATE
        -- The Rank just lets you pick by order of change incase you only want the last change, or last 2 changes
         ,RANK () OVER (ORDER BY EFFECTIVE_START_DATE DESC) CHANGE_RANK
    FROM (
          -- A select over Partition for each column where Old_val and New_Val need to be retrieved.
          -- In this Select the Address Column and Changed Address Are retrieved.
          SELECT PERSON_ID, 'ADDRESS_LINE1 CHANGE' CHANGED, ADDRESS_LINE1 NEW_VAL
                ,LEAD (ADDRESS_LINE1) OVER (PARTITION BY PERSON_ID ORDER BY EFFECTIVE_START_DATE DESC) AS OLD_VAL
                ,EFFECTIVE_START_DATE
            FROM TMP_RM_TEST
           -- Usually You want to run a report for a period,  Or remove this entire filter
          WHERE  EFFECTIVE_START_DATE BETWEEN TO_DATE (:REPORT_START_DATE, 'YYYYMMDD')
                                          AND TO_DATE (:REPORT_END_DATE, 'YYYYMMDD')
          UNION
          -- In this Select the Name Column and Changed Name Are retrieved.
          SELECT PERSON_ID, 'LAST_NAME CHANGE' CHANGED, LAST_NAME NEW_VAL
                ,LEAD (LAST_NAME) OVER (PARTITION BY PERSON_ID ORDER BY EFFECTIVE_START_DATE DESC) AS OLD_VAL
                ,EFFECTIVE_START_DATE
            FROM TMP_RM_TEST
           WHERE EFFECTIVE_START_DATE BETWEEN TO_DATE (:REPORT_START_DATE, 'YYYYMMDD')
                                          AND TO_DATE (:REPORT_END_DATE, 'YYYYMMDD') )
   -- Since the Partition By lists all changes over Person_ID, Irrespective of whether the Column changed, Filter to Remove Lines where no change was made to the Coloumn monitored
WHERE    NEW_VAL <> OLD_VAL
ORDER BY CHANGE_RANK