Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 7: использование составных условий
MS SQL І Решение 2.1.7.a
1 |
-- Вариант 1: использование BETWEEN |
||
2 |
SELECT [b |
name] |
|
3 |
|
[b |
year] |
4 |
|
[b_quantity] |
|
5 |
FROM |
[books] |
|
6 |
WHERE |
[b |
year] BETWEEN 1990 AND 2000 |
7 |
|
AND |
[b_quantity] >= 3 |
1 |
-- Вариант 2: использование двойного неравенства |
||
2 |
SELECT [b |
name] |
|
3 |
|
[b |
year] |
4 |
|
[b |
quantity] |
5 |
FROM |
[books] |
|
6 |
WHERE |
[b |
year] >= 1990 |
7 |
|
AND [b year] <= 2000 |
|
8 |
|
AND [b quantity] >= 3 |
|
Oracle I Решение 2.1.7.a
1-- Вариант 1: использование BETWEEN
2SELECT "b name",
3"b_year",
4"b_quanti ty"
5 |
FROM |
"books" |
6 |
WHERE |
"b year" BETWEEN 1990 AND 2000 |
7 |
|
AND "b_quantity" >= 3 |
1-- Вариант 2: использование двойного неравенства
2SELECT "b name",
3"b_year",
4"b_quanti ty"
5 |
FROM |
"books" |
6 |
WHERE |
"b year" >= 1990 |
7AND "b year" <= 2000
8AND "b quantity" >= 3
ЧР’ Решение 2.1.7. b{39}.
Сначала рассмотрим правильный и неправильный вариант решения, а потом поясним, в чём проблема с неправильным.
Правильный вариант:
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 45/545
Пример 7: использование составных условий
Указание правой границы диапазона дат в виде строгого неравенства удобно потому, что не надо запоминать или вычислять последний день месяца (особенно актуально для февраля) или писать конструкции вида 23:59:59.9999 (если надо учесть ещё и время). Какой бы частью даты мы ни оперировали (год, месяц, день, час, минута, секунда, доли секунд), всегда можно сформировать следующее значение, не входящее в искомый диапазон, и использовать строгое неравенство.
Также обратите внимание на строки 4-5 запросов 2.1.7.b: MySQL и MS SQL Server допускают строковое указание даты (и автоматически выполняют необходимые преобразования), в то время как Oracle требует явного преобразования строкового представления даты к соответствующему типу данных.
Пришло время рассмотреть очень распространённый, но неправильный вариант решения, который возвращает корректный результат, но является фатальным для производительности. Вместо того, чтобы получить две константы (начала и конца диапазона дат) и напрямую сравнивать с ними значения анализируемого столбца таблицы (используя индекс), СУБД вынуждена для каждой записи в таблице выполнять два преобразования, и затем сравнивать результаты с заданными константами (напрямую, без использования индекса, т.к. в таблице нет индексов по результатам извлечения года и месяца из даты).
Решение 2.1.7.b |
с точки |
1SELECT 'sb_id',
2
'sb_start'
3 FROM 'subscriptions'
4WHERE YEAR('sb_start') = 2012
5
AND MONTH('sb start') BETWEEN 6 AND 8
MS SQL і Решение 2.1.7.b (неправильный с точки зрения производительности вариант)
1 |
SELECT |
[sb_id], |
2 |
|
[sb_start] |
3 |
FROM |
[subscriptions] |
4 |
WHERE |
YEAR([sb_start] = 2012 |
5 |
|
AND MONTH([sb start]) BETWEEN 6 AND 8 |
Oracle і Решение 2.1.7.b (неправильный с точки зрения производительности вариант)
1 |
SELECT "sb id" |
|
|
|
2 |
|
"sb_start" |
|
|
3 |
FROM |
"subscriptions" |
|
|
4 |
WHERE |
EXTRACT(year FROM |
"sb_start" = 2012 |
|
5 |
|
AND EXTRACT(month |
FROM "sb start") BETWEEN 6 AND 8 |
|
|
|
|
|
|
Исследование 2.1.7.EXP.A. Продемонстрируем разницу в производительности СУБД при выполнении запросов из правильного и неправильного решения.
Создадим в БД «Исследование» ещё одну таблицу с одним полем для хранения даты, над которым будет построен индекс.
dates |
dates |
«column» |
«column» |
d: DATE |
d: DATE |
«index» |
«index» |
+ idx_d(DATE) |
+ idx_d(DATE) |
|
|
MySQL Oracle
Рисунок 2.1.h — Таблица dates во всех трёх СУБД
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 46/545
Пример 7: использование составных условий
Наполним получившуюся таблицу миллионом записей и выполним по сто раз каждый вариант запроса 2.1.7.b — правильный и неправильный.
Медианные значения времени выполнения таковы:
|
MySQL |
MS SQL Server |
Oracle |
|
Правильное реше- |
0.003 |
0.059 |
0.674 |
|
ние |
||||
|
|
|
||
Неправильное ре- |
0.436 |
0.086 |
0.966 |
|
шение |
||||
|
|
|
||
Разница (раз) |
145.3 |
1.4 |
1.5 |
Даже такое тривиальное исследование показывает, что запрос, требующий извлечения части поля, приводит к падению производительности от примерно полутора до примерно ста пятидесяти раз.
К сожалению, иногда у нас нет возможности указать диапазон дат (например, нужно будет показать информацию о книгах, выданных с 3-го по 7-е число каждого месяца каждого года или о книгах, выданных в любое воскресенье, см. задачу 2.3.3. b{193}). Если таких запросов много, стоит либо хранить дату в виде отдельных полей (год, месяц, число, день недели), либо создавать т.н. «вычисляемые поля» для года, месяца, числа, дня недели, и над этими полями также строить индекс.
& Задание 2.1.7.TSK.A: показать книги, количество экземпляров которых меньше среднего по библиотеке.
Задание 2.1.7.TSK.B: показать идентификаторы и даты выдачи книг за первый год работы библиотеки (первым годом работы библиотеки считать все даты с первой выдачи книги по 31 -е декабря (включительно)
того года, когда библиотека начала работать).
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 47/545
Пример 8: поиск множества минимальных и максимальных значений
2.1.8.Пример 8: поиск множества минимальных и максимальных значений
Задача 2.1.8.а{нет}: показать книгу, представленную в библиотеке максимальным количеством экземпляров.
В самой формулировке этой задачи скрывается ловушка: в такой формулировке задача 2.1.8.а не имеет правильного решения (потому оно и не будет показано). На самом деле, здесь не одна задача, а три.
ОЗадача 2.1.8.b{44}: показать просто одну любую книгу, количество экземпляров которой максимально (равно максимуму по всем книгам).
ОЗадача 2.1.8.c{46}: показать все книги, количество экземпляров которых максимально (и одинаково для всех этих показанных книг).
ОЗадача 2.1.8.d{48}: показать книгу (если такая есть), количество экземпляров которой больше, чем у любой другой книги.
Ожидаемые результаты по этим трём задачам таковы (причём для первой результат может меняться, т.к. мы не указываем, какую именно книгу из числа соответствующих условию показывать).
Ожидаемый результат 2.1.8.bc.
b_name b_quantity
Курс теоретической физики 12
Ожидаемый результат 2.1.8.d.
b_name b_quantity
Курс теоретической физики 12
Кажется странным, не так ли? Три разных задачи, но три одинаковых результата. Да, при том наборе данных, который сейчас есть в базе данных, все три решения дают одинаковый результат, но стоит, например, привезти в библиотеку ещё десять экземпляров «Евгения Онегина» (чтобы их тоже стало 12, как и у книги «Курс теоретической физики»), как результат становится
таким (убедитесь в этом сами, сделав соответствующую правку в базе данных).
Возможный ожидаемый результат 2.1.8.b.
b_name |
b_quantity |
Евгений Онегин |
12 |
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 48/545
Пример 8: поиск множества минимальных и максимальных значений
Возможный ожидаемый результат 2.1.8.с.
b_name |
b_quantity |
Евгений Онегин |
12 |
Курс теоретической физики |
12 |
Возможный ожидаемый результат 2.1.8.d.
b_name |
b_quantity |
{пустое множество, запрос вернул ноль рядов}
уЦ7
Л-/* Решение 2.1.8.b{43}.
Р ^<4
Эта задача — самая простая: нужно упорядочить выборку по убыванию поля b_quantity и взять первый ряд выборки.
MySQL і Решение 2.1.8.b і
1SELECT 'b name',
2'b_quanti ty' 'books'
3FROM
4ORDER BY 'b quantity' DESC
5LIMIT 1
MS SQL І Решение 2.1.8.b
1-- Вариант 1: использование TOP
2SELECT TOP 1 [b name],
3 |
|
[b_quantity] |
4 |
FROM |
[books] |
5 |
ORDER BY [b_quantity] DESC |
|
1 |
-- Вариант 2: использование FETCH NEXT |
|
2 |
SELECT |
[b name], |
3 |
|
[b_quantity] |
4 |
FROM |
[books] |
5 |
ORDER BY [b quantity] DESC |
|
6 |
OFFSET |
0 ROWS |
7 |
FETCH |
NEXT 1 ROWS ONLY |
Oracle I Решение 2.1.8.b
1SELECT "b name"
2"b_quanti ty"
3 |
FROM (SELECT "b name" |
|
4 |
|
"b quantity" |
5 |
|
ROW NUMBER() OVER(ORDER BY "b quantity" DESC) AS "rn" |
6 |
FROM |
"books" |
7WHERE "rn" = 1
ВMySQL всё просто: достаточно указать, какое количество рядов (LIMIT 1) возвращать из упорядоченной выборки.
ВMS SQL Server вариант с TOP 1 вполне аналогичен решению для MySQL:
мы говорим СУБД, что нас интересует только один первый («верхний») ряд. Второй запрос 2.1.8.b для MS SQL Server (строки 5-6) говорит СУБД пропустить ноль рядов и вернуть один следующий ряд.
Решение для Oracle самое нетривиальное. Версия Oracle 12c уже поддерживает синтаксис, аналогичный MS SQL Server, но мы работаем с версией Oracle 11gR2, и потому вынуждены реализовывать классический вариант.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 49/545