Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Пример 5: использование функций SUM, MIN, MAX, AVG

2.1.5. Пример 5: использование функций SUM, MIN, MAX, AVG

Задача 2.1.5.a{31}: показать общее (сумму), минимальное, максимальное и среднее значение количества экземпляров книг в библиотеке.

Ожидаемый результат 2.1.5.a.

чР Решение 2.1.5.a{31}.

Втакой простой формулировке эта задача решается запросом, в котором достаточно перечислить соответствующие функции, передав им в качестве параметра поле b_quantity (и только в MS SQL Server придётся сделать небольшую

доработку).

Обратите внимание на 4-ю строку в запросе 2.1.5.a для MS SQL Server: без приведения функцией CAST значения количества книг к дроби, итоговый результат работы функции AVG будет некорректным (это будет целое

число), т.к. MS SQL Server выбирает тип данных результата на основе типа данных входного параметра. Продемонстрируем это.

1SELECT SUM [b_quantity] AS [sum],

2MIN([b_quantity]) AS [min],

3MAX([b_quantity]) AS [max],

4AVG([b_quantity]) AS [avg]

5FROM [books]

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 35/545

Пример 5: использование функций SUM, MIN, MAX, AVG

сумма, поделённая на количество значений) может произойти переполнение разрядной сетки.

Исследование 2.1.5. EXP.A: рассмотрим реакцию различных СУБД на ситуацию переполнения разрядной сетки.

Создадим в БД «Исследование» таблицу overflow с одним числовым целочисленным полем:

MySQL MS SQL Server Oracle

Рисунок 2.1.g — Таблица overflow во всех трёх СУБД

Поместим в созданную таблицу три максимальных значения для её поля x:

1-- Запрос 1: SUM

2SELECT SUM [x]) AS [sum]

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 36/545

Пример 5: использование функций SUM, MIN, MAX, AVG

3 FROM [overflow]

1-- Запрос 2: AVG

2SELECT AVG [x]) AS [avg]

3FROM [overflow]

Теперь выполним для каждой СУБД запросы на получение суммы значений

MySQL Исследование 2.1.5.EXP.A

1-- Запрос 1: SUM

2SELECT SUM('x') AS 'sum'

FROM 'overflow'

Исследование 2.1.5.EXP.A

1-- Запрос 2: AVG

2SELECT AVG('x') AS 'avg' FROM 'overflow'

из поля x и среднего значения в поле х:

MS SQL

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 37/545

Пример 5: использование функций SUM, MIN, MAX, AVG

2 SELECT SUM("x" AS "sum"

3 FROM "overflow"

1-- Запрос 2: AVG

2SELECT AVG("x" AS "avg"

3FROM "overflow"

MySQL и Oracle выполнят запросы 2.1.5.EXP.A и вернут корректные данные, а в MS SQL Server мы получим сообщение об ошибке:

Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int.

Это вполне логично, т.к. при попытке разместить в переменной типа INT трёхкратное максимальное значение типа INT, возникает переполнение разрядной

сетки.

MySQL и Oracle менее подвержены этому эффекту, т.к. у них «в запасе» есть DECIMAL и NUMBER, в формате которых и происходит вычисление. Но при достаточ-

ном объёме данных там тоже возникает переполнение разрядной сетки.

Особая опасность этой ошибки состоит в том, что на стадии разработки и поверхностного тестирования БД она не проявляется, и лишь со временем, когда у реальных пользователей накопится большой объём данных, в какой-то момент ранее прекрасно работавшие запросы перестают работать.

Исследование 2.1.5.EXP.B. Чтобы больше не возвращаться к особенностям работы агрегирующих функций, рассмотрим их поведение в случае наличия в анализируемом поле NULL-значений, а также в случае пустого набора входных значений.

Используем ранее созданную таблицу table_with_nulls{26}. В ней попрежнему находятся следующие данные:

x

_1 ___

_1 ___

_2 ___

NULL

Выполним запросы 2.1.5.EXP.B:

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 38/545

Пример 5: использование функций SUM, MIN, MAX, AVG

Все запросы 2.1.5.EXP.B возвращают почти одинаковый результат (разница только в количестве знаков после запятой в значении функции AVG: у MySQL там четыре знака, у MS SQL Server и Oracle — 14 и 38 знаков соответственно):

Как легко заметить из полученных данных, ни одна из функций не учитывает NULL-значения.

Исследование 2.1.5.EXP.C. Последний эксперимент будет заключаться в применении к выборке такого условия, которому не соответствует ни один ряд: таким образом в выборке окажется пустое множество строк.

Здесь все три СУБД также работают одинаково, наглядно демонстрируя, что на пустом множестве функции SUM, MIN, MAX, AVG возвращают NULL:

Также обратите внимание, что при вычислении среднего значения не произошло ошибки деления на ноль.

Логику работы на пустом множестве значений функции COUNT мы уже рассмотрели ранее (см. исследование 2.1.3.EXP.B{28}).

& Задание 2.1.5.TSK.A: показать первую и последнюю даты выдачи книги читателю.

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 39/545