Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 7: использование составных условий
MS SQL Решение 2.1.7.a
1-- Вариант 1: использование BETWEEN
2SELECT [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: использование двойного неравенства
2SELECT [b_name],
3[b_year],
4[b_quantity]
5 |
|
FROM |
[books] |
6 |
|
WHERE |
[b_year] >= 1990 |
|
|
|
|
7AND [b_year] <= 2000
8AND [b_quantity] >= 3
Oracle Решение 2.1.7.a
1-- Вариант 1: использование BETWEEN
2SELECT "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: использование двойного неравенства
2SELECT "b_name",
3"b_year",
4"b_quantity"
5 FROM "books"
6WHERE "b_year" >= 1990
7AND "b_year" <= 2000
8AND "b_quantity" >= 3
Решение 2.1.7.b{39}.
Сначала рассмотрим правильный и неправильный вариант решения, а потом поясним, в чём проблема с неправильным.
Правильный вариант:
MySQL Решение 2.1.7.b
1SELECT `sb_id`,
2`sb_start`
3 |
|
FROM |
`subscriptions` |
4 |
|
WHERE |
`sb_start` >= '2012-06-01' |
5 |
|
|
AND `sb_start` < '2012-09-01' |
MS SQL Решение 2.1.7.b
1SELECT [sb_id],
2[sb_start]
3 |
|
FROM |
[subscriptions] |
4 |
|
WHERE |
[sb_start] >= '2012-06-01' |
5 |
|
|
AND [sb_start] < '2012-09-01' |
Oracle Решение 2.1.7.b
1SELECT "sb_id",
2"sb_start"
3 |
|
FROM |
"subscriptions" |
4 |
|
WHERE |
"sb_start" >= TO_DATE('2012-06-01', 'yyyy-mm-dd') |
5 |
|
|
AND "sb_start" < TO_DATE('2012-09-01', 'yyyy-mm-dd') |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 40/545
Пример 7: использование составных условий
Указание правой границы диапазона дат в виде строгого неравенства удобно потому, что не надо запоминать или вычислять последний день месяца (особенно актуально для февраля) или писать конструкции вида 23:59:59.9999 (если надо учесть ещё и время). Какой бы частью даты мы ни оперировали (год, месяц, день, час, минута, секунда, доли секунд), всегда можно сформировать следующее значение, не входящее в искомый диапазон, и использовать строгое неравенство.
Также обратите внимание на строки 4-5 запросов 2.1.7.b: MySQL и MS SQL Server допускают строковое указание даты (и автоматически выполняют необходимые преобразования), в то время как Oracle требует явного преобразования строкового представления даты к соответствующему типу данных.
Пришло время рассмотреть очень распространённый, но неправильный вариант решения, который возвращает корректный результат, но является фатальным для производительности. Вместо того, чтобы получить две константы (начала и конца диапазона дат) и напрямую сравнивать с ними значения анализируемого столбца таблицы (используя индекс), СУБД вынуждена для каждой записи в таблице выполнять два преобразования, и затем сравнивать результаты с заданными константами (напрямую, без использования индекса, т.к. в таблице нет индексов по результатам извлечения года и месяца из даты).
MySQL Решение 2.1.7.b (неправильный с точки зрения производительности вариант)
1SELECT `sb_id`,
2`sb_start`
3 FROM `subscriptions`
4WHERE YEAR(`sb_start`) = 2012
5AND MONTH(`sb_start`) BETWEEN 6 AND 8
MS SQL Решение 2.1.7.b (неправильный с точки зрения производительности вариант)
1SELECT [sb_id],
2[sb_start]
3 FROM [subscriptions]
4WHERE YEAR([sb_start]) = 2012
5AND MONTH([sb_start]) BETWEEN 6 AND 8
Oracle Решение 2.1.7.b (неправильный с точки зрения производительности вариант)
1SELECT "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. Продемонстрируем разницу в производительности СУБД при выполнении запросов из правильного и неправильного решения.
Создадим в БД «Исследование» ещё одну таблицу с одним полем для хранения даты, над которым будет построен индекс.
dm MySQL |
dm SQLServ er2012 |
dm Oracle |
|||
|
dates |
|
dates |
|
dates |
|
|
|
|
|
|
|
«column» |
|
«column» |
|
«column» |
|
d: DATE |
|
d: date |
|
d: DATE |
|
|
|
|
|
|
|
«index» |
|
«index» |
|
«index» |
|
+ idx_d(DATE) |
|
+ idx_d(date) |
|
+ idx_d(DATE) |
MySQL MS SQL Server Oracle
Рисунок 2.1.h — Таблица dates во всех трёх СУБД
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 41/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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 42/545
Пример 8: поиск множества минимальных и максимальных значений
2.1.8.Пример 8: поиск множества минимальных и максимальных значений
Задача 2.1.8.a{нет}: показать книгу, представленную в библиотеке максимальным количеством экземпляров.
В самой формулировке этой задачи скрывается ловушка: в такой формулировке задача 2.1.8.a не имеет правильного решения (потому оно и не будет показано). На самом деле, здесь не одна задача, а три.
Задача 2.1.8.b{44}: показать просто одну любую книгу, количество экземпляров которой максимально (равно максимуму по всем книгам).
Задача 2.1.8.c{46}: показать все книги, количество экземпляров которых максимально (и одинаково для всех этих показанных книг).
Задача 2.1.8.d{48}: показать книгу (если такая есть), количество экземпляров которой больше, чем у любой другой книги.
Ожидаемые результаты по этим трём задачам таковы (причём для первой результат может меняться, т.к. мы не указываем, какую именно книгу из числа соответствующих условию показывать).
Ожидаемый результат 2.1.8.b.
b_name |
b_quantity |
Курс теоретической физики |
12 |
Ожидаемый результат 2.1.8.c. |
|
|
|
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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 43/545
Пример 8: поиск множества минимальных и максимальных значений
Возможный ожидаемый результат 2.1.8.c.
b_name |
b_quantity |
Евгений Онегин |
12 |
Курс теоретической физики |
12 |
Возможный ожидаемый результат 2.1.8.d.
b_name |
b_quantity |
{пустое множество, запрос вернул ноль рядов}
Решение 2.1.8.b{43}.
Эта задача — самая простая: нужно упорядочить выборку по убыванию поля b_quantity и взять первый ряд выборки.
MySQL Решение 2.1.8.b
1SELECT `b_name`,
2`b_quantity`
3 |
|
FROM |
`books` |
4 |
|
ORDER |
BY `b_quantity` DESC |
5 |
|
LIMIT |
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 Решение 2.1.8.b
1SELECT "b_name",
2"b_quantity"
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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 44/545