Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 18: учёт вариантов и комбинаций признаков
Снова доработаем подзапрос (строки 4-8) и посмотрим, какие данные он возвращает:
MySQL Решение 2.2.8.b (модифицированный код подзапроса)
1 |
|
SELECT DISTINCT `a_id`, |
2 |
|
`a_name`, |
3 |
|
`g_id`, |
4 |
|
`g_name` |
5 |
|
FROM `m2m_books_genres` |
|
|
|
6JOIN `m2m_books_authors` USING (`b_id`)
7JOIN `authors` USING (`a_id`)
8JOIN `genres` USING (`g_id`)
9ORDER BY `a_id`,
10`g_id`
Данные получаются такие (список без повторений всех жанров, в которых работал автор):
a_id |
a_name |
g_id |
g_name |
1 |
Д. Кнут |
2 |
Программирование |
1 |
Д. Кнут |
5 |
Классика |
2 |
А. Азимов |
6 |
Фантастика |
3 |
Д. Карнеги |
2 |
Программирование |
3 |
Д. Карнеги |
3 |
Психология |
4 |
Л.Д. Ландау |
5 |
Классика |
5 |
Е.М. Лифшиц |
5 |
Классика |
6 |
Б. Страуструп |
2 |
Программирование |
6 |
Б. Страуструп |
3 |
Психология |
7 |
А.С. Пушкин |
1 |
Поэзия |
7 |
А.С. Пушкин |
5 |
Классика |
В основной части запроса (строки 1-3 и 9-11) остаётся только посчитать количество элементов в списке жанров для каждого автора, а затем оставить в выборке тех авторов, у которых это количество больше единицы. Так мы получаем итоговый результат.
Решение этой задачи для MS SQL Server и Oracle отличается от решения для MySQL только синтаксическими нюансами.
MS SQL Решение 2.2.8.b
1SELECT [prepared_data].[a_id],
2[a_name],
3COUNT([g_id]) AS [genres_count]
4 |
|
FROM (SELECT |
DISTINCT [m2m_books_authors].[a_id], |
|
5 |
|
|
|
[m2m_books_genres].[g_id] |
6 |
|
FROM |
[m2m_books_genres] |
|
7 |
|
|
JOIN |
[m2m_books_authors] |
8 |
|
|
ON |
[m2m_books_genres].[b_id] = [m2m_books_authors].[b_id]) |
9AS
10[prepared_data]
11JOIN [authors]
12ON [prepared_data].[a_id] = [authors].[a_id]
13GROUP BY [prepared_data].[a_id],
14[a_name]
15HAVING COUNT([g_id]) > 1
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 130/545
Пример 18: учёт вариантов и комбинаций признаков
Oracle Решение 2.2.8.b
1SELECT "a_id",
2"a_name",
3COUNT("g_id") AS "genres_count"
4 |
|
FROM (SELECT |
DISTINCT "a_id", |
5 |
|
|
"g_id" |
6 |
|
FROM |
"m2m_books_genres" |
7 |
|
|
JOIN "m2m_books_authors" USING ("b_id") |
8) "prepared_data"
9JOIN "authors" USING ("a_id")
10GROUP BY "a_id",
11"a_name"
12HAVING COUNT("g_id") > 1
Задание 2.2.8.TSK.A: переписать решения задач 2.2.8.a{127} и 2.2.8.b{129} для MS SQL Server и Oracle с использованием общих табличных выражений.
Задание 2.2.8.TSK.B: показать читателей, бравших самые разножанровые книги (т.е. книги, одновременно относящиеся к максимальному количеству жанров).
Задание 2.2.8.TSK.C: показать читателей наибольшего количества жанров (не важно, брали ли они книги, каждая из которых относится одновременно к многим жанрам, или же просто много книг из разных жанров, каждая из которых относится к небольшому количеству жанров).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 131/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
2.2.9.Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Задача 2.2.9.a{132}: показать читателя, первым взявшего в библиотеке книгу.
Задача 2.2.9.b{134}: показать читателя (или читателей, если их окажется несколько), быстрее всего прочитавшего книгу (учитывать только случаи, когда книга возвращена).
Задача 2.2.9.c{137}: показать, какую книгу (или книги, если их несколько) каждый читатель взял в первый день своей работы с библиотекой.
Задача 2.2.9.d{140}: показать первую книгу, которую каждый из читателей взял в библиотеке.
Ожидаемый результат 2.2.9.a.
s_name
Иванов И.И.
Ожидаемый результат 2.2.9.b.
s_id |
s_name |
|
days |
|
|
|
|
1 |
Иванов И.И. |
|
31 |
|
|
|
|
|
Ожидаемый результат 2.2.9.c. |
|
|
||||
|
|
|
|
|
|
|
|
s_id |
s_name |
|
|
books_list |
|
|
|
1 |
Иванов И.И. |
Евгений Онегин, Основание и империя |
|
||||
3 |
Сидоров С.С. |
|
Основание и империя |
|
Это разные |
||
4 |
Сидоров С.С. |
|
Язык программирования С++ |
|
Сидоровы! |
||
|
Ожидаемый результат 2.2.9.d. |
|
|
||||
|
|
|
|
|
|
|
|
s_id |
s_name |
|
|
b_name |
|
|
|
1 |
Иванов И.И. |
|
Евгений Онегин |
|
|
||
3 |
Сидоров С.С. |
|
Основание и империя |
Это разные |
|||
4 |
Сидоров С.С. |
|
Язык программирования С++ |
Сидоровы! |
|||
Решение 2.2.9.a{132}.
В данном случае мы сделаем допущение о том, что первичные ключи в таблице subscriptions никогда не изменяются, т.е. минимальное значение первичного ключа действительно соответствует первому факту выдачи книги читателю. Тогда решение оказывается очень простым (что будет, если подобного допущения не делать, показано в решении 2.2.9.c{137}).
Для всех трёх СУБД рассмотрим два варианта решения, отличающиеся логикой получения минимального значения первичного ключа таблицы subscrip-
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 132/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
tions: с использованием функции MIN и с использованием сортировки по возрастанию с последующим использованием первого ряда выборки.
MySQL Решение 2.2.9.a
1-- Вариант 1: использование функции MIN
2SELECT `s_name`
3 |
|
FROM |
`subscribers` |
|
|
4 |
|
WHERE |
`s_id` = (SELECT |
`sb_subscriber` |
|
5 |
|
|
FROM |
`subscriptions` |
|
6 |
|
|
WHERE |
`sb_id` = (SELECT |
MIN(`sb_id`) |
7 |
|
|
|
FROM |
`subscriptions`)) |
|
|
|
|
|
|
1-- Вариант 2: использование сортировки
2SELECT `s_name`
3 |
|
FROM |
`subscribers` |
|
4 |
|
WHERE |
`s_id` = (SELECT |
`sb_subscriber` |
5 |
|
|
FROM |
`subscriptions` |
6 |
|
|
ORDER |
BY `sb_id` ASC |
7 |
|
|
LIMIT 1) |
|
|
|
|
|
|
MS SQL Решение 2.2.9.a
1-- Вариант 1: использование функции MIN
2SELECT [s_name]
3 |
|
FROM |
[subscribers] |
|
|
4 |
|
WHERE |
[s_id] = (SELECT |
[sb_subscriber] |
|
5 |
|
|
FROM |
[subscriptions] |
|
6 |
|
|
WHERE |
[sb_id] = (SELECT |
MIN([sb_id]) |
7 |
|
|
|
FROM |
[subscriptions])) |
|
|
|
|
|
|
1-- Вариант 2: использование сортировки
2SELECT [s_name]
3 |
|
FROM |
[subscribers] |
|
4 |
|
WHERE |
[s_id] = (SELECT |
TOP 1 [sb_subscriber] |
5 |
|
|
FROM |
[subscriptions] |
6 |
|
|
ORDER |
BY [sb_id] ASC) |
|
|
|
|
|
Oracle Решение 2.2.9.a
1-- Вариант 1: использование функции MIN
2SELECT "s_name"
3 |
|
FROM |
"subscribers" |
|
|
4 |
|
WHERE |
"s_id" = (SELECT |
"sb_subscriber" |
|
5 |
|
|
FROM |
"subscriptions" |
|
6 |
|
|
WHERE |
"sb_id" = (SELECT |
MIN("sb_id") |
7 |
|
|
|
FROM |
"subscriptions")) |
|
|
|
|
|
|
1-- Вариант 2: использование сортировки
2SELECT "s_name"
3 |
|
FROM |
"subscribers" |
|
|
4 |
|
WHERE |
"s_id" = (SELECT |
"sb_subscriber" |
|
5 |
|
|
FROM |
(SELECT |
"sb_subscriber", |
6 |
|
|
|
|
ROW_NUMBER() |
7 |
|
|
|
|
OVER( |
8 |
|
|
|
|
ORDER BY "sb_id" ASC) AS "rn" |
9 |
|
|
|
FROM |
"subscriptions") |
10 |
|
|
WHERE |
"rn" = 1) |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 133/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Исследование 2.2.9.EXP.A: сравним скорость работы решений этой задачи, выполнив запросы 2.2.9.a на базе данных «Большая библиотека».
Медианные значения времени после ста выполнений каждого запроса:
|
MySQL |
MS SQL Server |
Oracle |
Использование функ- |
0.001 |
0.020 |
2.876 |
ции MIN |
|
|
|
Использование сор- |
0.001 |
0.018 |
10.330 |
тировки |
|
|
|
В MySQL и MS SQL Server оба варианта запросов работают с примерно сопоставимой скоростью, но в Oracle эмуляция конструкций LIMIT/TOP через нумерацию рядов приводит к очень ощутимому падению скорости работы запроса.
Решение 2.2.9.b{132}.
Т.к. MySQL не поддерживает общие табличные выражения и ранжирующие (оконные) функции, для этой СУБД мы рассмотрим только один вариант решения,
а для MS SQL Server и Oracle — три варианта.
MySQL Решение 2.2.9.b
1-- Вариант 1: использование подзапроса и сортировки
2SELECT DISTINCT `s_id`,
3 |
|
|
`s_name`, |
4 |
|
|
DATEDIFF(`sb_finish`, `sb_start`) AS `days` |
5 |
|
FROM |
`subscribers` |
|
|
|
|
6JOIN `subscriptions`
7ON `s_id` = `sb_subscriber`
8 |
|
WHERE `sb_is_active` = 'N' |
|
9 |
|
AND DATEDIFF(`sb_finish`, `sb_start`) = |
|
10 |
|
(SELECT |
DATEDIFF(`sb_finish`, `sb_start`) AS `days` |
11 |
|
FROM |
`subscriptions` |
12 |
|
WHERE |
`sb_is_active` = 'N' |
13 |
|
ORDER |
BY `days` ASC |
14 |
|
LIMIT |
1) |
Подзапрос в строках 10-14 возвращает информацию о минимальном количестве дней, за которые была возвращена книга:
days
31
Основная часть запроса в строках 2-8 получает информацию обо всех читателях и количестве дней, которые каждый из читателей держал у себя каждую взятую и возвращённую им книгу:
s_id |
s_name |
days |
1 |
Иванов И.И. |
31 |
1 |
Иванов И.И. |
61 |
4 |
Сидоров С.С. |
61 |
Условие в строке 9 оставляет из этого набора только те записи, в которых количество дней равно количеству, определённому подзапросом в строках 10-14. Так мы получаем конечный набор данных.
Вариант 1 решений для MS SQL Server и Oracle построен аналогичным образом.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 134/545