Агрегатные функции в запросах sql. Агрегатные функции в языке SQL. Агрегатная функция AVG
В прошлый раз мы научились складывать и вычитать дроби (см. урок «Сложение и вычитание дробей »). Наиболее сложным моментом в тех действиях было приведение дробей к общему знаменателю.
Теперь настала пора разобраться с умножением и делением. Хорошая новость состоит в том, что эти операции выполняются даже проще, чем сложение и вычитание. Для начала рассмотрим простейший случай, когда есть две положительные дроби без выделенной целой части.
Чтобы умножить две дроби, надо отдельно умножить их числители и знаменатели. Первое число будет числителем новой дроби, а второе - знаменателем.
Чтобы разделить две дроби, надо первую дробь умножить на «перевернутую» вторую.
Обозначение:
Из определения следует, что деление дробей сводится к умножению. Чтобы «перевернуть» дробь, достаточно поменять местами числитель и знаменатель. Поэтому весь урок мы будем рассматривать в основном умножение.
В результате умножения может возникнуть (и зачастую действительно возникает) сократимая дробь - ее, разумеется, надо сократить. Если после всех сокращений дробь оказалась неправильной, в ней следует выделить целую часть. Но чего точно не будет при умножении, так это приведения к общему знаменателю: никаких методов «крест-накрест», наибольших множителей и наименьших общих кратных.
По определению имеем:
Умножение дробей с целой частью и отрицательных дробей
Если в дробях присутствует целая часть, их надо перевести в неправильные - и только затем умножать по схемам, изложенным выше.
Если в числителе дроби, в знаменателе или перед ней стоит минус, его можно вынести за пределы умножения или вообще убрать по следующим правилам:
- Плюс на минус дает минус;
- Минус на минус дает плюс.
До сих пор эти правила встречались только при сложении и вычитании отрицательных дробей, когда требовалось избавиться от целой части. Для произведения их можно обобщить, чтобы «сжигать» сразу несколько минусов:
- Вычеркиваем минусы парами до тех пор, пока они полностью не исчезнут. В крайнем случае, один минус может выжить - тот, которому не нашлось пары;
- Если минусов не осталось, операция выполнена - можно приступать к умножению. Если же последний минус не зачеркнут, поскольку ему не нашлось пары, выносим его за пределы умножения. Получится отрицательная дробь.
Задача. Найдите значение выражения:
Все дроби переводим в неправильные, а затем выносим минусы за пределы умножения. То, что осталось, умножаем по обычным правилам. Получаем:
Еще раз напомню, что минус, который стоит перед дробью с выделенной целой частью, относится именно ко всей дроби, а не только к ее целой части (это касается двух последних примеров).
Также обратите внимание на отрицательные числа: при умножении они заключаются в скобки. Это сделано для того, чтобы отделить минусы от знаков умножения и сделать всю запись более аккуратной.
Сокращение дробей «на лету»
Умножение - весьма трудоемкая операция. Числа здесь получаются довольно большие, и чтобы упростить задачу, можно попробовать сократить дробь еще до умножения . Ведь по существу, числители и знаменатели дробей - это обычные множители, и, следовательно, их можно сокращать, используя основное свойство дроби. Взгляните на примеры:
Задача. Найдите значение выражения:
По определению имеем:
Во всех примерах красным цветом отмечены числа, которые подверглись сокращению, и то, что от них осталось.
Обратите внимание: в первом случае множители сократились полностью. На их месте остались единицы, которые, вообще говоря, можно не писать. Во втором примере полного сокращения добиться не удалось, но суммарный объем вычислений все равно уменьшился.
Однако ни в коем случае не используйте этот прием при сложении и вычитании дробей! Да, иногда там встречаются похожие числа, которые так и хочется сократить. Вот, посмотрите:
Так делать нельзя!
Ошибка возникает из-за того, что при сложении в числителе дроби появляется сумма, а не произведение чисел. Следовательно, применять основное свойство дроби нельзя, поскольку в этом свойстве речь идет именно об умножении чисел.
Других оснований для сокращения дробей просто не существует, поэтому правильное решение предыдущей задачи выглядит так:
Правильное решение:
Как видите, правильный ответ оказался не таким красивым. В общем, будьте внимательны.
Обыкновенные дробные числа впервые встречают школьников в 5 классе и сопровождают их на протяжении всей жизни, так как в быту зачастую требуется рассматривать или использовать какой-то объект не целиком, а отдельными кусками. Начало изучения этой темы - доли. Доли - это равные части , на которые разделен тот или иной предмет. Ведь не всегда получается выразить, допустим, длину или цену товара целым числом, следует принять во внимание части или доли какой-либо меры. Образованное от глагола «дробить» - разделять на части, и имея арабские корни, в VIII веке возникло само слово «дробь» в русском языке.
Дробные выражения продолжительное время считали самым сложным разделом математики. В XVII веке, при появлении первоучебников по математике, их называли «ломаные числа», что очень сложно отображалось в понимании людей.
Современному виду простых дробных остатков, части которых разделены именно горизонтальной чертой, впервые поспособствовал Фибоначчи - Леонардо Пизанский. Его труды датированы в 1202 году. Но цель этой статьи - просто и понятно объяснить читателю, как происходит умножение смешанных дробей с разными знаменателями.
Умножение дробей с разными знаменателями
Изначально стоит определить разновидности дробей
:
- правильные;
- неправильные;
- смешанные.
Далее нужно вспомнить, как происходит умножение дробных чисел с одинаковыми знаменателями. Само правило этого процесса несложно сформулировать самостоятельно: результатом умножения простых дробей с одинаковыми знаменателями является дробное выражение, числитель которой есть произведение числителей, а знаменатель - произведение знаменателей данных дробей. То есть, по сути, новый знаменатель есть квадрат одного из существующих изначально.
При умножении простых дробей с разными знаменателями для двух и более множителей правило не меняется:
a/ b * c/ d = a*c / b*d.
Единственное отличие в том, что образованное число под дробной чертой будет произведением разных чисел и, естественно, квадратом одного числового выражения его назвать невозможно.
Стоит рассмотреть умножение дробей с разными знаменателями на примерах:
- 8/ 9 * 6/ 7 = 8*6 / 9*7 = 48/ 63 = 16/2 1 ;
- 4/ 6 * 3/ 7 = 2/ 3 * 3/7 <> 2*3 / 3*7 = 6/ 21 .
В примерах применяются способы сокращения дробных выражений. Можно сокращать только числа числителя с числами знаменателя, рядом стоящие множители над дробной чертой или под ней сокращать нельзя.
Наряду с простыми дробными числами, существует понятие смешанных дробей. Смешанное число состоит из целого числа и дробной части, то есть является суммой этих чисел:
1 4/ 11 =1 + 4/ 11.
Как происходит перемножение
Предлагается несколько примеров для рассмотрения.
2 1/ 2 * 7 3/ 5 = 2 + 1/ 2 * 7 + 3/ 5 = 2*7 + 2* 3/ 5 + 1/ 2 * 7 + 1/ 2 * 3/ 5 = 14 + 6/5 + 7/ 2 + 3/ 10 = 14 + 12/ 10 + 35/ 10 + 3/ 10 = 14 + 50/ 10 = 14 + 5=19.
В примере используется умножение числа на обыкновенную дробную часть , записать правило для этого действия можно формулой:
a * b/ c = a*b / c.
По сути, такое произведение есть сумма одинаковых дробных остатков, а количество слагаемых указывает это натуральное число. Частный случай:
4 * 12/ 15 = 12/ 15 + 12/ 15 + 12/ 15 + 12/ 15 = 48/ 15 = 3 1/ 5.
Существует еще один вариант решения умножения числа на дробный остаток. Стоит просто разделить знаменатель на это число:
d * e/ f = e/ f: d.
Этим приемом полезно пользоваться, когда знаменатель делится на натуральное число без остатка или, как говорится, нацело.
Перевести смешанные числа в неправильные дроби и получить произведение ранее описанным способом:
1 2/ 3 * 4 1/ 5 = 5/ 3 * 21/ 5 = 5*21 / 3*5 =7.
В этом примере участвует способ представления смешанной дроби в неправильную, его также можно представить в виде общей формулы:
a b c = a * b + c / c, где знаменатель новой дроби образуется при умножении целой части со знаменателем и при сложении его с числителем исходного дробного остатка, а знаменатель остается прежним.
Этот процесс работает и в обратную сторону. Для выделения целой части и дробного остатка нужно поделить числитель неправильной дроби на ее знаменатель «уголком».
Умножение неправильных дробей производят общепринятым способом. Когда запись идет под единой дробной чертой, по мере необходимости нужно сделать сокращение дробей, чтобы уменьшить таким методом числа и проще посчитать результат.
В интернете существует множество помощников, чтобы решать даже сложные математические задачи в различных вариациях программ. Достаточное количество таких сервисов предлагают свою помощь при счете умножения дробей с разными числами в знаменателях - так называемые онлайн-калькуляторы для расчета дробей. Они способны не только умножить, но и произвести все остальные простейшие арифметические операции с обыкновенными дробями и смешанными числами. Работать с ним несложно, на странице сайта заполняются соответствующие поля, выбирается знак математического действия и нажимается «вычислить». Программа считает автоматически.
Тема арифметических действий с дробными числами актуальна на всем протяжении обучения школьников среднего и старшего звена. В старших классах рассматривают уже не простейшие виды, а целые дробные выражения , но знания правил по преобразованию и расчетам, полученные ранее, применяются в первозданном виде. Хорошо усвоенные базовые знания дают полную уверенность в удачном решении наиболее сложных задач.
В заключение имеет смысл привести слова Льва Николаевича Толстого, который писал: «Человек есть дробь. Увеличить своего числителя - свои достоинства, - не во власти человека, но всякий может уменьшить своего знаменателя - своё мнение о самом себе, и этим уменьшением приблизиться к своему совершенству».
Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций . Стандартом предусмотрены следующие агрегатные функции:
Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(<имя поля>) состоит в том, что вторая при подсчете не учитывает NULL-значения.
Пример. Найти минимальную и максимальную цену на персональные компьютеры:
Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:
Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):
Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).
Для того, чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно использовать параметр DISTINCT . Другой параметр ALL используется по умолчанию и предполагает подсчет всех возвращаемых значений в столбце. Оператор,
Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY , синтаксически следующего после предложения WHERE .
Предложение GROUP BY
Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM) . Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT , должны быть включены в агрегатные функции , и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY . В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После этого к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.Если при наличии предложения GROUP BY , в предложении SELECT отсутствуют агрегатные функции , то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:
| SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model; |
В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средние значения цены для каждой группы. Результатом выполнения запроса будет следующая таблица:
| model | Qty_model | Avg_price |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).
Существует несколько определенных правил выполнения агрегатных функций :
- Если в результате выполнения запроса не получено ни одной строки (или не одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других функций - NULL.
- Аргумент агрегатной функции не может сам содержать агрегатные функции (функция от функции). Т.е. в одном запросе нельзя, скажем, получить максимум средних значений.
- Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений.
- Если при выполнении функции SUM был получен результат, превышающий максимальное значение используемого типа данных, возникает ошибка .
Итак, если запрос не содержит предложения GROUP BY , то агрегатные функции , включенные в предложение SELECT , исполняются над всеми результирующими строками запроса. Если запрос содержит предложение GROUP BY , каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляет группу, и агрегатные функции выполняются для каждой группы отдельно.
Предложение HAVING
Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций . Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM , а из групп таких строк . Поэтому такая проверка не может содержаться в предложении WHERE .
Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.
Функция SQL SUM
Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:
SELECT SUM(ИМЯ_СТОЛБЦА) ...
После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).
Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):
Для получения суммы размеров всех заработных плат используем следующий запрос:
SELECT SUM(Salary) FROM Staff
Этот запрос вернёт значение 287664,63.
А теперь . В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.
Функция SQL MIN
Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.
Пример 3. База данных и таблица - те же, что и в примере 1.
Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Запрос вернёт значение 10505,90.
И вновь упражнение для самостоятельного решения . В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:
Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.
Функция SQL MAX
Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.
Пример 5.
Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Запрос вернёт значение 18352,80
Пришло время упражнения для самостоятельного решения .
Пример 6. Вновь работаем с двумя таблицами - Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц) .
Функция SQL AVG
Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.
Пример 7. База данных и таблица - те же, что и в предыдущих примерах.
Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос:
Результатом будет значение 6,33
Пример 8. Работаем с одной таблицей - Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.
Функция SQL COUNT
Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) ..., то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) ..., то результатом будет количество всех записей (строк) таблицы.
Пример 9. База данных и таблица - те же, что и в предыдущих примерах.
Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm - не NULL, вернёт следующий запрос:
SELECT COUNT(Comm) FROM Staff
Результатом будет значение 11.
Пример 10. База данных и таблица - те же, что и в предыдущих примерах.
Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT:
SELECT COUNT(*) FROM Staff
Результатом будет значение 17.
В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.
Пример 11. Работаем с одной таблицей - Staff. Вывести число сотрудников в отделе планирования (Plains).
Агрегатные функции вместе с SQL GROUP BY (группировкой)
Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных.
Пример 12. Есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts - о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money - о денежных суммах, вырученных за подачу объявлений.
| Category | Part | Units | Money |
| Транспорт | Автомашины | 110 | 17600 |
| Недвижимость | Квартиры | 89 | 18690 |
| Недвижимость | Дачи | 57 | 11970 |
| Транспорт | Мотоциклы | 131 | 20960 |
| Стройматериалы | Доски | 68 | 7140 |
| Электротехника | Телевизоры | 127 | 8255 |
| Электротехника | Холодильники | 137 | 8905 |
| Стройматериалы | Регипс | 112 | 11760 |
| Досуг | Книги | 96 | 6240 |
| Недвижимость | Дома | 47 | 9870 |
| Досуг | Музыка | 117 | 7605 |
| Досуг | Игры | 41 | 2665 |
Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу объявлений в каждой категории. Пишем следующий запрос.
Могут производить обобщенную групповую обработку значений полей. Это осуществляется с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. В SQL предусмотрены следующие агрегатные функции:
- COUNT – производит подсчет количества строк таблицы с не-NULL значениями указанного в качестве аргумента поля.
- SUM – вычисляет арифметическую сумму всех выбранных значений данного поля.
- AVG – производит усреднение всех выбранных значений данного поля.
- MAX – выводит наибольшее значение из всех выбранных значений данного поля.
- MIN
– выводит наименьшее значение из всех выбранных значений данного поля.
Использование агрегатных функций
Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением: они берут имена полей в качестве аргумента. Только числовые поля могут использоваться с SUM и AVG . С COUNT , MAX , и MIN могут использоваться и числовые, и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII. Это означает, что MIN будет выбирать первое, а MAX последнее значение в алфавитном порядке.
Чтобы найти общую сумму продаж в таблице продаж, мы должны написать следующий запрос:
SELECT SUM(SSum) FROM Sells
В результате получим:
Этот запрос подсчитал количество непустых значений в поле SNum таблицы Sells. Если же мы перепишем запрос в следующем виде:
SELECT COUNT(SDate) FROM Sells
То в результате получим:
COUNT OF SDate 4 Различные результаты запросов при вычислении, казалось бы, одного и того же, получены, потому что одно из значений поля SDate имеет пустое значение (NULL ). Будьте внимательны при использовании таких запросов.
