Как: аналитическая функция Oracle для возврата строки

Думаю, на мой вопрос можно было бы ответить с помощью аналитической функции Oracle в SQL, но я не уверен. Скажем, у меня есть следующая таблица БД ДОКУМЕНТОВ:

  • Ранг: обратная последовательность, каждый документ имеет свою собственную последовательность, последняя редакция документа имеет наименьший (0) номер
  • Редакция: буквенно-цифровая последовательность для каждого документа, последняя редакция документа имеет наивысший идентификатор редакции
NAME RANK REVISION STATE
DocumentA 0 5b ReadOnly
DocumentA 1 5a Draft
DocumentA 3 3 ReadOnly
DocumentA 4 2 Draft
DocumentA 2 4 Published
DocumentA 5 1 Published
DocumentB 0 2 Draft
DocumentB 1 1 Published
DocumentC 0 1 Published

Запрошенный набор результатов: последняя опубликованная редакция для каждого документа.

Дайте мне для каждого документа последний опубликованный документ с наименьшим порядковым номером

Поскольку последняя редакция документа может находиться в состоянии черновика, это не всегда 0

NAME RANK REVISION STATE
DocumentA 2 4 Published
DocumentB 1 1 Published
DocumentC 0 1 Published

Пожалуйста, сформулируйте SQL-запрос, чтобы вернуть этот набор результатов. Большое спасибо!


sql oracle analytic-functions
person Spanky    schedule 27.01.2021    source источник
comment
dba.stackexchange.com/questions/6368/   -  person Mat    schedule 27.01.2021
comment
Вы хотите получать предпоследние строки непосредственно перед Draft всякий раз, когда они упорядочиваются по убыванию, начиная с самых ранних?   -  person Barbaros Özhan    schedule 27.01.2021
comment
Меня интересуют только опубликованные ревизии, независимо от того, сколько ревизий документа было добавлено до или позже   -  person Spanky    schedule 27.01.2021
comment
Вы выбираете ДокументA RANK 2, REVISION 4, потому что RANK 2 ‹RANK 5 или потому, что REVISION 4› REVISION 1?   -  person Thorsten Kettner    schedule 27.01.2021
comment
Потому что ранг 2 ‹ранг 5. Я хочу, чтобы опубликованные редакции имели самый низкий ранг, поскольку это самая новая редакция документа.   -  person Spanky    schedule 27.01.2021
comment
Итак, мы можем полностью игнорировать столбец ревизии? Это было бы легче понять, если бы вы просто опустили его в своем запросе :-) Ответ Литтлфута должен сработать для вас. Удалите только ревизию из пункта order by.   -  person Thorsten Kettner    schedule 27.01.2021
comment
Правильный. Ревизия для запроса не требуется. Я добавил его только для лучшей визуализации обратного символа ранга.   -  person Spanky    schedule 27.01.2021

Ответы (2)


arrow_upward
0
arrow_downward

Что-то вроде этого?

SQL> with test (name, rank, revision, state) as
  2    (select 'A', 0, '5b', 'ReadOnly'  from dual union all
  3     select 'A', 2,  '4', 'Published' from dual union all
  4     select 'A', 5,  '1', 'Published' from dual union all
  5     select 'B', 0,  '2', 'Draft'     from dual union all
  6     select 'B', 1,  '1', 'Published' from dual union all
  7     select 'C', 0,  '1', 'Published' from dual
  8     )
  9  select name, rank, revision, state
 10  from (select t.*,
 11          rank() over (partition by name order by revision desc, rank) rn
 12        from test t
 13        where state = 'Published'
 14       )
 15  where rn = 1;

N       RANK RE STATE
- ---------- -- ---------
A          2 4  Published
B          1 1  Published
C          0 1  Published

SQL>
person Littlefoot    schedule 27.01.2021

arrow_upward
0
arrow_downward

Запрошенный набор результатов: последняя опубликованная редакция для каждого документа.

Один метод не использует оконные функции:

select t.*
from t
where t.state = 'Published' and
      t.rank = (select min(t2.rank) 
                from t t2
                where t2.name = t.name and t2.state = t.state
               );

А в Oracle вы даже можете использовать агрегирование:

select document, state, min(rank),
       min(revision) keep (dense_rank first order by rank) as revision
from t
where state = 'Published'
group by document, state;

Оконные функции - вполне разумное решение проблемы, однако они не требуются.

person Gordon Linoff    schedule 27.01.2021