Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 4: использование функции COUNT в запросе с условием
Решение 2.1.4.b{29}.
Здесь мы расширяем решение{29} задачи 2.1.4.a{29}, добавляя ключевое слово DISTINCT в параметр функции COUNT, что обеспечивает подсчёт неповторяющихся
значений поля sb_book.
MySQL |
Решение 2.1.4.b |
||
1 |
SELECT |
COUNT(DISTINCT `sb_book`) AS `in_use` |
|
2 |
FROM |
`subscriptions` |
|
3 |
WHERE |
`sb_is_active` = 'Y' |
|
|
|
||
MS SQL |
Решение 2.1.4.b |
||
1 |
SELECT |
COUNT(DISTINCT [sb_book]) AS [in_use] |
|
2 |
FROM |
[subscriptions] |
|
3 |
WHERE |
[sb_is_active] = 'Y' |
|
|
|
|
|
Oracle |
|
Решение 2.1.4.b |
|
1 |
SELECT |
COUNT(DISTINCT "sb_book") AS "in_use" |
|
2 |
FROM |
"subscriptions" |
|
3 |
WHERE |
"sb_is_active" = 'Y' |
|
Задание 2.1.4.TSK.A: показать, сколько всего раз читателям выдавались книги.
Задание 2.1.4.TSK.B: показать, сколько читателей брало книги в библиотеке.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 30/545
Пример 5: использование функций SUM, MIN, MAX, AVG
2.1.5. Пример 5: использование функций SUM, MIN, MAX, AVG
Задача 2.1.5.a{31}: показать общее (сумму), минимальное, максимальное и среднее значение количества экземпляров книг в библиотеке.
Ожидаемый результат 2.1.5.a.
sum |
min |
max |
avg |
33 |
1 |
12 |
4.7143 |
Решение 2.1.5.a{31}.
В такой простой формулировке эта задача решается запросом, в котором достаточно перечислить соответствующие функции, передав им в качестве параметра поле b_quantity (и только в MS SQL Server придётся сделать небольшую доработку).
MySQL Решение 2.1.5.a
1SELECT SUM(`b_quantity`) AS `sum`,
2MIN(`b_quantity`) AS `min`,
3MAX(`b_quantity`) AS `max`,
4AVG(`b_quantity`) AS `avg`
5 FROM `books`
MS SQL Решение 2.1.5.a
1SELECT SUM([b_quantity]) AS [sum],
2MIN([b_quantity]) AS [min],
3MAX([b_quantity]) AS [max],
4AVG(CAST([b_quantity] AS FLOAT)) AS [avg]
5 FROM [books]
Oracle Решение 2.1.5.a
1SELECT SUM("b_quantity") AS "sum",
2MIN("b_quantity") AS "min",
3MAX("b_quantity") AS "max",
4AVG("b_quantity") AS "avg"
5FROM "books"
Обратите внимание на 4-ю строку в запросе 2.1.5.a для MS SQL Server: без приведения функцией CAST значения количества книг к дроби, итоговый результат работы функции AVG будет некорректным (это будет целое число), т.к. MS SQL Server выбирает тип данных результата на основе типа данных входного параметра. Продемонстрируем это.
MS SQL Решение 2.1.5.a (пример запроса с ошибкой)
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 в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 31/545
Пример 5: использование функций SUM, MIN, MAX, AVG
Получится:
sum |
min |
max |
avg |
|
33 |
1 |
12 |
4 |
|
|
А должно быть: |
|||
|
|
|
|
|
sum |
min |
max |
avg |
|
33 |
1 |
12 |
4.7143 |
|
Стоит упомянуть ещё одну опасную ошибку: очень легко забыть, что при вычислении суммы и среднего значения (которое определяется как сумма, поделённая на количество значений) может произойти переполнение разрядной сетки.
Исследование 2.1.5.EXP.A: рассмотрим реакцию различных СУБД на ситуацию переполнения разрядной сетки.
Создадим в БД «Исследование» таблицу overflow с одним числовым целочисленным полем:
dm MySQL |
dm SQLServ er2012 |
dm Oracle |
ov erflow |
ov erflow |
ov erflow |
«column» |
«column» |
«column» |
x: INT |
x: int |
x: NUMBER(10) |
MySQL MS SQL Server Oracle
Рисунок 2.1.g — Таблица overflow во всех трёх СУБД
Поместим в созданную таблицу три максимальных значения для её поля x:
x |
|
x |
|
x |
2147483647 |
|
2147483647 |
|
9999999999 |
2147483647 |
|
2147483647 |
|
9999999999 |
2147483647 |
|
2147483647 |
|
9999999999 |
MySQL |
MS SQL Server |
Oracle |
||
Теперь выполним для каждой СУБД запросы на получение суммы значений из поля x и среднего значения в поле x:
MySQL Исследование 2.1.5.EXP.A
1-- Запрос 1: SUM
2SELECT SUM(`x`) AS `sum`
3 FROM `overflow`
1-- Запрос 2: AVG
2SELECT AVG(`x`) AS `avg`
3FROM `overflow`
MS SQL Исследование 2.1.5.EXP.A
1-- Запрос 1: SUM
2SELECT SUM([x]) AS [sum]
3 FROM [overflow]
1-- Запрос 2: AVG
2SELECT AVG([x]) AS [avg]
3FROM [overflow]
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 32/545
Пример 5: использование функций SUM, MIN, MAX, AVG
Oracle Исследование 2.1.5.EXP.A
1-- Запрос 1: SUM
2SELECT 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 Исследование 2.1.5.EXP.B
1SELECT SUM(`x`) AS `sum`,
2MIN(`x`) AS `min`,
3MAX(`x`) AS `max`,
4AVG(`x`) AS `avg`
5 FROM `table_with_nulls`
MS SQL Исследование 2.1.5.EXP.B
1SELECT SUM([x]) AS [sum],
2MIN([x]) AS [min],
3MAX([x]) AS [max],
4AVG(CAST([x] AS FLOAT)) AS [avg]
5 FROM [table_with_nulls]
Oracle Исследование 2.1.5.EXP.B
1SELECT SUM("x") AS "sum",
2MIN("x") AS "min",
3MAX("x") AS "max",
4AVG("x") AS "avg"
5FROM "table_with_nulls"
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 33/545
Пример 5: использование функций SUM, MIN, MAX, AVG
Все запросы 2.1.5.EXP.B возвращают почти одинаковый результат (разница только в количестве знаков после запятой в значении функции AVG: у MySQL там четыре знака, у MS SQL Server и Oracle — 14 и 38 знаков соответственно):
sum |
min |
max |
avg |
4 |
1 |
2 |
1.3333 |
Как легко заметить из полученных данных, ни одна из функций не учитывает NULL-значения.
Исследование 2.1.5.EXP.C. Последний эксперимент будет заключаться в применении к выборке такого условия, которому не соответствует ни один ряд: таким образом в выборке окажется пустое множество строк.
MySQL Исследование 2.1.5.EXP.C
1SELECT SUM(`x`) AS `sum`,
2MIN(`x`) AS `min`,
3MAX(`x`) AS `max`,
4AVG(`x`) AS `avg`
5 |
|
FROM |
`table_with_nulls` |
6 |
|
WHERE |
`x` < 0 |
|
|
|
|
MS SQL Исследование 2.1.5.EXP.C
1SELECT SUM([x]) AS [sum],
2MIN([x]) AS [min],
3MAX([x]) AS [max],
4AVG(CAST([x] AS FLOAT)) AS [avg]
|
5 |
|
FROM |
[table_with_nulls] |
|
|
6 |
|
WHERE |
[x] < 0 |
|
|
|
|
|
||
|
Oracle |
|
Исследование 2.1.5.EXP.C |
||
1SELECT SUM("x") AS "sum",
2MIN("x") AS "min",
3MAX("x") AS "max",
4AVG("x") AS "avg"
5 |
|
FROM |
"table_with_nulls" |
6 |
|
WHERE |
"x" < 0 |
Здесь все три СУБД также работают одинаково, наглядно демонстрируя, что на пустом множестве функции SUM, MIN, MAX, AVG возвращают NULL:
sum |
min |
max |
avg |
NULL |
NULL |
NULL |
NULL |
Также обратите внимание, что при вычислении среднего значения не произошло ошибки деления на ноль.
Логику работы на пустом множестве значений функции COUNT мы уже рассмотрели ранее (см. исследование 2.1.3.EXP.B{28}).
Задание 2.1.5.TSK.A: показать первую и последнюю даты выдачи книги читателю.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 34/545