Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 8: поиск множества минимальных и максимальных значений
Как мы видим, только для книги с количеством экземпляров, равным 12, заданное условие выполняется. Если бы ещё хотя бы у одной книги было такое же количество экземпляров, ни для одной строки выборки условие бы не выполнилось, и запрос возвратил бы пустой результат (что и показано в начале этого примера, см. «Возможный ожидаемый результат 2.1.8.d»).
Вторые варианты решения доступны только в MS SQL Server и Oracle (т.к. MySQL не поддерживает общие табличные выражения, хотя в нём и возможно написать подобный вариант решения через подзапросы). Идея второго варианта состоит в том, чтобы отказаться от коррелирующих подзапросов. Для этого в строках 1-7 второго варианта решения 2.1.8.d в общем табличном выражении ranked производится подготовка данных с ранжированием книг по количеству их экземпляров, в строках 8-12 в общем табличном выражении counted определяется количество книг, занявших одно и то же место, а в основной части запроса в строках 1319 происходит объединение полученных данных с наложением фильтра «должно быть первое место, и на первом месте должна быть только одна книга».
Исследование 2.1.8.EXP.C. Что работает быстрее — вариант с коррелирующим подзапросом или с общим табличным выражением и последующим объединением? Выполним по сто раз соответствующие запросы 2.1.8.d на базе данных «Большая библиотека».
Медианные значения времени после ста выполнений запросов таковы:
|
MS QL Server |
Oracle |
Коррелирующий подза- |
0.198 |
0.402 |
прос |
|
|
Общее табличное выра- |
0.185 |
0.378 |
жение с последующим |
|
|
объединением |
|
|
Вариант с общим табличным выражением оказывается пусть и немного, но всё же быстрее.
Исследование 2.1.8.EXP.D. И ещё раз продемонстрируем разницу в скорости работы решений, основанных на коррелирующих запросах, агрегирующих функциях и ранжировании. Представим, что для каждого читателя нам нужно показать ровно одну (любую, если их может быть несколько, но — одну) запись из таблицы subscriptions, соответствующую первому визиту читателя в библиотеку.
В результате мы ожидаем увидеть:
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
2 |
1 |
1 |
2011-01-12 |
2011-02-12 |
N |
3 |
3 |
3 |
2012-05-17 |
2012-07-17 |
Y |
57 |
4 |
5 |
2012-06-11 |
2012-08-11 |
N |
В каждой из СУБД возможно три варианта получения этого результата (в MS SQL Server и Oracle добавляется ещё вариант с общим табличным выражением, но мы осознанно не будем его рассматривать, ограничившись аналогом с подзапросами):
• на основе коррелирующих запросов (при этом в Oracle придётся очень нетривиальным образом эмулировать в подзапросе ограничение на количество выбранных записей, реализуемое через LIMIT 1 и TOP 1 в MySQL и MS SQL Server);
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 50/545
Пример 8: поиск множества минимальных и максимальных значений
•на основе агрегирующих функций;
•на основе ранжирования (обратите внимание, что в MySQL нет соответствующих готовых решений, потому нам придётся эмулировать поведение доступной в MS SQL Server и Oracle функции ROW_NUMBER средствами MySQL).
MySQL Исследование 2.1.8.EXP.D
1-- Вариант 1: решение на основе коррелирующих запросов
2SELECT `sb_id`,
3`sb_subscriber`,
4`sb_book`,
5`sb_start`,
6`sb_finish`,
7`sb_is_active`
8 |
|
FROM |
`subscriptions` AS `outer` |
|
9 |
|
WHERE |
`sb_id` = (SELECT |
`sb_id` |
10 |
|
|
FROM |
`subscriptions` AS `inner` |
11 |
|
|
WHERE |
`outer`.`sb_subscriber` = `inner`.`sb_subscriber` |
12 |
|
|
ORDER |
BY `sb_start` ASC |
13 |
|
|
LIMIT |
1) |
|
|
|
|
|
1-- Вариант 2: решение на основе агрегирующих функций
2SELECT `sb_id`,
3`subscriptions`.`sb_subscriber`,
4`sb_book`,
5`sb_start`,
6`sb_finish`,
7`sb_is_active`
8 |
|
FROM |
`subscriptions` |
|
|
9 |
|
WHERE |
`sb_id` IN (SELECT |
MIN(`sb_id`) |
|
10 |
|
|
FROM |
`subscriptions` |
|
11 |
|
|
|
JOIN (SELECT `sb_subscriber`, |
|
12 |
|
|
|
|
MIN(`sb_start`) AS `min_date` |
13 |
|
|
|
FROM |
`subscriptions` |
14 |
|
|
|
GROUP |
BY `sb_subscriber`) AS `prepared` |
15 |
|
|
|
ON `subscriptions`.`sb_subscriber` = |
|
16 |
|
|
|
`prepared`.`sb_subscriber` |
|
17 |
|
|
|
AND `subscriptions`.`sb_start` = |
|
18 |
|
|
|
`prepared`.`min_date` |
|
19 |
|
|
GROUP |
BY `prepared`.`sb_subscriber`, |
|
20 |
|
|
|
`prepared`.`min_date`) |
|
1-- Вариант 3: решение на основе ранжирования
2SELECT `subscriptions`.`sb_id`,
3`sb_subscriber`,
4`sb_book`,
5`sb_start`,
6`sb_finish`,
7`sb_is_active`
8 |
|
FROM `subscriptions` |
|
9 |
|
JOIN (SELECT |
`sb_id`, |
10 |
|
|
@row_num := IF(@prev_value = `sb_subscriber`, |
11 |
|
|
@row_num + 1, |
12 |
|
|
1) AS `visit`, |
13 |
|
|
@prev_value := `sb_subscriber` |
14 |
|
FROM |
`subscriptions`, |
15 |
|
|
(SELECT @row_num := 1) AS `x`, |
16 |
|
|
(SELECT @prev_value := '') AS `y` |
17 |
|
ORDER |
BY `sb_subscriber` ASC, |
18 |
|
|
`sb_start` ASC) AS `prepared` |
19ON `subscriptions`.`sb_id` = `prepared`.`sb_id`
20WHERE `visit` = 1
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 51/545
Пример 8: поиск множества минимальных и максимальных значений
MS SQL Исследование 2.1.8.EXP.D
1-- Вариант 1: решение на основе коррелирующих запросов
2SELECT [sb_id],
3[sb_subscriber],
4[sb_book],
5[sb_start],
6[sb_finish],
7[sb_is_active]
8 |
|
FROM |
[subscriptions] AS [outer] |
|
9 |
|
WHERE |
[sb_id] = (SELECT |
TOP 1 [sb_id] |
10 |
|
|
FROM |
[subscriptions] AS [inner] |
11 |
|
|
WHERE |
[outer].[sb_subscriber] = [inner].[sb_subscriber] |
12 |
|
|
ORDER |
BY [sb_start] ASC) |
|
|
|
|
|
1-- Вариант 2: решение на основе агрегирующих функций
2SELECT [sb_id],
3[subscriptions].[sb_subscriber],
4[sb_book],
5[sb_start],
6[sb_finish],
7[sb_is_active]
8 |
|
FROM |
[subscriptions] |
|
|
9 |
|
WHERE |
[sb_id] IN (SELECT |
MIN([sb_id]) |
|
10 |
|
|
FROM |
[subscriptions] |
|
11 |
|
|
|
JOIN (SELECT [sb_subscriber], |
|
12 |
|
|
|
|
MIN([sb_start]) AS [min_date] |
13 |
|
|
|
FROM |
[subscriptions] |
14 |
|
|
|
GROUP |
BY [sb_subscriber]) AS [prepared] |
15 |
|
|
|
ON [subscriptions].[sb_subscriber] = |
|
16 |
|
|
|
[prepared].[sb_subscriber] |
|
17 |
|
|
|
AND [subscriptions].[sb_start] = |
|
18 |
|
|
|
[prepared].[min_date] |
|
19 |
|
|
GROUP |
BY [prepared].[sb_subscriber], |
|
20 |
|
|
|
[prepared].[min_date]) |
|
1-- Вариант 3: решение на основе ранжирования
2SELECT [subscriptions].[sb_id],
3[sb_subscriber],
4[sb_book],
5[sb_start],
6[sb_finish],
7[sb_is_active]
8 |
|
FROM [subscriptions] |
|
9 |
|
JOIN (SELECT |
[sb_id], |
10 |
|
|
ROW_NUMBER() |
11 |
|
|
OVER ( |
12 |
|
|
PARTITION BY [sb_subscriber] |
13 |
|
|
ORDER BY [sb_start] ASC) AS [visit] |
14 |
|
FROM |
[subscriptions]) AS [prepared] |
|
|
|
|
15ON [subscriptions].[sb_id] = [prepared].[sb_id]
16WHERE [visit] = 1
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 52/545
Пример 8: поиск множества минимальных и максимальных значений
Oracle Исследование 2.1.8.EXP.D
1-- Вариант 1: решение на основе коррелирующих запросов
2SELECT "sb_id",
3"sb_subscriber",
4"sb_book",
5"sb_start",
6"sb_finish",
7"sb_is_active"
8 |
|
FROM |
"subscriptions" "outer" |
|
9 |
|
WHERE |
"sb_id" = (SELECT |
DISTINCT FIRST_VALUE("inner"."sb_id") |
10 |
|
|
|
OVER ( |
11 |
|
|
|
ORDER BY "inner"."sb_start" ASC) |
12 |
|
|
FROM |
"subscriptions" "inner" |
13 |
|
|
WHERE |
"outer"."sb_subscriber" = "inner"."sb_subscriber") |
|
|
|
|
|
1-- Вариант 2: решение на основе агрегирующих функций
2SELECT "sb_id",
3"subscriptions"."sb_subscriber",
4"sb_book",
5"sb_start",
6"sb_finish",
7"sb_is_active"
8 |
|
FROM |
"subscriptions" |
|
|
9 |
|
WHERE |
"sb_id" IN (SELECT |
MIN("sb_id") |
|
10 |
|
|
FROM |
"subscriptions" |
|
11 |
|
|
|
JOIN (SELECT "sb_subscriber", |
|
12 |
|
|
|
|
MIN("sb_start") AS "min_date" |
13 |
|
|
|
FROM |
"subscriptions" |
14 |
|
|
|
GROUP |
BY "sb_subscriber") "prepared" |
15 |
|
|
|
ON "subscriptions"."sb_subscriber" = |
|
16 |
|
|
|
"prepared"."sb_subscriber" |
|
17 |
|
|
|
AND "subscriptions"."sb_start" = |
|
18 |
|
|
|
"prepared"."min_date" |
|
19 |
|
|
GROUP |
BY "prepared"."sb_subscriber", |
|
20 |
|
|
|
"prepared"."min_date") |
|
|
|
|
|
|
|
1-- Вариант 3: решение на основе ранжирования
2SELECT "subscriptions"."sb_id",
3"sb_subscriber",
4"sb_book",
5"sb_start",
6"sb_finish",
7"sb_is_active"
8 |
|
FROM "subscriptions" |
|
9 |
|
JOIN (SELECT |
"sb_id", |
10 |
|
|
ROW_NUMBER() |
11 |
|
|
OVER ( |
12 |
|
|
partition BY "sb_subscriber" |
13 |
|
|
ORDER BY "sb_start" ASC) AS "visit" |
14 |
|
FROM |
"subscriptions") "prepared" |
15ON "subscriptions"."sb_id" = "prepared"."sb_id"
16WHERE "visit" = 1
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 53/545
Пример 8: поиск множества минимальных и максимальных значений
После выполнения каждого из представленных запросов по одному разу (увидев результаты, вы легко поймёте, почему только под одному разу) на базе данных «Большая библиотека» получились следующие значения времени:
|
MySQL |
MS SQL Server |
Oracle |
Решение на ос- |
43:12:17.674 |
247:53:21.645 |
763:32:22.878 |
нове коррелирую- |
|
|
|
щих запросов |
|
|
|
Решение на ос- |
44:17:12.736 |
688:43:58.244 |
041:19:43.344 |
нове агрегирую- |
|
|
|
щих функций |
|
|
|
Решение на ос- |
00:18:48.828 |
000:00:41.511 |
000:02:32.274 |
нове ранжирова- |
|
|
|
ния |
|
|
|
Каждая из СУБД оказалась самой быстрой в одном из видов запросов, но во всех трёх СУБД решение на основе ранжирования стало бесспорным лидером (сравните, например, лучший и худший результаты для MS SQL Server: 41.5 секунды вместо почти месяца).
Задание 2.1.8.TSK.A: показать идентификатор одного (любого) читателя, взявшего в библиотеке больше всего книг.
Задание 2.1.8.TSK.B: показать идентификаторы всех «самых читающих читателей», взявших в библиотеке больше всего книг.
Задание 2.1.8.TSK.C: показать идентификатор «читателя-рекордсмена», взявшего в библиотеке больше книг, чем любой другой читатель.
Задание 2.1.8.TSK.D: написать второй вариант решения задачи 2.1.8.d (основанный на общем табличном выражении) для MySQL, проэмулировав общее табличное выражение через подзапросы.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 54/545