Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Oracl |
і |
Решение 2.2.9.c |
I |
|
|
e |
|
||||
|
|
|
|
|
|
1 |
WITH "step 1" |
|
|
||
2 |
|
|
AS (SELECT "sb subscriber" |
|
|
3 |
|
|
|
MIN("sb start" |
AS "min date" |
4 |
|
|
FROM |
"subscriptions" |
|
5 |
|
|
GROUP |
BY "sb subscriber" , |
|
6 |
|
|
"step 2" |
|
|
7 |
|
|
AS (SELECT "subscriptions" "sb subscriber", |
||
8 |
|
|
|
"subscriptions" "sb_book" |
|
9 |
|
|
FROM |
"subscriptions" |
|
10 |
|
|
|
join "step 1" |
|
11 |
|
|
|
ON "subscriptions" "sb subscriber" = |
|
12 |
|
|
|
"step 1" "sb subscriber" |
|
13 |
|
|
|
AND "subscriptions" "sb start" = "step 1" "min date" , |
|
14 |
|
|
"step_3" |
|
|
15 |
|
|
AS (SELECT "s id" , |
|
|
16 |
|
|
|
"s name", |
|
17 |
|
|
|
"b id", |
|
18 |
|
|
|
"b name" |
|
19 |
|
|
FROM |
"subscribers" |
|
20 |
|
|
|
JOIN "step 2" |
|
21 |
|
|
|
ON "s id" = "sb subscriber" |
|
22 |
|
|
|
JOIN "books" |
|
23 |
|
|
|
ON "sb book" = "b id") |
|
24 |
SELECT "s id", |
|
|
||
25 |
|
|
"s name" |
|
|
26 |
|
|
UTL RAW.CAST TO NVARCHAR2 |
||
27 |
|
|
( |
|
|
28 |
|
|
LISTAGG |
|
|
29 |
|
|
( |
|
|
30 |
|
|
UTL RAW.CAST TO RAW "b name"), |
||
31 |
|
|
UTL RAW.CAST TO RAW(N', ') |
||
32 |
|
|
) |
|
|
33 |
|
|
WITHIN GROUP (ORDER BY "b name") |
||
34 |
|
|
) |
|
|
35 |
|
|
AS "books list" |
|
|
36 |
FROM |
|
"step 3" |
|
|
37 |
GROUP |
BY "s id", |
|
||
38 |
|
|
"s name" |
|
|
Исследование 2.2.9.EXP.B: сравним скорость работы трёх СУБД при выполнении запросов 2.2.9.c на базе данных «Большая библиотека».
Медианные значения времени после ста выполнений каждого запроса:
MySQL |
MS SQL Server |
Oracle |
91789.850 |
203.083 |
5.167 |
Результаты говорят сами за себя. В реальной задаче для MySQL и MS SQL Server явно придётся искать иное, пусть технически и более сложное, но более производительное решение.
Решение 2.2.9.d{132}.
Данная задача является логическим продолжением задачи 2.2.9. c{132}, только теперь нужно определить, какая книга из тех, что читатель взял в первый свой визит в библиотеку, была выдана первой. Поскольку дата выдачи хранится с точностью до дня, у нас не остаётся иного выхода, кроме как предположить, что первой была выдана книга, запись о выдаче которой имеет минимальное значение первичного ключа.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 150/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Первый вариант решения основан на последовательном определении первого визита для каждого читателя, первой выдачи книги в рамках этого визита, идентификатора книги в этой выдаче и объединения с таблицами subscribers и books, из которых мы извлечём имя читателя и название книги.
MySQL |
Решение 2.2.9.d |
|
|
||
1 |
-- Вариант |
: решение в четыре шага без использования ранжирования |
|||
2 |
SELECT 's |
, |
|
|
|
3 |
|
's name', |
|
|
|
4 |
|
'b name' |
|
|
|
5 |
FROM |
(SELECT 'subscriptions' 'sb subscriber', |
|||
6 |
|
|
'sb book' |
|
|
7 |
|
FROM |
'subscriptions' |
|
|
8 |
|
|
JOIN (SELECT 'subscriptions' 'sb subscriber', |
||
9 |
|
|
|
MIN('sb id') AS 'min sb id' |
|
10 |
|
|
FROM |
'subscriptions' |
|
11 |
|
|
|
JOIN (SELECT 'sb subscriber', |
|
12 |
|
|
|
|
MIN('sb start') AS 'min sb start' |
13 |
|
|
|
FROM |
'subscriptions' |
14 |
|
|
|
GROUP |
BY 'sb subscriber') |
15 |
|
|
|
AS 'step 1' |
|
16 |
|
|
|
ON 'subscriptions' 'sb subscriber' = |
|
17 |
|
|
|
'step 1' 'sb subscriber' |
|
18 |
|
|
|
AND 'subscriptions' 'sb start' = |
|
19 |
|
|
|
'step 1' 'min sb start' |
|
20 |
|
|
GROUP |
BY 'subscriptions' 'sb subscriber', |
|
21 |
|
|
|
'min sb start') |
|
22 |
|
|
AS 'step 2' |
|
|
23 |
|
|
ON 'subscriptions' 'sb id' = 'step 2' 'min sb id') |
||
24 |
|
AS ' step 3' |
|
|
|
25 |
|
JOIN'subscribers' |
|
|
|
26 |
|
ON'sb subscriber' = 's id' |
|
||
27 |
|
JOIN'books' |
|
|
|
28 |
|
ON'sb book' = 'b id' |
|
||
|
|
|
|
|
|
Наиболее глубоко вложенный подзапрос (step_1) в строках 11-15 возвращает информацию о дате первого визита в библиотеку каждого читателя:
sb_subscriber |
min_sb_start |
1 |
2011-01-12 |
3 |
2012-05-17 |
4 |
2012-06-11 |
Следующий подзапрос (step_2) в строках 8-22 на основе только что полученной информации определяет минимальное значение первичного ключа таблицы subscribers, соответствующее каждому читателю и дате его первого визита в библиотеку:
sb_subscriber |
min_sb_id |
1 |
2 |
3 |
3 |
4 |
57 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 151/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Следующий подзапрос (step_3) в строках 5-24 на основе этой информации определяет идентификатор книги, соответствующий каждой выдаче с идентифика-
тором min_sb_id:
sb_subscriber |
sb_book |
1 |
1 |
3 |
3 |
4 |
5 |
Основная часть запроса в строках 2-4 и 25-28 является четвёртым шагом, на котором по имеющимся идентификаторам определяются имя читателя и название книги. Так получается финальный результат.
MySQL Решение 2.2.9.d
1-- Вариант 2: решение в два шага с использованием ранжирования
2SELECT 's id',
3's name',
4'b name'
5 |
FROM |
(SELECT 'sb subscriber', |
|
6 |
|
|
'sb start', |
7 |
|
|
'sb id', |
8 |
|
|
'sb book', |
9 |
|
|
( CASE |
10 |
|
|
WHEN ( @sb subscriber value = 'sb subscriber' ) |
11 |
|
|
THEN @i := @i + 1 |
12 |
|
|
ELSE ( @i := 1 ) |
13 |
|
|
AND ( @sb subscriber value := 'sb subscriber' ) |
14 |
|
|
END ) AS 'rank by subscriber', |
15 |
|
|
( CASE |
16 |
|
|
WHEN ( @sb subscriber value = 'sb subscriber' ) |
17 |
|
|
AND ( @sb start value = 'sb start' ) |
18 |
|
|
THEN @j := @j + 1 |
19 |
|
|
ELSE ( @j := 1 ) |
20 |
|
|
AND ( @sb subscriber value := 'sb subscriber' ) |
21 |
|
|
AND ( @sb start value := 'sb start' ) |
22 |
|
|
END ) AS 'rank by date' |
23 |
|
FROM |
'subscriptions', |
24 |
|
|
(SELECT @i := 0 |
25 |
|
|
@j := 0 |
26 |
|
|
@sb subscriber value := '', |
27 |
|
|
@sb start value := '' |
28 |
|
|
) AS 'initialisation' |
29 |
|
ORDER BY 'sb subscriber', |
|
30 |
|
|
'sb start', |
31 |
|
|
'sb id') AS 'ranked' |
32JOIN 'subscribers'
33ON 'sb subscriber' = 's id'
34JOIN 'books'
35ON 'sb book' = 'b id'
36WHERE 'rank by subscriber' = 1
37AND 'rank by date' = 1
Второй вариант решения построен на идее ранжирования дат визитов каждого читателя и ранжирования выдач книг в рамках каждого визита каждого читателя. Поскольку MySQL не поддерживает никаких ранжирующих (оконных) функций, их поведение придётся эмулировать.
Подзапрос в строках 24-28 отвечает за начальную инициализацию перемен-
ных:
•переменная @i хранит порядковый номер визита каждого из читателей;
•переменная @ j хранит порядковый номер выдачи книги в рамках каждого визита каждого читателя;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 152/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
•переменная @sb_subscriber_value используется для определения того факта, что в процессе обработки данных изменился идентификатор читателя;
•переменная @sb_start_value используется для определения того факта, что в процессе обработки данных изменилось значение даты визита.
Выражение в строках 9-14 определяет, изменилось ли значение идентификатора читателя, и либо инкрементирует порядковые номер @i (если значение идентификатора не изменилось), либо инициализирует его значением 1 (если значение идентификатора изменилось).
Выражение в строках 15-22 определяет, изменились ли значения идентификатора читателя и даты визита, и либо инкрементирует порядковые номер @j (если значения идентификатора и даты не изменились), либо инициализирует его значением 1 (если значения идентификатора или даты изменились).
Результатом работы подзапроса в строках 5-21 является следующий набор данных (серым фоном отмечены строки, представляющие для нас интерес в контексте решения задачи):
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 |
Выбрав из этого набора строки, соответствующие первому визиту читателя и первой выдаче книги в рамках этого визита (строки 36-37 запроса), мы получаем:
sb_subscriber |
sb_start |
sb_id |
sb_book |
rank_by_subscriber |
rank_by_date |
1 |
2011-01-12 |
2 |
1 |
1 |
1 |
3 |
2012-05-17 |
3 |
3 |
1 |
1 |
4 |
2012-06-11 |
57 |
5 |
1 |
1 |
Теперь остаётся по идентификаторам читателе и книг получить их имена и названия (строки 2-4 и 32-35 запроса). Так получается итоговый результат.
Этот запрос можно оптимизировать, выбирая меньше полей и проводя меньше проверок, но тогда он станет сложнее для понимания.
Решение для MS SQL Server и Oracle реализуется проще, и даже допускает ещё один — третий — вариант, т.к. эти СУБД поддерживают ранжирующие (оконные) функции. И всё же первый вариант решения по соображениям совместимости будет реализован без ранжирования.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 153/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
MS SQL I Решение 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] |
|
8 |
AS (SELECT [subscriptions] [sb subscriber] |
|
9 |
|
MIN [sb id]I 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]
27FROM
28JOIN [subscribers]
29ON [sb subscriber] = [s id]
30JOIN [books]
31ON [sb book] = [b id]
Здесь общие табличные выражения возвращают те же наборы данных, что и подзапросы с соответствующими именами в MySQL.
Общее табличное выражение step_1 (строки 2-6 запроса) возвращает информацию о дате первого визита в библиотеку каждого читателя:
sb_subscriber |
min_sb_start |
1 |
2011-01-12 |
3 |
2012-05-17 |
4 |
2012-06-11 |
Общее табличное выражение step_2 (строки 7-17 запроса) возвращает минимальное значение первичного ключа таблицы subscribers, соответствующее каждому читателю и дате его первого визита в библиотеку:
sb_subscriber |
min_sb_id |
1 |
2 |
3 |
3 |
4 |
57 |
Общее табличное выражение step_3 (строки 18-23 запроса) возвращает идентификатор книги, соответствующий каждой выдаче с идентификатором min_sb_id:
sb_subscriber |
sb_book |
1 |
1 |
3 |
3 |
4 |
5 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 154/545