Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 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 |
|
|||||||
Язык программирования С++ |
|
3 |
5 |
|
|||||||
|
Евгений Онегин |
|
|
|
2 |
1 |
|
||||
Психология программирования |
|
1 |
4 |
|
|||||||
Легко заметить, что нумерация строк произошла до упорядочивания, и первый номер был присвоен книге «Евгений Онегин». При этом вариант с функцией ROW_NUMBER работает корректно.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 45/545
Пример 8: поиск множества минимальных и максимальных значений
Решение 2.1.8.c{43}.
Если нам нужно показать все книги, представленные равным максимальным количеством экземпляров, нужно выяснить это максимальное количество и использовать полученное значение как условие выборки.
MySQL Решение 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`) |
MS SQL Решение 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 Решение 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") |
8WHERE "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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 46/545
Пример 8: поиск множества минимальных и максимальных значений
Функция RANK позволяет ранжировать строки выборки (т.е. расставить их на 1-е, 2-е, 3-е, 4-е и так далее места) по указанному условию (в нашем случае — по убыванию количества экземпляров книг). Книги с одинаковыми количествами экземпляров будут занимать одинаковые места, а на первом месте будут книги с максимальным количеством экземпляров. Остаётся только показать эти книги, занявшие первое место.
Для наглядности рассмотрим, что возвращает подзапрос, представленный строками 4-7 второго варианта решения для MS SQL Server и Oracle:
MS SQL Решение 2.1.8.c (фрагмент запроса)
1SELECT [b_name],
2[b_quantity],
3RANK() OVER (ORDER BY [b_quantity] DESC) AS [rn]
4 FROM [books]
Oracle Решение 2.1.8.c (фрагмент запроса)
1SELECT "b_name",
2"b_quantity",
3RANK() OVER (ORDER BY "b_quantity" DESC) AS "rn"
4FROM "books"
b_name |
b_quantity |
rn |
Курс теоретической физики |
12 |
1 |
Искусство программирования |
7 |
2 |
Основание и империя |
5 |
3 |
Сказка о рыбаке и рыбке |
3 |
4 |
Язык программирования С++ |
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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 47/545
Пример 8: поиск множества минимальных и максимальных значений
Решение 2.1.8.d{43}.
Чтобы найти «абсолютного рекордсмена» по количеству экземпляров, мы используем функцию работы с множествами ALL, которая позволяет сравнить некоторое значение с каждым элементом множества.
MySQL Решение 2.1.8.d
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`) |
|
|
|
|
|
|
MS SQL Решение 2.1.8.d
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
Oracle Решение 2.1.8.d
1-- Вариант 1: использование ALL и подзапроса
2SELECT "b_name",
3"b_quantity"
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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 48/545
Пример 8: поиск множества минимальных и максимальных значений
Oracle Решение 2.1.8.d (продолжение)
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
Первые варианты решения для всех трёх СУБД идентичны (только в 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 в примерах |
© EPAM Systems, RD Dep, 2016–2018 Стр: 49/545 |
||