Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 12: запросы на объединение и преобразование столбцов в строки
Здесь мы применяем такой двухуровневый подход потому, что не существует простого и производительного способа устранить дублирование данных в функции LISTAGG. Некуда применить DISTINCT и нет никаких специальных встроенных ме-
ханизмов дедубликации. Альтернативные решения с регулярными выражениями или подготовкой отфильтрованных данных оказываются ещё сложнее и медленнее.
Но ничто не мешает нам разбить эту задачу на два этапа, вынеся первую часть в подзапрос (который теперь можно рассматривать как готовую таблицу), а вторую часть сделав полностью идентичной первой за исключением имени агрегируемого поля (было a_name, стало g_name).
Почему не работает одноуровневое решение (когда мы пытаемся сразу в одном SELECT получить набор авторов и набор жанров)? Итак, у нас есть данные:
b_name |
a_name |
g name |
|
Евгений Онегин |
А.С. Пушкин |
Классика |
|
А.С. Пушкин |
Поэзия |
||
|
|||
Искусство программирования |
Д. Кнут |
Классика |
|
Д. Кнут |
Программирование |
||
|
|||
Курс теоретической физики |
Е.М. Лифшиц |
Классика |
|
Л.Д. Ландау |
Классика |
||
|
|||
Основание и империя |
А. Азимов |
Фантастика |
|
|
Б. Страуструп |
Программирование |
|
Психология программирования |
Б. Страуструп |
Психология |
|
Д. Карнеги |
Программирование |
||
|
|||
|
Д. Карнеги |
Психология |
|
Сказка о рыбаке и рыбке |
А.С. Пушкин |
Классика |
|
А.С. Пушкин |
Поэзия |
||
|
|||
Язык программирования C++ |
Б. Страуструп |
Программирование |
Попытка сразу получить в виде строки список авторов и жанров выглядит так:
b_name |
a_name |
g_name |
|
Евгений Онегин |
А.С. Пушкин, |
Классика, Поэзия |
|
А.С. Пушкин |
|
||
|
|
||
Искусство программирования |
Д. Кнут, Д. Кнут |
Классика, |
|
|
Программирование |
||
|
|
||
Курс теоретической физики |
Е.М. Лифшиц, |
Классика, Классика |
|
Л.Д. Ландау |
|
||
|
|
||
Основание и империя |
А. Азимов |
Фантастика |
|
|
Б. Страуструп, |
Программирование, |
|
Психология программирования |
Б. Страуструп, |
Психология, |
|
Д. Карнеги, Д. |
Программирование, |
||
|
|||
|
Карнеги |
Психология |
|
Сказка о рыбаке и рыбке |
А.С. Пушкин, |
Классика, Поэзия |
|
А.С. Пушкин |
|
||
|
|
||
Язык программирования С++ |
Б. Страуструп |
Программирование |
А вот как работает двухходовое решение. Сначала у нас есть такой набор данных (серым фоном отмечены строки, которые при группировке превратятся в одну строку и дадут список из более чем одного автора):
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 85/545
Пример 12: запросы на объединение и преобразование столбцов в строки
b_name |
a_name |
Евгений Онегин |
А.С. Пушкин |
Сказка о рыбаке и рыбке |
А.С. Пушкин |
Основание и империя |
А. Азимов |
Психология программирования |
Д. Карнеги |
Психология программирования |
Б. Страуструп |
Язык программирования C++ |
Б. Страуструп |
Курс теоретической физики |
Л.Д. Ландау |
Курс теоретической физики |
Е.М. Лифшиц |
Искусство программирования |
Д. Кнут |
Результат выполнения первой группировки и построчного объединения:
book |
author(s) |
Евгений Онегин |
А.С. Пушкин |
Сказка о рыбаке и рыбке |
А.С. Пушкин |
Основание и империя |
А. Азимов |
Психология программирования |
Б. Страуструп, Д. Карнеги |
Язык программирования С++ |
Б. Страуструп |
Курс теоретической физики |
Е.М. Лифшиц, Л.Д. Ландау |
Искусство программирования |
Д. Кнут |
На втором шаге набор данных о каждой книге и её авторах уже позволяет проводить группировку сразу по двум полям — book и author(s), т.к. списки авторов уже подготовлены, и никакая информация о них не будет потеряна:
book |
|
author(s) |
|
g name |
Евгений Онегин |
А.С. Пушкин |
|
Классика |
|
Евгений Онегин |
А.С. Пушкин |
|
Поэзия |
|
Сказка о рыбаке и рыбке |
А.С. Пушкин |
|
Классика |
|
Сказка о рыбаке и рыбке |
А.С. Пушкин |
|
Поэзия |
|
Основание и империя |
А. Азимов |
|
Фантастика |
|
Психология программирования |
Б. Страуструп, Д. Карнеги |
Программирование |
||
Психология программирования |
Б. Страуструп, Д. Карнеги |
Психология |
||
Язык программирования С++ |
Б. Страуструп |
|
Программирование |
|
Курс теоретической физики |
|
|
|
Классика |
|
Е.М. Лифшиц, Л.Д. Ландау |
|
||
Искусство программирования |
Д. Кнут |
|
Классика |
|
Искусство программирования |
Д. Кнут |
|
Программирование |
|
И получается итоговый результат: |
|
|
||
book |
|
author(s) |
|
genre(s) |
Евгений Онегин |
|
А.С. Пушкин |
Классика, Поэзия |
|
Искусство программирования |
|
Д. Кнут |
Классика, |
|
|
|
|
Программирование |
|
Курс теоретической физики |
|
Е.М. Лифшиц, Л.Д. |
Классика |
|
|
|
Ландау |
|
|
Основание и империя |
|
А. Азимов |
Фантастика |
|
Психология программирования |
|
Б. Страуструп, Д. |
Программирование, |
|
|
|
Карнеги |
Психология |
|
Сказка о рыбаке и рыбке |
|
А.С. Пушкин |
Классика, Поэзия |
|
Язык программирования С++ |
|
Б. Страуструп |
Программирование |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 86/545
Пример 12: запросы на объединение и преобразование столбцов в строки
Задание 2.2.2.TSK.A: показать все книги с их жанрами (дублирование названий книг не допускается).
Задание 2.2.2.TSK.B: показать всех авторов со всеми написанными ими книгами и всеми жанрами, в которых они работали (дублирование имён авторов, названий книг и жанров не допускается).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 87/545
Пример 13: запросы на объединение и подзапросы с условием IN
2.2.3.Пример 13: запросы на объединение и подзапросы с условием
IN
Очень часто запросы на объединение можно преобразовать в запросы с подзапросом и ключевым словом IN (обратное преобразование тоже возможно). Рассмотрим несколько типичных примеров:
Задача 2.2.3.a{83}: показать список читателей, когда-либо бравших в библиотеке книги (использовать JOIN).
Задача 2.2.3.b{84}: показать список читателей, когда-либо бравших в библиотеке книги (не использовать JOIN).
Задача 2.2.3.c{86}: показать список читателей, никогда не бравших в библиотеке книги (использовать JOIN).
Задача 2.2.3. d{87}: показать список читателей, никогда не бравших в библиотеке книги (не использовать JOIN).
Легко заметить, что пары задач 2.2.3.а-2.2.3.Ь и 2.2.3.c-2.2.3.d как раз являются предпосылкой к использованию преобразования JOIN в IN.
|
Ожидаемый результат 2.2.3.а. |
|||
|
|
|
|
|
s_id |
s_name |
|
||
1 |
Иванов И.И. |
|
||
3 |
Сидоров С.С. |
|
||
4 |
Сидоров С.С. |
|
||
|
Ожидаемый результат 2.2.3.Ь. |
|||
|
|
|
|
|
s_id |
s_name |
|
||
1 |
Иванов И.И. |
|
||
3 |
Сидоров С.С. |
|
||
4 |
Сидоров С.С. |
|
||
|
Ожидаемый результат 2.2.3.C. |
|||
|
|
|
|
|
s_id |
s_name |
|
|
|
2 |
Петров П.П. |
|
|
|
|
Ожидаемый результат 2.2.3.d. |
|||
|
|
|||
s_id |
s_name |
|
||
2 |
Петров П.П. |
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 88/545
Пример 13: запросы на объединение и подзапросы с условием IN
Решение 2.2.3.a{83}.
Для всех трёх СУБД это решение полностью эквивалентно. Важную роль здесь играет ключевое слово DISTINCT, т.к. без него результат будет вот таким (в силу того факта, что JOIN найдёт все случаи выдачи книг каждому из читателей, а нам по условию задачи важен просто факт того, что человек хотя бы раз брал книгу):
s_id |
s_name |
1 |
Иванов И.И. |
1 |
Иванов И.И. |
1 |
Иванов И.И. |
1 |
Иванов И.И. |
1 |
Иванов И.И. |
3 |
Сидоров С.С. |
3 |
Сидоров С.С. |
3 |
Сидоров С.С. |
4 |
Сидоров С.С. |
4 |
Сидоров С.С. |
4 |
Сидоров С.С. |
Но у DISTINCT есть и опасность. Представьте, что мы решили не извле-
кать идентификатор читателя, ограничившись его именем (приведём пример только для MySQL, т.к. в MS SQL Server и Oracle ситуация совершенно идентична):
MySQL Решение 2.2.3.a (демонстрация потенциальной проблемы)
1SELECT ... DISTINCT ................. 'sTname'
2FROM 'subscribers' JOIN 'subscriptions'
3ON 's id' = 'sb subscriber'
4
Запрос вернул следующие данные:
s_name
Иванов И.И. Сидоров С.С.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 89/545