Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Основная часть запроса в строках 24-31 является четвёртым шагом, на котором по имеющимся идентификаторам определяются имя читателя и название книги. Так получается финальный результат.
MS SQL Решение 2.2.9.d
1-- Вариант 2: решение в два шага с использованием ранжирования
2WITH [step_1]
3AS (SELECT [sb_subscriber],
4 |
|
|
[sb_start], |
5 |
|
|
[sb_id], |
6 |
|
|
[sb_book], |
7 |
|
|
ROW_NUMBER() |
8 |
|
|
OVER ( |
9 |
|
|
PARTITION BY [sb_subscriber] |
10 |
|
|
ORDER BY [sb_subscriber] ASC) |
11 |
|
|
AS [rank_by_subscriber], |
12 |
|
|
ROW_NUMBER() |
13 |
|
|
OVER ( |
14 |
|
|
PARTITION BY [sb_subscriber], [sb_start] |
15 |
|
|
ORDER BY [sb_subscriber], [sb_start] ASC) |
16 |
|
|
AS [rank_by_date] |
17 |
|
FROM |
[subscriptions]) |
18SELECT [s_id],
19[s_name],
20[b_name]
21 FROM [step_1]
22JOIN [subscribers]
23ON [sb_subscriber] = [s_id]
24JOIN [books]
25ON [sb_book] = [b_id]
26WHERE [rank_by_subscriber] = 1 AND [rank_by_date] = 1
Общее табличное выражение в строках 2-17 возвращает те же данные, что и подзапрос в решении для MySQL — информацию о выдаче читателям книг, ранжированную по номеру визита читателя в библиотеку и номеру выдачи книги в рамках каждого визита:
sb_subscriber |
sb_start |
sb_id |
sb_book |
rank_by_subscriber |
rank_by_date |
1 |
2011-01-12 |
2 |
1 |
1 |
1 |
1 |
2011-01-12 |
100 |
3 |
2 |
2 |
1 |
2012-06-11 |
42 |
2 |
3 |
1 |
1 |
2014-08-03 |
61 |
7 |
4 |
1 |
1 |
2015-10-07 |
95 |
4 |
5 |
1 |
3 |
2012-05-17 |
3 |
3 |
1 |
1 |
3 |
2014-08-03 |
62 |
5 |
2 |
1 |
3 |
2014-08-03 |
86 |
1 |
3 |
2 |
4 |
2012-06-11 |
57 |
5 |
1 |
1 |
4 |
2015-10-07 |
91 |
1 |
2 |
1 |
4 |
2015-10-08 |
99 |
4 |
3 |
1 |
Основная часть запроса в строках 18-26 оставляет из этого набора только каждую первую выдачу в каждом первом визите и получает по идентификаторам читателей и книг их имена и названия. Так получается финальный результат.
В MySQL бессмысленно было реализовывать третий вариант решения, т.к. группировка там нарушает логику нумерации рядов. В MS SQL Server и Oracle этого ограничения нет, потому возможно ещё одно решение — третий вариант.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 145/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
MS SQL Решение 2.2.9.d
1-- Вариант 3: решение в три шага с использованием ранжирования
2-- и группировки
3WITH [step_1]
4AS (SELECT [sb_subscriber],
5 |
|
|
[sb_start], |
|
6 |
|
|
MIN([sb_id]) |
AS [min_sb_id], |
7 |
|
|
RANK() |
|
8 |
|
|
OVER ( |
|
9 |
|
|
PARTITION BY [sb_subscriber] |
|
10 |
|
|
ORDER BY [sb_start] ASC) AS [rank] |
|
11 |
|
FROM |
[subscriptions] |
|
12 |
|
GROUP |
BY [sb_subscriber], |
|
13 |
|
|
[sb_start]), |
|
14[step_2]
15AS (SELECT [subscriptions].[sb_subscriber],
16 |
|
|
[subscriptions].[sb_book] |
|
17 |
|
FROM |
[subscriptions] |
|
18 |
|
|
JOIN |
[step_1] |
19 |
|
|
ON |
[subscriptions].[sb_id] = [step_1].[min_sb_id] |
20WHERE [rank] = 1)
21SELECT [s_id],
22[s_name],
23[b_name]
24 FROM [step_2]
25JOIN [subscribers]
26ON [sb_subscriber] = [s_id]
27JOIN [books]
28ON [sb_book] = [b_id]
Первое общее табличное выражение (строки 3-13 запроса) сразу выясняет наименьшее значение первичного ключа таблицы subscriptions и ранжирует полученные данные по номеру визита читателя в библиотеку:
sb_subscriber |
sb_start |
min_sb_id |
rank |
1 |
2011-01-12 |
2 |
1 |
1 |
2012-06-11 |
42 |
2 |
1 |
2014-08-03 |
61 |
3 |
1 |
2015-10-07 |
95 |
4 |
3 |
2012-05-17 |
3 |
1 |
3 |
2014-08-03 |
62 |
2 |
4 |
2012-06-11 |
57 |
1 |
4 |
2015-10-07 |
91 |
2 |
4 |
2015-10-08 |
99 |
3 |
Второе общее табличное выражение убирает лишние данные и, обладая информацией об идентификаторе записи из таблицы subscriptions, извлекает оттуда значение поля sb_book, т.е. идентификатор книги. В результате получается:
sb_subscriber |
sb_book |
1 |
1 |
3 |
3 |
4 |
5 |
Основная часть запроса в строках 21-28 нужна только для замены идентификаторов читателей и книг на их имена и названия. Так получается итоговый результат.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 146/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Решение для Oracle аналогично решению для MS SQL Server и отличается лишь незначительными синтаксическими деталями.
Oracle Решение 2.2.9.d
1-- Вариант 1: решение в четыре шага без использования ранжирования
2WITH "step_1"
3AS (SELECT "sb_subscriber",
4 |
|
|
MIN("sb_start") |
AS "min_sb_start" |
5 |
|
FROM |
"subscriptions" |
|
6 |
|
GROUP |
BY "sb_subscriber"), |
|
7"step_2"
8AS (SELECT "subscriptions"."sb_subscriber",
9 |
|
|
MIN("sb_id") AS "min_sb_id" |
10 |
|
FROM |
"subscriptions" |
11 |
|
|
JOIN "step_1" |
12 |
|
|
ON "subscriptions"."sb_subscriber" = |
13 |
|
|
"step_1"."sb_subscriber" |
14 |
|
|
AND "subscriptions"."sb_start" = |
15 |
|
|
"step_1"."min_sb_start" |
16 |
|
GROUP |
BY "subscriptions"."sb_subscriber", |
17 |
|
|
"min_sb_start"), |
|
|
|
|
18"step_3"
19AS (SELECT "subscriptions"."sb_subscriber",
20 |
|
|
"sb_book" |
|
21 |
|
FROM |
"subscriptions" |
|
22 |
|
|
JOIN |
"step_2" |
23 |
|
|
ON |
"subscriptions"."sb_id" = "step_2"."min_sb_id") |
24SELECT "s_id",
25"s_name",
26"b_name"
27 FROM "step_3"
28JOIN "subscribers"
29ON "sb_subscriber" = "s_id"
30JOIN "books"
31ON "sb_book" = "b_id"
1-- Вариант 2: решение в два шага с использованием ранжирования
2WITH "step_1"
3AS (SELECT "sb_subscriber",
4 |
|
|
"sb_start", |
5 |
|
|
"sb_id", |
6 |
|
|
"sb_book", |
7 |
|
|
ROW_NUMBER() |
8 |
|
|
OVER ( |
9 |
|
|
PARTITION BY "sb_subscriber" |
10 |
|
|
ORDER BY "sb_subscriber" ASC) |
11 |
|
|
AS "rank_by_subscriber", |
12 |
|
|
ROW_NUMBER() |
13 |
|
|
OVER ( |
14 |
|
|
PARTITION BY "sb_subscriber", "sb_start" |
15 |
|
|
ORDER BY "sb_subscriber", "sb_start" ASC) |
16 |
|
|
AS "rank_by_date" |
17 |
|
FROM |
"subscriptions") |
18SELECT "s_id",
19"s_name",
20"b_name"
21 FROM "step_1"
22JOIN "subscribers"
23ON "sb_subscriber" = "s_id"
24JOIN "books"
25ON "sb_book" = "b_id"
26WHERE "rank_by_subscriber" = 1 AND "rank_by_date" = 1
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 147/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Oracle Решение 2.2.9.d (продолжение)
1-- Вариант 3: решение в три шага с использованием ранжирования
2-- и группировки
3WITH "step_1"
4AS (SELECT "sb_subscriber",
5 |
|
|
"sb_start", |
|
6 |
|
|
MIN("sb_id") |
AS "min_sb_id", |
7 |
|
|
RANK() |
|
8 |
|
|
OVER ( |
|
9 |
|
|
PARTITION BY "sb_subscriber" |
|
10 |
|
|
ORDER BY "sb_start" ASC) AS "rank" |
|
11 |
|
FROM |
"subscriptions" |
|
12 |
|
GROUP |
BY "sb_subscriber", |
|
13 |
|
|
"sb_start"), |
|
14"step_2"
15AS (SELECT "subscriptions"."sb_subscriber",
16 |
|
|
"subscriptions"."sb_book" |
|
17 |
|
FROM |
"subscriptions" |
|
18 |
|
|
JOIN |
"step_1" |
19 |
|
|
ON |
"subscriptions"."sb_id" = "step_1"."min_sb_id" |
20WHERE "rank" = 1)
21SELECT "s_id",
22"s_name",
23"b_name"
24 FROM "step_2"
25JOIN "subscribers"
26ON "sb_subscriber" = "s_id"
27JOIN "books"
28ON "sb_book" = "b_id"
Исследование 2.2.9.EXP.С: сравним скорость работы решений этой задачи, выполнив запросы 2.2.9.d на базе данных «Большая библиотека».
Медианные значения времени после ста выполнений каждого запроса:
|
MySQL |
MS SQL Server |
Oracle |
Вариант 1 |
87135.510 |
31.158 |
2.281 |
Вариант 2 |
62.524 |
35.226 |
1.040 |
Вариант 3 |
- |
39.339 |
1.250 |
Обратите внимание на разницу в скорости работы первого и второго вариантов для MySQL, а также насколько Oracle превосходит конкурентов в решении задач такого класса.
Задание 2.2.9.TSK.A: показать читателя, последним взявшего в библиотеке книгу.
Задание 2.2.9.TSK.B: показать читателя (или читателей, если их окажется несколько), дольше всего держащего у себя книгу (учитывать только случаи, когда книга не возвращена).
Задание 2.2.9.TSK.C: показать, какую книгу (или книги, если их несколько) каждый читатель взял в свой последний визит в библиотеку.
Задание 2.2.9.TSK.D: показать последнюю книгу, которую каждый из читателей взял в библиотеке.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 148/545
Пример 20: все разновидности запросов на объединение в трёх СУБД
2.2.10. Пример 20: все разновидности запросов на объединение в трёх СУБД
Для начала напомним, какие результаты можно получить, объединяя данные из двух таблиц с помощью классических вариантов JOIN3:
Вид объединения
Внутреннее объединение
Левое внешнее объединение
Левое внешнее объединение с исключением
Правое внешнее объединение
Правое внешнее объединение с исключением
Полное внешнее объединение
Полное внешнее объединение с исключением
Перекрёстное объединение (декартово произведение): попарная комбинация всех записей
Перекрёстное объединение (декартово произведение) с исключением: попарная комбинация всех записей, за исключением тех, что имеют пары по указанному полю)
Графическое
представление
A |
B |
A |
B |
A |
B |
A |
B |
A |
B |
A |
B |
A B
B |
B |
|
|
B |
|
|
|
|
B |
A |
B |
|
|
|
B |
B |
|
B
B B
B A B
B B
Псевдокод запроса
SELECT поля
FROM A INNER JOIN B ON A.поле = B.поле
SELECT поля
FROM A LEFT OUTER JOIN B ON A.поле = B.поле
SELECT поля
FROM A LEFT OUTER JOIN B ON A.поле = B.поле
WHERE B.поле IS NULL
SELECT поля
FROM A RIGHT OUTER JOIN B ON A.поле = B.поле
SELECT поля
FROM A RIGHT OUTER JOIN B ON A.поле = B.поле
WHERE A.поле IS NULL
SELECT поля
FROM A FULL OUTER JOIN B ON A.поле = B.поле
SELECT поля
FROM A FULL OUTER JOIN B ON A.поле = B.поле
WHERE A.поле IS NULL OR B.поле IS NULL
SELECT поля
FROM A CROSS JOIN B
или
SELECT поля
FROM A, B
SELECT поля
FROM A CROSS JOIN B
WHERE A.поле != B.поле
или
SELECT поля
FROM A, B
WHERE A.поле != B.поле
3 Оригинальный рисунок: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 149/545