用于比较历史表中某列的当前值和上一个值的SQL查询

Question

我有一个SQL Server 2008数据库，其中有一个历史表，用于在主表中记录更改。我需要报告'rate‘列的当前(最新)值，以及与当前值不同的最近前一个值。

因此，假设如下所示：

id   | rate   | uninteresting | updated_on   | version
-----+--------+---------------+--------------+----------
123  | 1.20   | foo           | 2010-10-18   | 1500
456  | 2.10   | bar           | 2010-10-12   | 2123
123  | 1.20   | baz           | 2010-10-10   | 1499
123  | 1.10   | baz           | 2010-10-08   | 1498
456  | 2.00   | bar           | 2010-10-11   | 2122
123  | 1.00   | baz           | 2010-08-01   | 1497
456  | 2.00   | quux          | 2010-10-05   | 2121
456  | 1.95   | quux          | 2010-09-07   | 2120

我想制作：

id   | cur_rate | cur_ver | updated_on | prev_rate | prev_ver | prev_updated
-----+----------+---------+------------+-----------+----------+-------------
123  | 1.20     | 1500    | 2010-10-18 | 1.10      | 1498     | 2010-10-08
456  | 2.10     | 2123    | 2010-10-12 | 2.00      | 2122     | 2010-10-11

请注意，我正在查找速率与最近条目不同的最新条目。

我尝试了各种方法，但要么得到了太多的结果，要么根本没有结果。有什么建议吗？

Answer 1

有几种方法可以做到这一点。这里有一种方法

Declare @table  as table( 
id int, 
rate decimal(10,5) , 
uninteresting  varchar(10) ,
updated_on  date, 
version int )
INSERT INTO @table
VALUES 
(123  , 1.20   , 'foo     ' , '2010-10-18'   , 1500),
(456, 2.1, ' bar           ', ' 2010-10-12   ', 2123),
(123, 1.2, ' baz           ', ' 2010-10-10   ', 1499),
(123, 1.1, ' baz           ', ' 2010-10-08   ', 1498),
(456, 2, ' bar           ', ' 2010-10-11   ', 2122),
(123, 1, ' baz           ', ' 2010-08-01   ', 1497),
(456, 2, ' quux          ', ' 2010-10-05   ', 2121),
(456, 1.95, ' quux          ', ' 2010-09-07   ', 2120)


;WITH rates 
     AS (SELECT Row_number() OVER ( PARTITION BY curr.id, curr.rate ORDER BY curr.updated_on DESC) AS rn,
            curr.id, 
            curr.rate        cur_rate, 
            curr.version     cur_ver, 
            curr.updated_on, 
            previous.rate       prev_rate, 
            previous.version    prev_ver, 
            previous.updated_on prev_updated 
          FROM 
                @table curr
                LEFT JOIN @table previous
                ON curr.id = previous.id 
                   AND curr.rate <> previous.rate
                   AND curr.updated_on > previous.updated_on 

   ) 
   SELECT 
           id, 
           cur_rate, 
           cur_ver, 
           updated_on, 
           prev_rate, 
           prev_ver, 
           prev_updated 
    FROM 
           rates 
    WHERE 
           rn = 1

产生这样的结果

id          cur_rate cur_ver     updated_on prev_rate prev_ver    prev_updated
----------- -------- ----------- ---------- --------- ----------- ------------
123         1.00000  1497        2010-08-01 NULL      NULL        NULL
123         1.10000  1498        2010-10-08 1.00000   1497        2010-08-01
123         1.20000  1500        2010-10-18 1.10000   1498        2010-10-08
456         1.95000  2120        2010-09-07 NULL      NULL        NULL
456         2.00000  2122        2010-10-11 1.95000   2120        2010-09-07
456         2.10000  2123        2010-10-12 2.00000   2122        2010-10-11

如果您通过更改rn来丢弃分区中的速率，例如( PARTITION BY curr.id ORDER BY curr.updated_on DESC) AS rn,

你会得到

id          cur_rate cur_ver     updated_on prev_rate prev_ver    prev_updated
----------- -------- ----------- ---------- --------- ----------- ------------
123         1.20000  1500        2010-10-18 1.10000   1498        2010-10-08
456         2.10000  2123        2010-10-12 2.00000   2122        2010-10-11

Answer 2

由于某种原因，基于我在测试中插入的方式，它不能工作。我必须按如下方式在row_number顺序中添加previous.date：

ROW_NUMBER() OVER (PARTITION BY curr.id, curr.status_id ORDER BY curr.row_created_date DESC, previous.row_created_date DESC) AS rn,

我的场景略有不同，我还需要能够在我的“状态”可能发生变化时来回切换。这是为我工作的代码。

DECLARE @mytemptable TABLE
(
    tableid INT IDENTITY(1,1) PRIMARY KEY,
    id INT,
    status_id INT,
    [user_id] INT,
    row_created_date DATE
)

INSERT INTO @mytemptable VALUES (112266980, 1, 5, GETDATE()-21);
INSERT INTO @mytemptable VALUES (112266980, 2, 5, GETDATE()-14);
INSERT INTO @mytemptable VALUES (112266980, 3, 6, GETDATE()-7);
INSERT INTO @mytemptable VALUES (112266980, 4, 8, GETDATE());
INSERT INTO @mytemptable VALUES (112277777, 1, 5, GETDATE()-21);
INSERT INTO @mytemptable VALUES (112277777, 2, 5, GETDATE()-14);
INSERT INTO @mytemptable VALUES (112277777, 3, 5, GETDATE()-6);
INSERT INTO @mytemptable VALUES (112266666, 1, 5, GETDATE()-40);
INSERT INTO @mytemptable VALUES (112266666, 2, 5, GETDATE()-30);
INSERT INTO @mytemptable VALUES (112266666, 3, 5, GETDATE()-25);
INSERT INTO @mytemptable VALUES (112266666, 2, 5, GETDATE()-20);

SELECT * FROM @mytemptable ORDER BY id, row_created_date DESC

;WITH statuses
          AS (
              SELECT
                ROW_NUMBER() OVER (PARTITION BY curr.id, curr.status_id, curr.row_created_date ORDER BY curr.row_created_date DESC, previous.row_created_date DESC) AS rn,
                curr.id,
                curr.status_id curr_status_id,
                curr.user_id AS curr_user_id,
                curr.row_created_date AS curr_datetime,
                previous.status_id prev_status_id,
                previous.user_id AS prev_user_id,
                previous.row_created_date AS prev_datetime
              FROM
                @mytemptable AS curr
                LEFT JOIN @mytemptable AS previous
                    ON curr.id = previous.id
                       AND curr.status_id <> previous.status_id
                       AND curr.row_created_date > previous.row_created_date
             )
    SELECT
        id,
        curr_status_id,
        curr_user_id,
        curr_datetime,
        prev_status_id,
        prev_user_id,
        prev_datetime
    FROM
        statuses
    WHERE
        rn = 1
    ORDER BY
        id, curr_datetime DESC