Блог

Главная - Вопросы по программированию - SQL-запрос для сравнения текущего и предыдущего значения в столбце таблицы истории

SQL-запрос для сравнения текущего и предыдущего значения в столбце таблицы истории

#sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

У меня есть база данных 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

Обратите внимание, что я ищу последнюю запись, где скорость отличается от самой последней записи.

Я пробовал различные подходы, но либо получаю слишком много результатов, либо вообще ничего. Есть предложения?

Ответ №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

Комментарии:

1. спасибо за быстрый ответ, но у меня возникли проблемы с этим. Я получаю «недопустимое имя столбца» для всех столбцов, указанных в (select row_number() over () as ... ) за rn исключением. Конечно, имена, которые я привел в примере, не совсем соответствуют столбцам моей базы данных, но я заменил значения и повторил их несколько раз; Я уверен, что у меня правильные имена таблиц и столбцов. Я также реструктурировал запрос, как with rate as ( select rn = row_number() over (partition...), id, rate, ... ) , та же проблема.

2. Это не показало бы ему предыдущую скорость, отличную от текущей , только вторую по последней скорости

3. @val извините за все изменения, мне потребовалось некоторое время, чтобы получить правильные данные образца, а также согласованные выходные данные для полной истории и последней истории

4. @Conrad, NP, спасибо, что нашли время поработать над этим. Я многому учусь; я не часто использовал разделы. Прямо сейчас этот запрос выполняется очень медленно в моей базе данных, но результаты выглядят хорошо. Спасибо!

Ответ №2:

По какой-то причине, основанной на том, как я вставлял в свой тест, это не сработало. Мне пришлось добавить предыдущее.дата в порядке row_number следующим образом:

 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