Блог

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

Вот пример того, как будет выглядеть моя таблица истории:

 ID_OF_THING  VERSION_NUMBER   DATA
1            3                'Value2'
1            2                'Value2'
1            1                'Value1'
2            3                'Value3'
2            2                'Value2'
2            1                'Value1'
  

В этом случае ответом для id_of_thing 1 будет версия 2, потому что это самый высокий номер версии, где предыдущий номер версии содержит другие данные. Ответом для id_of_thing 2 является версия 3.

Я даже не уверен, с чего здесь начать. Если бы мне просто нужен был номер самой последней версии, это было бы так просто, как:

 select ID_OF_THING, MAX(VERSION_NUMBER)
GROUP BY ID_OF_THING;
  

Это проще всего сделать с помощью аналитических (иначе оконных) функций, в данном случае lead() или lag () для просмотра данных следующей или предыдущей строки. У вас должно сработать следующее (замените имя таблицы, которое я использовал («test»), на то, что вы назвали своей таблицей):

 select 
    id_of_thing, 
    version_with_latest_change=max(version_number) 
from (
    select 
        id_of_thing, version_number, data, 
        previous_data=lag(data) over (
            partition by id_of_thing 
            order by version_number
        ) 
    from test
) x
where data <> previous_data
group by id_of_thing
  

Смотрите пример ниже, где я использовал оператор WITH для «имитации» вашей таблицы. На самом деле просто замените «tbL» именем вашей таблицы и удалите предложение WITH.

Запрос находит максимальную версию, в которой изменилось значение, и использует идентификатор и версию для возврата строки из вашей таблицы. Также рассматриваются случаи, когда существует только одна версия записи без каких-либо обновлений.

     WITH tbl As
    (
    SELECT 1 As id, 3 As ver, 'Value2' As val FROM dual UNION ALL
    SELECT 1 As id, 2 As ver, 'Value2' As val FROM dual UNION ALL
    SELECT 1 As id, 1 As ver, 'Value1' As val FROM dual UNION ALL
    SELECT 2 As id, 3 As ver, 'Value3' As val FROM dual UNION ALL
    SELECT 2 As id, 2 As ver, 'Value2' As val FROM dual UNION ALL
    SELECT 2 As id, 1 As ver, 'Value1' As val FROM dual
    )
    SELECT t.*
      FROM tbl t
     WHERE (t.id, t.ver) IN 
    (
    SELECT z.id
         , MAX(z.ver) As max_ver
      FROM ( SELECT x.id
                  , x.ver
                  , x.val
                  , LAG(x.val) OVER (PARTITION BY x.id ORDER BY ver) As lag_val
                  , MIN(x.ver) OVER (PARTITION BY x.id) As min_ver
                  , MAX(x.ver) OVER (PARTITION BY x.id) As max_ver
               FROM tbl x
           ) z
     WHERE ( (z.min_ver = z.max_ver) -- where there is only one version
          OR (z.val != z.lag_val)    -- where the value has changed
           )
     GROUP BY z.id
    );