Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 8: поиск множества минимальных и максимальных значений
В строке 5 запроса 2.1.8.b для Oracle мы используем специальную аналитическую функцию ROW_NUMBER, позволяющую присвоить строке номер на основе вы-
ражения. В нашем случае выражение имеет упрощённый вариант: не указан принцип разбиения строк на группы и перезапуска нумерации — мы лишь просим СУБД пронумеровать строки в порядке их следования в упорядоченной выборке.
Oracle не позволяет в одном и том же запросе как пронумеровать строки, так и наложить условие на выборку на основе этой нумерации, потому мы вынуждены использовать подзапрос (строки 3-6). Альтернативой подзапросу может быть т.н. CTE (Common Table Expression, общее табличное выражение), но
( |
о CTE мы поговорим позже{73}. |
Частым вопросом относительно решения для Oracle является примени- |
|
мость здесь не функции ROW_NUMBER, а «псевдополя» ROWNUM. Его при- |
|
|
менять нельзя, т.к. нумерация с его использованием происходит до сра- |
|
батывания ORDER BY. |
|
Исследование 2.1.8.EXP.A. Продемонстрируем результат неверного ис- |
|
пользования «псевдополя» ROWNUM вместо функции ROW_NUMBER. |
Oracle |
Исследование 2.1.8.EXP.A, пример неверного запроса |
1SELECT "b name",
2"b quantity"
3 |
FROM |
(SELECT "b name", |
|
4 |
|
|
"b_quantity", |
5 |
|
|
ROWNUM AS "rn" |
6 |
|
FROM |
"books" |
7 |
|
ORDER BY "b_quantity" DESC) |
|
8 |
WHERE |
"rn" = 1 |
|
Результатом выполнения этого запроса является:
b_name |
b_quantity |
Евгений Онегин |
2 |
Такой результат получается потому, что подзапрос (строки 3-7) возвращает следующие данные:
b_name |
|
b_quantity |
rn |
Курс теоретической физики |
12 |
|
6 |
Искусство программирования |
7 |
|
7 |
Основание и империя |
5 |
|
3 |
Сказка о рыбаке и рыбке |
3 |
|
2 |
Язык программирования C++ |
3 |
|
5 |
Евгений Онегин |
2 |
|
1 |
Психология программирования |
1 |
|
4 |
Легко заметить, что нумерация строк произошла до упорядочивания, и первый номер был присвоен книге «Евгений Онегин». При этом вариант с функцией ROW_NUMBER работает корректно.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 50/545
Пример 8: поиск множества минимальных и максимальных значений
Решение 2.1.8.С43.
Если нам нужно показать все книги, представленные равным максимальным количеством экземпляров, нужно выяснить это максимальное количество и использовать полученное значение как условие выборки.
MySQL і Решение 2.1.8.С
1 |
-- Вариант 1: использование MAX |
||
2 |
SELECT 'b name', |
|
|
3 |
|
'b quantity' |
|
4 |
FROM |
'books' |
|
5 |
WHERE |
'b quantity' = (SELECT MAX('b quantity') |
|
6 |
|
FROM |
'books') |
MS SQL I Решение 2.1.8.c |
1-- Вариант 1: использование MAX
2SELECT [b name],
3[b quantity]
4 |
FROM |
[books] |
|
5 |
WHERE |
[b quantity] = (SELECT MAX([b quantity]) |
|
6 |
|
FROM |
[books]) |
1-- Вариант 2: использование RANK
2SELECT [b name],
3[b_quantity]
4 |
FROM |
(SELECT [b name], |
|
|
5 |
|
|
[b_quantity], |
|
6 |
|
|
RANK() OVER (ORDER BY [b quantity] DESC) AS [rn] |
|
7 |
|
FROM |
[books]) AS [temporary data] |
|
8 |
WHERE |
[rn] = 1 |
|
|
|
Oracle I Решение 2.1.8.С |
|
|
|
1 |
-- Вариант 1: использование MAX |
|||
2 |
SELECT "b name", |
|
||
3 |
|
"b_quanti ty" |
|
|
4 |
FROM |
"books" |
|
|
5 |
WHERE |
"b quantity" = (SELECT MAX "b quantity"i |
||
6 |
|
|
FROM |
"books") |
1 |
-- Вариант 2: использование RANK |
|||
2 |
SELECT "b name", |
|
||
3 |
|
"b_quanti ty" |
|
|
4 |
FROM |
(SELECT "b name", |
|
|
5 |
|
|
"b_quantity", |
|
6 |
|
|
RANK() OVER (ORDER BY "b quantity" DESC) AS "rn" |
|
7 |
|
FROM |
"books") |
|
8 |
WHERE |
"rn" = 1 |
|
|
|
|
|
|
|
В случае с MySQL доступен только один вариант решения2: подзапросом (строки 5-6) выяснить максимальное количество экземпляров книг и использовать полученное число как условие выборки. Этот же вариант решения прекрасно рабо-
тает в MS SQL Server и Oracle.
MS SQL Server и Oracle поддерживают т.н. «оконные (ранжирующие) функции», позволяющие реализовать второй вариант решения. Обратите внимание на строку 6 этого варианта: MS SQL Server требует явного именования подзапроса, являющегося источником данных, а Oracle не требует (именование подзапроса допустимо, но в данном случае не является обязательным).
2 На самом деле, в MySQL можно эмулировать ранжирующие (оконные) функции. Примеры такой эмуляции представлены в исследовании 2.1.8.EXP.D{48} и решениях 2.2.7.d{119}, 2.2.9.d{139}.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 51/545
Пример 8: поиск множества минимальных и максимальных значений
Функция RANK позволяет ранжировать строки выборки (т.е. расставить их на
1-е, 2-е, 3-е, 4-е и так далее места) по указанному условию (в нашем случае — по убыванию количества экземпляров книг). Книги с одинаковыми количествами экземпляров будут занимать одинаковые места, а на первом месте будут книги с максимальным количеством экземпляров. Остаётся только показать эти книги, занявшие первое место.
Для наглядности рассмотрим, что возвращает подзапрос, представленный строками 4-7 второго варианта решения для MS SQL Server и Oracle:
MS SQL Решение 2.1.8.c (фрагмент запроса)
|
1 |
|
SELECT [b name] |
|
|
|
|
|
2 |
|
|
[b_quantity] |
|
|
|
|
3 |
|
|
RANK() OVER (ORDER BY [b quantity] DESC) AS [rn] |
|||
|
4 |
|
FROM |
[books] |
|
|
|
|
|
Oracl |
|
|
|
|
|
|
e |
|
і |
Решение 2.1.8.c (фрагмент запроса) |
| |
|
|
|
1 |
|
SELECT "b name" |
|
|
|
|
|
2 |
|
|
"b quantity" |
|
|
|
|
3 |
|
|
RANK() OVER (ORDER BY "b quantity" DESC) AS "rn" |
|||
|
4 |
|
FROM |
"books" |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
b_name |
b_quantity |
rn |
|
Курс теоретической физики |
12 |
1 |
|
||||
Искусство программирования |
7 |
2 |
|
||||
Основание и империя |
5 |
3 |
|
||||
Сказка о рыбаке и рыбке |
3 |
4 |
|
||||
Язык программирования C++ |
3 |
4 |
|
||||
Евгений Онегин |
2 |
6 |
|
||||
Психология программирования |
1 |
7 |
|
||||
Исследование 2.1.8.EXP.B. Что работает быстрее — MAX или RANK? Ис-
пользуем ранее созданную и наполненную данными (десять миллионов записей) таблицу test counts{21} и проверим.
Медианные значения времени после ста выполнений запросов таковы:
|
MS QL Server |
Oracle |
MAX |
0.009 |
0.341 |
RANK |
6.940 |
0.818 |
Вариант с MAX в данном случае работает быстрее. Однако в исследовании 2.2.7. EXP.A{119} будет показана обратная ситуация, когда вариант с ранжированием окажется значительно быстрее варианта с функцией MAX. Таким образом, вновь и
вновь подтверждается идея о том, что исследование производительности стоит выполнять в конкретной ситуации на конкретном наборе данных.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 52/545
Пример 8: поиск множества минимальных и максимальных значений
Решение 2.1.8.d{43}.
Чтобы найти «абсолютного рекордсмена» по количеству экземпляров, мы используем функцию работы с множествами ALL, которая позволяет сравнить некоторое значение с каждым элементом множества.
1-- Вариант 1: использование ALL и подзапроса
2SELECT 'b name',
3'b_quanti ty'
4 |
FROM |
'books' AS 'ext' |
|
|
5 |
WHERE |
'b quantity' > ALL (SELECT |
'b quantity' |
|
6 |
|
FROM |
'books' AS 'int' |
|
7 |
|
WHERE |
'ext' 'b id' |
!= |
Решение 2.1.8.d
MS SQL I Решение 2.1.8.d
'b id')
1-- Вариант 1: использование ALL и подзапроса
2SELECT [b name],
3[b_quantity]
4 |
FROM |
[books] AS [ext] |
|
5 |
WHERE |
[b quantity] > ALL (SELECT |
[b quantity] |
6 |
|
FROM |
[books] AS [int] |
7 |
|
WHERE |
[ext] [b_id] != [int] [b_id]) |
1-- Вариант 2: использование общего табличного выражения и RANK
2WITH [ranked]
3AS (SELECT [b name],
4 |
|
[b_quantity], |
5 |
|
RANK() |
6 |
|
OVER ( |
7 |
|
ORDER BY [b quantity] DESC) AS [rank] |
8 |
FROM |
[books]), |
9[counted]
10AS (SELECT [rank]
11 |
|
COUNT(*) AS [competitors] |
12 |
FROM |
[ranked] |
13GROUP BY [rank])
14SELECT [b name],
15[b quantity]
16 FROM [ranked]
17JOIN [counted]
18ON [ranked] [rank] = [counted] [rank]
19WHERE [counted] [rank] = 1
20AND [counted] [competitors] = 1
|
Oracl |
і |
Решение 2.1.8.d |
I |
|
|
e |
|
|
||||
|
|
|
|
|
|
|
1 |
|
-- Вариант 1: использование ALL и подзапроса |
||||
2 |
|
SELECT "b name" |
|
|
||
3 |
|
|
|
"b_quanti ty" |
|
|
4 |
|
FROM |
"books" "ext" |
|
||
5 |
|
WHERE |
"b quantity" > ALL (SELECT "b quantity" |
|||
6 |
|
|
|
|
FROM |
"books" "int" |
7 |
|
|
|
|
WHERE |
"ext" "b id" != "int" "b id" |
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 53/545
Пример 8: поиск множества минимальных и максимальных значений
Oracl |
і Решение 2.1.8.d (продолжение) | |
|||
e |
||||
|
|
|
||
1 |
-- Вариант 2: использование общего табличного выражения и RANK |
|||
2 |
WITH "ranked" |
|
||
3 |
|
AS (SELECT "b name", |
||
4 |
|
|
"b_quantity", |
|
5 |
|
|
RANK() |
|
6 |
|
|
OVER ( |
|
7 |
|
|
ORDER BY "b quantity" DESC) AS "rank" |
|
8 |
|
FROM |
"books" , |
|
9 |
|
"counted" |
|
|
10 |
|
AS (SELECT "rank", |
||
11 |
|
|
COUNT(*) AS "competitors" |
|
12 |
|
FROM |
"ranked" |
|
13 |
|
GROUP |
BY "rank") |
|
14 |
SELECT "b name" |
|
||
15 |
|
"b_quanti ty" |
||
16 |
FROM |
"ranked" |
|
|
17 |
|
JOIN "counted" |
||
18 |
|
ON "ranked" "rank" = "counted" "rank" |
||
19 |
WHERE |
"counted" "rank" = 1 |
||
20 |
|
AND "counted" "competitors" = 1 |
||
Первые варианты решения для всех трёх СУБД идентичны (только в Oracle здесь для именования таблицы между исходным именем и псевдонимом не должно быть ключевого слова AS). Теперь поясним, как это работает.
Одна и та же таблица books фигурирует в запросе 2.1.8.d дважды — под именем ext (для внешней части запроса) и int (для внутренней части запроса). Это нужно затем, чтобы СУБД могла применить условие выборки, представленное в строке 7: для каждой строки таблицы ext выбрать значение поля b_quantity из всех строк таблицы int кроме той строки, которая сейчас рассматривается в таблице ext.
Это фундаментальный принцип построения т.н. коррелирующих запросов, потому покажем логику работы СУБД графически. Итак, у нас есть семь книг с идентификаторами от 1 до 7:
Строка из таблицы ext |
Какие строки анализируются в таблице int |
1 |
2, 3, 4, 5, 6, 7 {т.е. все, кроме 1-й} |
2 |
1, 3, 4, 5, 6, 7 {т.е. все, кроме 2-й} |
3 |
1,2, 4, 5, 6, 7 {т.е. все, кроме 3-й} |
4 |
1,2, 3, 5, 6, 7 {т.е. все, кроме 4-й} |
5 |
1,2, 3, 4, 6, 7 {т.е. все, кроме 5-й} |
6 |
1,2, 3, 4, 5, 7 {т.е. все, кроме 6-й} |
7 |
1,2, 3, 4, 5, 6 {т.е. все, кроме 7-й} |
Выбрав соответствующие значения поля b_quantity, СУБД проверяет, чтобы значение, выбранное из таблицы ext было больше каждого из значений, выбранных из таблицы int:
Значение ext.b_quantity |
Набор значений int.b_quantity |
2 |
3, 5, 1,3, 12, 7 |
3 |
2, 5, 1,3, 12, 7 |
5 |
2, 3, 1,3, 12, 7 |
1 |
2, 3, 5, 3, 12, 7 |
3 |
2, 3, 5, 1, 12, 7 |
12 |
2, 3, 5, 1, 3, 7 |
7 |
2, 3, 5, 1,3, 12 |
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 54/545