Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 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 в примерах © Богдан Марчук Стр: 55/545
Пример 8: поиск множества минимальных и максимальных значений
на основе агрегирующих функций;
на основе ранжирования (обратите внимание, что в MySQL нет соответствующих готовых решений, потому нам придётся эмулировать поведение доступной в MS SQL Server и Oracle функции ROW_NUMBER средствами MySQL).
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
Исследование 2.1.8.EXP.D
-- Вариант 1: решение на основе коррелирующих запросов
SELECT 'sb_id', |
|
|
'sb_subscriber', |
|
|
'sb_book', |
|
|
'sb_start', |
|
|
'sb_finish', |
|
|
' sb_is_active' |
|
|
FROM |
'subscriptions' AS 'outer' |
|
WHERE |
|
'sb_id' = (SELECT 'sb_id' |
|
FROM |
'subscriptions' AS 'inner' |
|
WHERE |
'outer'.'sb_subscriber' = 'inner'.'sb_subscriber' |
|
ORDER |
BY 'sb_start' ASC |
|
LIMIT |
1) |
1 |
-- Вариант 2: решение на основе агрегирующих функций |
||||
SELECT |
'sb_id', |
|
|
|
|
2 |
|
|
|
||
'subscriptions' 'sb_subscriber', |
|
|
|||
3 |
|
|
|||
'sb_book', |
|
|
|
||
4 |
|
|
|
||
'sb_start', |
|
|
|
||
5 |
|
|
|
||
'sb_finish', |
|
|
|
||
6 |
|
|
|
||
'sb_is_active' |
|
|
|
||
7 |
|
|
|
||
FROM |
'subscriptions' |
|
|
|
|
8 |
|
|
|
||
WHERE |
'sb_id' IN (SELECT MIN('sb_id') |
|
|||
9 |
|
||||
|
FROM 'subscriptions' |
|
|
||
10 |
|
|
|
||
|
JOIN (SELECT |
'sb_subscriber', |
|||
11 |
|
||||
|
|
MIN('sb_start') AS 'min_date' |
|||
12 |
|
|
|||
|
FROM |
'subscriptions' |
|||
13 |
|
||||
|
GROUP BY |
'sb_subscriber') AS 'prepared' |
|||
14 |
|
||||
|
ON 'subscriptions' |
'sb_subscriber' = |
|||
15 |
|
||||
|
'prepared' |
'sb_subs criber' |
|||
16 |
|
||||
|
AND 'subscriptions' 'sb_start' = |
||||
17 |
|
||||
|
'prepared' |
'min_date' |
|||
18 |
|
||||
|
GROUP BY 'prepared' 'sb_subscriber', |
||||
19 |
'prepared'.'min_date') |
|
20 |
||
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Вариант 3: решение на основе ранжирования SELECT 'subscriptions' 'sb_id', 'sb_subscriber', 'sb_book', 'sb_start', 'sb_finish', ' sb_is_active'
FROM 'subscriptions' JOIN (SELECT |
'sb_id', |
|
|
||
@row_num := IF(@prev_value = 'sb_subscriber', @row_num + |
|||||
1, 1) AS 'visit', @prev_value := |
'sb_subscriber' |
||||
FROM 'subscriptions', (SELECT @row_num := 1) AS 'x', (SELECT |
|||||
@prev_value := |
|
'') AS 'y' |
|
|
|
ORDER BY 'sb_subscriber' |
|
ASC, |
|
|
|
'sb_start' ASC) |
|
AS 'prepared' |
|
|
|
ON 'subscriptions' |
'sb_id' |
= 'prepared' |
'sb_id' |
||
WHERE 'visit' = 1 |
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 56/545
Пример 8: поиск множества минимальных и максимальных значений
MS SQL I Исследование 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] = |
[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 |
[mi_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 в примерах © Богдан Марчук Стр: 57/545
Пример 8: поиск множества минимальных и максимальных значений
Oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Исследование 2.1.8.EXP.D
— Вариант 1: решение на основе коррелирующих запросов
SELECT |
"sb_id" |
|
|
|
"sb_subscriber", |
|
|
|
|
"sb_book", |
|
|
|
|
"sb_start", |
|
|
|
|
"sb_finish", |
|
|
|
|
"sb_is_active" |
|
|
|
|
FROM "subscriptions" "outer" |
|
|
||
WHERE |
"sb_id" |
= (SELECT DISTINCT FIRST_VALUE("inner" |
||
"sb_id" |
|
|
|
|
|
|
OVER ( |
|
|
|
|
ORDER BY "inner" "sb_start" ASC) |
||
|
FROM |
"subscriptions" "inner" |
||
|
WHERE |
"outer" "sb_subscriber" = "inner" "sb_subscriber") |
||
— Вариант 2: решение на основе агрегирующих функций |
||||
SELECT |
"sb_id" |
|
|
|
"subscriptions" |
"sb_subscriber" |
|
|
|
"sb_book", |
|
|
|
|
"sb_start", |
|
|
|
|
"sb_finish", |
|
|
|
|
"sb_is_active" |
|
|
|
|
FROM |
"subscriptions" |
|
|
|
WHERE |
"sb_id" IN (SELECT MINl"sb_id" |
|||
|
FROM "subscriptions" |
|
||
|
|
JOIN (SELECT |
"sb_subscriber", |
|
|
|
|
MIN("sb_s tart" AS "min_date" |
|
|
|
FROM "subscriptions" |
||
|
|
GROUP BY |
"sb_subscriber" "prepared" |
|
|
|
ON "subscriptions" "sb_subscriber" = |
||
|
|
"prepared" |
"sb_subscriber" |
|
|
|
AND "subscriptions" "sb_start" = |
||
|
|
"prepared"."min_date" |
||
|
GROUP BY "prepared" |
"sb_subscriber", |
||
|
|
"prepared" |
"min_date") |
|
— Вариант 3: решение на основе ранжирования
SELECT "subscriptions" "sb_id" "sb_subscriber"
"sb_book", "sb_start", "sb_finish", "sb_is_active"
FROM "subscriptions" JOIN (SELECT "sb_id",
ROW_NUMBER()
OVER ( partition BY "sb_subscriber" ORDER BY "sb_start" ASC) AS "visit"
FROM "subscriptions") "prepared"
ON "subscriptions" "sb_id" = "prepared" "sb_id" WHERE "visit" = 1
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 58/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 в примерах © Богдан Марчук Стр: 59/545