Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 18: учёт вариантов и комбинаций признаков
Oracle I Решение 2.2.8.b
1 |
SELECT "a id", |
|
|
|
2 |
|
"a name" |
|
|
3 |
|
COUNT "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" |
||
9 |
|
JOIN "authors" USING "a id" |
||
10 |
GROUP |
BY "a id", |
|
|
11 |
|
"a name" |
|
|
12 |
HAVING 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 Стр: 140/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.d. |
|
|
|||
|
2.2.9.С. |
|
|
|
|
|
|
|
|
|
|
|
|
s_id |
s_name |
|
|
b_namebooks_list |
|
|
1 |
Иванов И.И. |
Евгений Онегин, Основание и |
империя |
|
||
3 |
Сидоров С.С. |
|
Основание и империя |
Это разныеЭто разные |
||
|
|
|
|
|
|
|
4 |
Сидоров С.С. |
|
Язык программирования С++ |
Сидоровы!Сидоровы! |
||
|
|
|
|
|
|
|
'Vf Решение 2.2.9.a{132}.
В данном случае мы сделаем допущение о том, что первичные ключи в таблице subscriptions никогда не изменяются, т.е. минимальное значение первичного ключа действительно соответствует первому факту выдачи книги читателю. Тогда решение оказывается очень простым (что будет, если подобного допущения не делать, показано в решении 2.2.9.c{137}).
Для всех трёх СУБД рассмотрим два варианта решения, отличающиеся логикой получения минимального значения первичного ключа таблицы subscrip-
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 141/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
tions: с использованием функции MIN и с использованием сортировки по возрастанию с последующим использованием первого ряда выборки.
MySQL I Решение 2.2.9.a |
1-- Вариант 1: использование функции MIN
2SELECT 's name'
3 |
FROM |
'subscribers' |
|
|
|
4 |
WHERE |
's id' = |
'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' = |
'sb subscriber' |
5 |
|
FROM |
'subscriptions' |
6 |
|
ORDER |
BY 'sb id' ASC |
7 |
|
LIMIT |
1) |
MS SQL I Решение 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) |
|
|
Oracl |
і |
Решение 2.2.9.a |
| |
|
|
|
e |
|
|
|
||||
|
|
|
|
|
|
|
|
1 |
|
-- Вариант 1: использование функции MIN |
|
||||
2 |
|
SELECT "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: использование сортировки |
|
||||
2 |
SELECT "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 Стр: 142/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Исследование 2.2.9.EXP.A: сравним скорость работы решений этой задачи, выполнив запросы 2.2.9.а на базе данных «Большая библиотека».
Медианные значения времени после ста выполнений каждого запроса:
|
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 через нумерацию рядов приводит к очень ощутимому падению скорости работы запроса.
уЦ7
Решение 2.2.9.b{132}.
Т.к. MySQL не поддерживает общие табличные выражения и ранжирующие (оконные) функции, для этой СУБД мы рассмотрим только один вариант решения, а
для MS SQL Server и Oracle — три варианта.
MySQL I Решение 2.2.9.b |
| |
|
||
1 |
-- Вариант 1: использование подзапроса и сортировки |
|||
2 |
SELECT DISTINCT 's_id', |
|||
3 |
|
|
' s_name', |
|
4 |
|
DATEDIFF('sb_finish', 'sb_start') AS 'days' |
||
5 |
FROM |
'subscribers' |
|
|
6 |
|
JOIN 'subscriptions' |
||
|
|
ON '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 Стр: 143/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
MS SQL Решение 2.2.9.b
1-- Вариант 1: использование подзапроса и сортировки
2SELECT DISTINCT [s id]
3 |
|
[s name], |
4 |
|
DATEDIFF(day, [sb start], [sb finish] AS [days] |
5 |
FROM |
[subscribers] |
6 |
|
JOIN [subscriptions] |
7 |
|
ON [s id] = [sb subscriber] |
8 |
WHERE |
[sb is active] = 'N' |
9AND DATEDIFF(day, [sb start], [sb finish]) =
10(SELECT TOP 1 DATEDIFF(day, [sb start], [sb finish]) AS [days]
11 |
FROM |
[subscriptions] |
12 |
WHERE |
[sb is active] = 'N' |
13 |
ORDER BY |
[days] ASC) |
14 |
|
|
1-- Вариант 2: использование общего табличного выражения и функции MIN
2WITH [prepared data]
3AS (SELECT DISTINCT [s id],
4 |
|
[s name], |
5 |
|
DATEDIFF(day, [sb start] [sb finish]) AS [days] |
6 |
FROM |
[subscribers] |
7 |
|
JOIN [subscriptions] |
8 |
|
ON [s id] = [sb subscriber] |
9 |
WHERE |
[sb is active] = 'N') |
10SELECT [s id],
11[s name],
12[days]
13 |
FROM |
[prepared data] |
|
14 |
WHERE |
[days] = (SELECT MIN([days] |
|
15 |
|
FROM |
[prepared_data]) |
1-- Вариант 3: использование общего табличного выражения и ранжирования
2WITH [prepared data]
3AS (SELECT DISTINCT [s id],
4 |
|
[s name], |
5 |
|
DATEDIFF(day, [sb start] [sb finish]) AS [days] |
6 |
|
RANK() |
7 |
|
OVER ( |
8 |
|
ORDER BY |
9 |
|
DATEDIFF(day, [sb start] [sb finish]) ASC |
10 |
|
) AS [rank] |
11 |
FROM |
[subscribers] |
12 |
|
JOIN [subscriptions] |
13 |
|
ON [s id] = [sb subscriber] |
14WHERE [sb is active] = 'N')
15SELECT [s id],
16[s name],
17[days]
18 |
FROM |
[prepared data] |
19 |
WHERE |
[rank] = 1 |
В варианте 2 общее табличное выражение (строки 2-9) возвращает тот же набор данных, что и основная часть запроса в варианте 1 (строки 2-8):
s_id |
s_name |
days |
1 |
Иванов И.И. |
31 |
1 |
Иванов И.И. |
61 |
4 |
Сидоров С.С. |
61 |
Подзапрос в строках 14-15 определяет минимальное значение столбца days, которое затем используется как условие ограничения выборки в основной части запроса (строки 10-14).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 144/545