Есть список элементов - пусть чисел, - которые могут повторяться; надо разбить их на группы, в которых одинаковых элементов исходного списка не будет.
Например, мы выполняем задачу краулинга, т.е. сбора информации с сайтов. На некотором сайте нас интересуют несколько страниц, но доменное имя верхнего уровня у них одинаковое. Мы не хотим, чтоб практически в одно и то же время (группа) с одного и того же сайта закачивались несколько его страниц - пусть первая страница будет прочитана в момент времени 1 (в рамках первой группы), а вторая страница - в момент времени 2 (в рамках второй группы). Решение хотим получить на SQL, автоматическое, универсальное и, желательно, в один запрос. Ну как всегда.
Сначала генерируем исходные данные. Пусть это будут числа. Поскольку генерировать детерминированно нам не очень интересно, генерируем в виде последовательности случайных чисел. Например, 20 чисел от 1 до 20.
Внимание! Поскольку генератор у нас случайный, все результаты будут далее разными - мы обсуждаем метод, а не фактические значения.
-- Генератор исходных данных:
select level as lv, round(dbms_random.value(1, 20)) as v
from dual
connect by level <= 20
;
1 8
2 17
3 2
...
19 2
20 17
Мы получили номер строки lv (который можем считать первичным ключом - id, rowid) и смысловое значение v, которое будем распихивать по группам.
Теперь пронумеруем каждое появление одинакового значения v при сортировке по ключу lv, т.е. по порядку:
-- Генератор исходных данных:
with tab as
(
select level as lv, round(dbms_random.value(1, 20)) as v from dual
connect by level <= 20
)
-- Смысловой запрос:
select lv, v, row_number() over (partition by v order by lv) as rn from tab
;
2 2 1
15 2 2
12 3 1
17 3 2
18 3 3
1 5 1
...
8 18 1
3 19 1
11 19 2
Обратите внимание: несмотря на то, что генератор исходных данных дает нам строго сортированные по ключу lv строки, результат сортированным не является. Это правильно: сортировку результата мы не заказывали, а как там Oracle реально выполнял запрос - это целиком в его компетенции.
Нам остается только поменять порядок столбцов и правильно отсортировать результат:
-- Генератор исходных данных:
with tab as
(
select level as lv, round(dbms_random.value(1, 20)) as v from dual
connect by level <= 20
)
-- Смысловой запрос:
select rn, v
from
(
select lv, v, row_number() over (partition by v order by lv) as rn from tab
)
order by rn, v
;
1 2 -- группа 1 началась
1 3
1 5
1 6
...
1 17
1 19 -- группа 1 закончилась
2 3 -- группа 2 началась
2 5
...
2 17 -- группа 2 закончилась
3 5 -- группа 3 началась
3 17 -- группа 3 - и задача в целом - закончились
- здесь мы отсортировали по группе (первый столбец) и, для наглядности, по значению; вторую сортировку точно так же могли бы выполнить по ключу, что, в принципе, было бы даже более естественно, но менее наглядно.
В пределах групп повторяющихся элементов исходного списка нет.
Применив агрегирующую функцию listagg(), запишем еще более наглядно в виде CSV:
-- Генератор исходных данных:
with tab as
(
select level as lv, round(dbms_random.value(1, 20)) as v from dual
connect by level <= 20
)
-- Смысловой запрос:
select distinct rn, listagg(v, ', ') within group (order by v) over (partition by rn) as list
from
(
select lv, v, row_number() over (partition by v order by lv) as rn from tab
)
order by rn
;
1 4, 6, 7, 8, 10, 12, 14, 16, 18, 19, 20
2 4, 6, 7, 12, 16, 20
3 6, 7, 16
Написав, по сути, запрос из одной строки на SQL, мы решили достаточно сложную комбинационную задачу. "Магия" состоит в аналитической функции - в данном случае в функции row_number() over ().
Ее параметрами являются два выражения, предваряемые ключевыми словами
- partition by,
- order by.
Ее вербальный смысл состоит в следующем: "Пронумеруй, пожалуйста, строки в порядке, определяемом ключом lv, сбрасывая счетчик при изменении значения v". Или, другими словами, например так: "Отсортируй исходный массив сначала по значениям v, затем по ключу lv; пронумеруй строки в порядке возрастания lv, но сбрасывай счетчик при изменении значения v".
Аналитические функции, равно как и иерархические, - мощное средство Oracle, позволяющее эффективно работать с данными.