Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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 Стр: 155/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
MS SQL I Решение 2.2.9.d |
1-- Вариант 3: решение в три шага с использованием ранжирования
2-- и группировки
3WITH [step 1]
4AS (SELECT [sb subscriber],
5 |
[sb start], |
|
|
6 |
MIN1[sb id] |
AS |
[min sb id] |
7 |
RANK() |
|
|
8 |
OVER ( |
|
|
9 |
PARTITION BY [sb subscriber] |
|
|
10 |
ORDER BY [sb start] ASC) AS |
[rank] |
|
11FROM [subscriptions]
12GROUP 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 Стр: 156/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Решение для Oracle аналогично решению для MS SQL Server и отличается лишь незначительными синтаксическими деталями.
Oracl |
і |
Решение 2.2.9.d |
| |
|
|
e |
|
|
|||
|
|
|
|
|
|
1 |
-- Вариант 1: решение в четыре шага без использования ранжирования |
||||
2 |
WITH "step 1" |
|
|
|
|
3 |
|
AS (SELECT "sb subscriber" |
|
||
4 |
|
|
MIN("sb start" |
AS "min sb start" |
|
5 |
|
FROM |
"subscriptions" |
|
|
6 |
|
GROUP |
BY "sb subscriber" , |
||
7 |
|
"step 2" |
|
|
|
8 |
|
AS (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" |
|
|
|
19 |
|
AS (SELECT "subscriptions" "sb subscriber" |
|||
20 |
|
|
"sb book" |
|
|
21 |
|
FROM |
"subscriptions" |
|
|
22 |
|
|
JOIN "step 2" |
|
|
23 |
|
|
ON "subscriptions" "sb id" = "step 2" "min sb id") |
||
24 |
SELECT "s id", |
|
|
|
|
25 |
|
"s name" |
|
|
|
26 |
|
"b name" |
|
|
|
27 |
FROM |
"step 3" |
|
|
|
28 |
|
JOIN "subscribers" |
|
|
|
29 |
|
ON "sb subscriber" = "s id" |
|||
30 |
|
JOIN "books" |
|
|
|
31 |
|
ON "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 Стр: 157/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Oracl |
і |
Решение 2.2.9.d (продолжение) |
| |
||
e |
|||||
|
|
|
|
||
1 |
-- Вариант 3: решение в три шага с использованием ранжирования |
||||
2 |
-- и группировки |
|
|||
3 |
WITH "step 1" |
|
|
||
4 |
|
AS (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" |
|
|
|
15 |
|
AS (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" |
||
20 |
|
WHERE |
"rank" = 1 |
|
|
21 |
SELECT "s id", |
|
|
||
22 |
|
"s name" |
|
|
|
23 |
|
"b name" |
|
|
|
24 |
FROM |
"step 2" |
|
|
|
25 |
|
JOIN "subscribers" |
|
||
26 |
|
ON "sb subscriber" = "s id" |
|||
27 |
|
JOIN "books" |
|
||
28 |
|
ON "sb book" = "b id" |
|||
Исследование 2.2.9.EXP.C: сравним скорость работы решений этой задачи, выполнив запросы 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 Стр: 158/545
