Затейливые сортировки

Конструкция order by запроса Oracle позволяет упорядочить выбор данных. Ее можно использовать и для более сложных задач.

Предположим, у нас есть таблица состояний некоторого процесса, состоящая из двух столбцов: идентификатор и формулировка состояния. Как обычно, для примера мы не будем создавать реальную таблицу, а воспользуемся генератором тестовых данных с конструкцией with:

with statuses as
(
select 1 as id, 'Открытие заказа' as text from dual union all
select 2, 'Проверка заказа' from dual union all
select 3, 'Выполнение заказа' from dual union all
select 4, 'Оплата заказа' from dual union all
select 5, 'Закрытие заказа' from dual
)
select id, text from statuses
order by id
;

1 Открытие заказа
2 Проверка заказа
3 Выполнение заказа
4 Оплата заказа
5 Закрытие заказа

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

Пока не оказалось, что для одной из ветвей нашего процесса состояние "Оплата заказа" должно предшествовать состоянию "Выполнение заказа". А с таблицей уже ничего делать нельзя. Может быть, она вообще находится в неподконтрольной нам базе, берем мы ее по DBlink'у, с владельцами говорить сложно, а проект сдавать надо.

Как переставить "Оплату" между "Проверкой" и "Выполнением"? Надо работать с конструкцией order by, сортировать не по ID, а по чему-то другому, что и следует записать.

Можно "пойти в лоб" и сформировать отдельное сортировочное поле ord прямо по тексту, что-то типа:

with statuses as
(
select 1 as id, 'Открытие заказа' as text from dual union all
select 2, 'Проверка заказа' from dual union all
select 3, 'Выполнение заказа' from dual union all
select 4, 'Оплата заказа' from dual union all
select 5, 'Закрытие заказа' from dual
)
select id, text
, case
when text = 'Открытие заказа' then 1
when text = 'Проверка заказа' then 2
when text = 'Оплата заказа' then 3
when text = 'Выполнение заказа' then 4
when text = 'Закрытие заказа' then 5
end as ord
from statuses
order by ord
;

1 Открытие заказа 1
2 Проверка заказа 2
4 Оплата заказа 3
3 Выполнение заказа 4
5 Закрытие заказа 5

Результат получен, но назвать решение красивым сложно. Можно слегка улучшить его, записав case не по тексту формулировки, а по идентификатору - будет несколько строже, но все равно не изящно.

Вспомним про функцию decode(), а также про то, что мы не обязаны сортировать по целым значениям - можем и по дробным. Используем функцию decode() для проверки значения идентификатора: если идет значение идентификатора 4, то сортировочному полю присвоим значение между 2 и 3, в остальных случаях присвоим сортировочному полю текущее значение идентификатора:

with statuses as
(
select 1 as id, 'Открытие заказа' as text from dual union all
select 2, 'Проверка заказа' from dual union all
select 3, 'Выполнение заказа' from dual union all
select 4, 'Оплата заказа' from dual union all
select 5, 'Закрытие заказа' from dual
)
select id, text
, decode(id, 4,2.5, id) as ord
from statuses
order by ord
;

1 Открытие заказа 1
2 Проверка заказа 2
4 Оплата заказа 2,5
3 Выполнение заказа 3
5 Закрытие заказа 5

- полученное решение гораздо компактней "лобового подхода".

Но мы можем его улучшить, вообще отказавшись от сортировочного поля ord. Для этого просто перенесем функцию decode() в конструкцию order by:

with statuses as
(
select 1 as id, 'Открытие заказа' as text from dual union all
select 2, 'Проверка заказа' from dual union all
select 3, 'Выполнение заказа' from dual union all
select 4, 'Оплата заказа' from dual union all
select 5, 'Закрытие заказа' from dual
)
select id, text
from statuses
order by decode(id, 4,2.5, id)
;

1 Открытие заказа
2 Проверка заказа
4 Оплата заказа
3 Выполнение заказа
5 Закрытие заказа

- весь фокус свелся к фразе order by decode(id, 4,2.5, id).

И совсем "вишенкой на торте" может выступить снабжение нашей сортировки целочисленными порядковыми номерами следования. Вернем функцию decode() в конструкцию select запроса и применим к ней аналитическую функцию ранжирования rank():

with statuses as
(
select 1 as id, 'Открытие заказа' as text from dual union all
select 2, 'Проверка заказа' from dual union all
select 3, 'Выполнение заказа' from dual union all
select 4, 'Оплата заказа' from dual union all
select 5, 'Закрытие заказа' from dual
)
select id, text
, rank() over (order by decode(id, 4,2.5, id)) as ord
from statuses
order by ord
;

1 Открытие заказа 1
2 Проверка заказа 2
4 Оплата заказа 3
3 Выполнение заказа 4
5 Закрытие заказа 5

Одной из наиболее приятных особенностей Oracle является частая возможность решить довольно сложные задачи буквально одной строкой кода.