В статье обсуждаются оригинальное использование SQL преобразований

использование аналитического sql , иерархических запросов, оператора modal 

Автор публикации мой коллега Доброквашин Михаил

Задача:

Есть набор данных из 2-х полей. Первое поле повторяется, второе уникальное (например, код подразделения и ФИО работника). Надо получить набор данных, где в первом поле был бы код, а во втором перечисление через запятую имен сотрудников.

Решение 1
Используем функцию LAG, иерархический запрос и функцию SYS_CONNECT_BY PATH
Сначала при помощи функции LAG получим значения поля EMP предыдущей строки.

select
tab.*, LAG (tab.emp) OVER(partition by tab.dep order by tab.dep, tab.emp) prev
from
(
select 1 dep, 'A' emp from dual
union
select 1 dep, 'B' emp from dual
union
select 1 dep, 'C' emp from dual
union
select 1 dep, 'D' emp from dual
union
select 2 dep, 'E' emp from dual
union
select 2 dep, 'F' emp from dual
union
select 3 dep, 'G' emp from dual
union
select 3 dep, 'H' emp from dual
union
select 4 dep, 'J' emp from dual
) tab

order by 1

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

select distinct
t.dep, sys_connect_by_path(emp,',') as path
from
(
select
tab.*, LAG (tab.emp) OVER(partition by tab.dep order by tab.dep, tab.emp) prev
from
(
select 1 dep, 'A' emp from dual
union
select 1 dep, 'B' emp from dual
union
select 1 dep, 'C' emp from dual
union
select 1 dep, 'D' emp from dual
union
select 2 dep, 'E' emp from dual
union
select 2 dep, 'F' emp from dual
union
select 3 dep, 'G' emp from dual
union
select 3 dep, 'H' emp from dual
union
select 4 dep, 'J' emp from dual
) tab
) t
CONNECT BY PRIOR t.emp = t.prev
START WITH t.prev is NULL
order by 1

Теперь можно сгруппировать строки по подразделениям, выделив максимальное значение PATH.

select dep, SUBSTR(MAX(path),2)
from
(select distinct
t.dep, sys_connect_by_path(emp,',') as path
from
(
select
tab.*, LAG (tab.emp) OVER(partition by tab.dep order by tab.dep, tab.emp) prev
from
(
select 1 dep, 'A' emp from dual
union
select 1 dep, 'B' emp from dual
union
select 1 dep, 'C' emp from dual
union
select 1 dep, 'D' emp from dual
union
select 2 dep, 'E' emp from dual
union
select 2 dep, 'F' emp from dual
union
select 3 dep, 'G' emp from dual
union
select 3 dep, 'H' emp from dual
union
select 4 dep, 'J' emp from dual
) tab
) t
CONNECT BY PRIOR t.emp = t.prev
START WITH t.prev is NULL
order by 1
) t2

GROUP BY dep
order by 1

Решение 2

Для достижения того же результата можно использовать XML функцию XMLAGG()

select dep, CAST(XMLAGG(XMLELEMENT("emp",emp)) as VARCHAR2(1000) )
from
(
select 1 dep, 'A' emp from dual
union
select 1 dep, 'B' emp from dual
union
select 1 dep, 'C' emp from dual
union
select 1 dep, 'D' emp from dual
union
select 2 dep, 'E' emp from dual
union
select 2 dep, 'F' emp from dual
union
select 3 dep, 'G' emp from dual
union
select 3 dep, 'H' emp from dual
union
select 4 dep, 'J' emp from dual
) tab
group by dep

Далее результат можно причесать функциями REPLACE, убрав теги и , и заменив их на запятые.

Решение 3

Также для решения этой задачи можно использовать конструкцию MODEL, появившуюся в десятой версии ORACLE.

select *
from (
select *
from
(select dep, emp, lead(emp) over (partition by dep order by emp ) prev
from
(
select 1 dep, 'A' emp from dual
union
select 1 dep, 'B' emp from dual
union
select 1 dep, 'C' emp from dual
union
select 1 dep, 'D' emp from dual
union
select 2 dep, 'E' emp from dual
union
select 2 dep, 'F' emp from dual
union
select 3 dep, 'G' emp from dual
union
select 3 dep, 'H' emp from dual
union
select 4 dep, 'J' emp from dual
) tab
)
model
dimension by (
dep
, row_number() over( partition by dep order by emp desc) rn
)

measures (
emp
, prev
, cast(null as VARCHAR2(2000)) list
, count(*) over (partition by dep) cnt
, row_number() over (partition by dep order by emp desc) rnk
)

rules (
list[any, any]
order by dep, rn = case when prev[cv(), cv()] is null
then emp[cv(), cv()]
else emp[cv(),cv()] ||','||list[cv(), rnk[cv(),cv()] -1]
end
)
order by dep, rnk
)

получаем

Это решение похоже на первое. Только здесь пришлось использовать вместо функции LAG функцию LEAD и отсортировать строки первоначального набора данных в обратном порядке по EMP, для того чтобы в результате получить не "D,C,B,A", а "A,B,C,D".
Промежуточный результат здесь такой же как в первом решении - набор строк с новой колонкой, в которой хранятся значения предыдущей строки. Однако дальнейшая обработка этих данных другая. Здесь применяется конструкция MODEL. В MESUARES описываются новые столбцы набора данных, столбец LIST при этом вычисляется по некоторому алгоритму, который описывается в RULES. Там осуществляется проход по всем строкам набора данных и если предыдущее значение поля PREV не пустое, то оно добавляется к значению поля EMP через запятую.
Добавим в конец условие
Where cnt = rn
Чтобы отсечь лишние строки с промежуточными результатами.
В результате получается такой набор данных, который практически то что и требовалось получить