Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 12: запросы на объединение и преобразование столбцов в строки

Чтобы было проще пояснять, перепишем эту часть запроса в упрощённом

виде:

Часть FOR XML PATH(''), TYPE относится к SELECT и говорит СУБД представить результат выборки как часть XML-документа для пути '' (пустой строки), т.е. — просто в виде обычной строки, хоть пока и выглядящей для СУБД как «некие данные в формате XML».

Промежуточный результат уже выглядит проще:

STUFF (({XML-строка}).value('.', 'nvarchar(max)'), 1, 2, '')

Метод value(путь, тип_данных) применяется в MS SQL Server для извлечения строкового представления из XML. Не вдаваясь в подробности, скажем, что первый параметр путь, равный . (точка) говорит, что нужно взять текущий элемент XML-документа (у нас «текущим элементом» является наша строка), а параметр тип_данных выставлен в nvarchar(max) как один из самых удобных в MS SQL Server типов для хранения длинных строк.

Результат уже почти готов:

STUFF ({строка с ", " в начале}, 1, 2, '')

Остаётся только избавиться от символов «, » в начале каждого списка авторов. Это и делает функция STUFF, заменяя в строке «{строка с ", " в начале}» символы с первого по второй (см. второй и третий параметры функции, равные 1 и 2 соответственно) на пустую строку (см. четвёртый параметр функции, равный, '').

И на этом с MS SQL Server — всё, переходим к Oracle. Здесь реализуется уже третий вариант решения:

Oracle Решение 2.2.2.a

1SELECT "b_name" AS "book",

2UTL_RAW.CAST_TO_NVARCHAR2

3(

10)

11AS "author(s)"

12FROM "books"

13JOIN "m2m_books_authors" USING ("b_id")

14JOIN "authors" USING("a_id")

15GROUP BY "b_id",

16"b_name"

ВOracle решение получается проще, чем в MS SQL Server, т.к. здесь (начиная с версии 11gR2 поддерживается функция LISTAGG, выполняющая практически

то же самое, что и GROUP_CONCAT в MySQL. Таким образом, строки 4-8 запроса отвечают за «разворачивание в строку части столбца».

Вызовы метода UTL_RAW.CAST_TO_RAW в строках 6-7 и метода UTL_RAW.CAST_TO_NVARCHAR2 в строке 2 нужны для того, чтобы сначала представить текстовые данные в формате, который обрабатывается без интерпретации значений байтов, а затем вернуть обработанные данные из этого формата в тек-

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 75/545

Пример 12: запросы на объединение и преобразование столбцов в строки

стовый вид. Без этого преобразования информация об авторах превращается в нечитаемый набор спецсимволов.

В остальном поведение Oracle при решении этой задачи вполне эквивалентно поведению MySQL.

Решение 2.2.2.b{71}.

MySQL Решение 2.2.2.b

1SELECT `b_name`

2AS `book`,

3GROUP_CONCAT(DISTINCT `a_name` ORDER BY `a_name` SEPARATOR ', ')

4AS `author(s)`,

5GROUP_CONCAT(DISTINCT `g_name` ORDER BY `g_name` SEPARATOR ', ')

6AS `genre(s)`

7 FROM `books`

8JOIN `m2m_books_authors` USING(`b_id`)

9JOIN `authors` USING(`a_id`)

10JOIN `m2m_books_genres` USING(`b_id`)

11JOIN `genres` USING(`g_id`)

12GROUP BY `b_id`

13ORDER BY `b_name`

Решение 2.2.2.b для MySQL лишь чуть-чуть сложнее решения 2.2.2.a: здесь появился ещё один вызов GROUP_CONCAT (строки 5-6), и в обоих вызовах GROUP_CONCAT появилось ключевое слово DISTINCT, чтобы избежать дублирования информации об авторах и жанрах, которое появляется объективным образом в процессе выполнения объединения:

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 76/545

Пример 12: запросы на объединение и преобразование столбцов в строки

MS SQL Решение 2.2.2.b

1WITH [prepared_data]

2AS (SELECT [books].[b_id],

15)

16SELECT [outer].[b_name]

17AS [book],

18STUFF ((SELECT DISTINCT ', ' + [inner].[a_name]

24AS [author(s)],

25STUFF ((SELECT DISTINCT ', ' + [inner].[g_name]

33GROUP BY [outer].[b_id],

34[outer].[b_name]

Данное решение для MS SQL Server тоже строится на основе решения 2.2.2.a{72} и отличается чуть большим количеством JOIN в общем табличном выражении (добавились строки 11-14), а также (по тем же причинам, что и в MySQL) добавлением DISTINCT в строках 18 и 25. Блоки строк 18-24 и 25-31 отличаются только именем выбираемого поля (в первом случае — a_name, во втором — g_name).

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 77/545

Пример 12: запросы на объединение и преобразование столбцов в строки

Oracle Решение 2.2.2.b

1SELECT "book", "author(s)",

2UTL_RAW.CAST_TO_NVARCHAR2

3(

10)

11AS "genre(s)"

12FROM

13(

14SELECT "b_id", "b_name" AS "book",

26JOIN "m2m_books_authors" USING ("b_id")

27JOIN "authors" USING("a_id")

28GROUP BY "b_id",

30) "first_level"

31JOIN "m2m_books_genres" USING ("b_id")

32JOIN "genres" USING("g_id")

33GROUP BY "b_id",

34"book",

35"author(s)"

Здесь подзапрос в строках 13-30 представляет собой решение 2.2.2.a{72}, в котором в SELECT добавлено поле b_id, чтобы оно было доступно для дальнейших операций JOIN и GROUP BY. Если переписать запрос с учётом этой информации, получается:

Oracle Решение 2.2.2.b

1SELECT "book", "author(s)",

2UTL_RAW.CAST_TO_NVARCHAR2

3(

10)

11AS "genre(s)"

12FROM {данные_из_решения_2_2_2_a + поле b_id}

13JOIN "m2m_books_genres" USING ("b_id")

14JOIN "genres" USING("g_id")

15GROUP BY "b_id",

16"book",

17"author(s)"

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 78/545

Пример 12: запросы на объединение и преобразование столбцов в строки

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

Но ничто не мешает нам разбить эту задачу на два этапа, вынеся первую часть в подзапрос (который теперь можно рассматривать как готовую таблицу), а вторую часть сделав полностью идентичной первой за исключением имени агрегируемого поля (было a_name, стало g_name).

Почему не работает одноуровневое решение (когда мы пытаемся сразу в одном SELECT получить набор авторов и набор жанров)? Итак, у нас есть данные:

Попытка сразу получить в виде строки список авторов и жанров выглядит так:

А вот как работает двухходовое решение. Сначала у нас есть такой набор данных (серым фоном отмечены строки, которые при группировке превратятся в одну строку и дадут список из более чем одного автора):

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 79/545