Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Oracle Решение 2.2.9.c
1WITH "step_1"
2AS (SELECT "sb_subscriber",
3 |
|
|
MIN("sb_start") |
AS "min_date" |
4 |
|
FROM |
"subscriptions" |
|
5 |
|
GROUP |
BY "sb_subscriber"), |
|
|
|
|
|
|
6"step_2"
7AS (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"
15AS (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") |
24SELECT "s_id",
25"s_name",
26UTL_RAW.CAST_TO_NVARCHAR2
27(
28LISTAGG
29(
30UTL_RAW.CAST_TO_RAW("b_name"),
31UTL_RAW.CAST_TO_RAW(N', ')
32)
33WITHIN GROUP (ORDER BY "b_name")
34)
35AS "books_list"
36 FROM "step_3"
37GROUP 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 Стр: 140/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
Первый вариант решения основан на последовательном определении первого визита для каждого читателя, первой выдачи книги в рамках этого визита, идентификатора книги в этой выдаче и объединения с таблицами subscribers и books, из которых мы извлечём имя читателя и название книги.
MySQL Решение 2.2.9.d
1-- Вариант 1: решение в четыре шага без использования ранжирования
2SELECT `s_id`,
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`) |
||
|
|
|
|
|
|
24AS `step_3`
25JOIN `subscribers`
26ON `sb_subscriber` = `s_id`
27JOIN `books`
28ON `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 Стр: 141/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 Стр: 142/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 Стр: 143/545
Пример 19: запросы на объединение и поиск минимума, максимума, диапазонов
MS SQL Решение 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]
Здесь общие табличные выражения возвращают те же наборы данных, что и подзапросы с соответствующими именами в 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 Стр: 144/545