Публикация не была найдена — Студопедия
Поделись
АГРЕГАТНЫЕ ФУНКЦИИ В СПИСКЕ ВОЗВРАЩАЕМЫХ СТОЛБЦОВ
ВЫЧИСЛЕНИЕ КОЛИЧЕСТВА ЗНАЧЕНИЙ В СТОЛБЦЕ (COUNT)
Агрегатная функция count () подсчитывает количество значений в столбце. При этом тип данных столбца может быть любым. Функция count () всегда возвращает целое число независимо от типа данных столбца. Ниже приведен ряд запросов, в которых используется эта функция:
Мысленно трудно представить запрос вроде «подсчитать, сколько стоимостей заказов» или «подсчитать, сколько номеров заказов»; гораздо проще представить запрос «подсчитать, сколько заказов». Поэтому в SQL была введена специальная агрегатная функция count (*), которая подсчитывает строки, а не значения данных. Ниже приведен предыдущий запрос, переписанный с использованием этой функции:
Если использовать count (*) в качестве функции подсчета строк, то запрос становится более удобочитаемым.
Назначение простого запроса с использованием агрегатной функции можно понять достаточно легко. Однако если в список возвращаемых столбцов входит несколько агрегатных функций или если аргументом агрегатной функции является сложное выражение, понять запрос становится значительно сложнее.
Агрегатные функции sum (), avg (), min (), мах () и count () в качестве аргумента принимают столбец значений и возвращают в качестве результата одно значение. А что происходит, когда в столбце содержится одно или несколько значений null? В стандарте ANSI/ISO сказано, что значения null агрегатными функциями игнорируются.
Следующий запрос показывает, как агрегатная функция count
() игнорирует все значения null, содержащиеся в столбце:В таблице salesreps содержится десять строк, поэтому функция count (*) возвращает число 10.
В столбце sales содержится десять значений, причем ни одно из них не равно null, поэтому функция count ( sale s) также возвращает число 10. А вот в столбце quota содержится одно значение null —- для служащего, принятого совсем недавно. Функция count (quota) игнорирует это значение и возвращает число 9. Именно из-за таких расхождений вместо функции count () для подсчета строк почти всегда используется функция count (*). Исключение составляют случаи, когда необходимо не учитывать строки, содержащие значения
Игнорирование значений null не оказывает влияния на результаты, возвращаемые агрегатными функциями min () и мах (). Однако оно может привести к проблемам при использовании функций sum () и avg (), что иллюстрирует следующий запрос:
Можно ожидать, что выражения
SUM(SALES) — SUM (QUOTA) SUM (SALES-QUOTA)
вернут одинаковые результаты, однако пример показывает, что так не происходит.
И снова причиной является строка со значением null в столбце quota. Выражение
SUM(SALES)
вычисляет сумму продаж для всех десяти служащих, а выражение
SUM (QUOTA)
вычисляет сумму только девяти значений и не учитывает значение null Следующее выражение вычисляет разницу между ними:
SUM(SALES) – SUM(QUOTA)
В то же время выражение
SUM(SALES-QUOTA)
принимает в качестве аргументов только девять значений, которые не равны null. В строке, где значение планового объема продаж равно null, оператор вычитания возвращает значение null, которое функция sum () игнорирует. Таким образом, из результатов этого выражения исключаются фактические продажи служащего, для которого еще не установлен план, вошедшие в результаты предыдущего выражения.
Какой же ответ является «правильным»? Оба! Первое выражение вычисляет именно то, что и означает, т.
е. «сумма по sales минус сумма по QUOTA.И второе выражение также вычисляет именно то, что оно означает, «сумма (sales — quota)». Однако при наличии значений
В стандарте ANSI/ISO определены следующие точные правила обработки значений null в агрегатных функциях:
• если какие-либо из значений, содержащихся в столбце, равны null, то при вычислении результата функций они исключаются;
• если все значения в столбце равны null, то функции sum(), avg min() и мах () возвращают значение null; функция count () возврат ноль;
• если в столбце нет значений (т.е. столбец пустой), то функции sum(), avg (), min () и мах () возвращают значение null; функция count возвращает ноль;
• функция count(*) подсчитывает количество строк и не зависит наличия или отсутствия в столбце значений null; если строк в таблице нет, эта функция возвращает ноль.
Курс SQL & Hibernate — Лекция: Агрегатные функции
6.1 Список агрегатных функций
Когда ты используешь группировку строк в SQL с помощью оператора GROUP BY, то можешь использовать в операторе SELECT функции, которые работают над сгруппированными данными. Такие функции еще называют агрегатными функциями.
Вот список самых популярных из них:
| # | Функция | Описание |
|---|---|---|
| 1 | COUNT() | Возвращает количество значений в группе |
| 2 | SUM() | Возвращает сумму значений в группе |
| 3 | MAX() | Возвращает максимальное значение группы |
| 4 | Возвращает минимальное значение группы | |
| 5 | AVG() | Возвращает среднее значение группы |
| 6 | BIT_AND() | Выполняет побитовое AND над всеми значениями группы |
| 7 | BIT_OR() | Выполняет побитовое OR над всеми значениями группы |
| 8 | BIT_XOR() | Выполняет побитовое XOR над всеми значениями группы |
| 9 | GROUP_CONCAT() | Склеивает все значения группы в одну строку |
Это не полный список агрегатных функций, но остальные очень специфические и я не думаю, что ты будешь ими пользоваться в ближайшие лет 5.
А теперь давай рассмотрим несколько примеров с нашими агрегатными функциями.
6.2 Анализируем зарплаты сотрудников
Давай вычислим некоторую статистику по нашим сотрудникам из таблицы employee.
Вопрос первый: сколько у нас сотрудников?
Если мы хотим узнать количество всех записей в таблице, то для этого можно использовать агрегатную функцию COUNT. Запрос будет выглядеть так:
SELECT COUNT(*) FROM employee
И в качестве ответа MySQL вернет число 6. У нас 6 сотрудников в отделе, включая кота. Все верно.
Вопрос второй: сколько мы платим в месяц всем сотрудникам?
Чтобы ответить на этот вопрос нам нужно просуммировать зарплаты всех сотрудников. Для этого воспользуемся агрегатной функцией
Запрос будет выглядеть так:
SELECT SUM(salary) FROM employee
Обрати внимание, что в этот раз мы обязаны указать значения какой колонки мы суммируем.
Мы указали колонку salary. Мы же не можем просто просуммировать все поля таблицы.
И в качестве ответа MySQL вернет число 461000. У нас 6 сотрудников в отделе, а зарплата 461 тысяча. Многовато.
И наконец третий вопрос: какая у нас максимальная и минимальная зарплаты в отделе? Ну и еще давайте среднюю зарплату посчитаем. Для этого нам понадобятся функции MIN, MAX и AVG.
Запрос будет немного посложнее в этот раз выглядеть так:
SELECT MIN(salary), AVG(salary), MAX(salary)
FROM employee
Результат этого запроса будет таким:
| MIN(salary) | AVG(salary) | MAX(salary) |
|---|---|---|
| 1000 | 76833.3333 | 200000 |
Минимальная зарплата в нашем отделе 1000 долларов – очень хорошо. Максимальная зарплата – 200 тысяч, но это директор.
А вот средняя зарплата слишком высокая, нужно как-то оптимизировать затраты.
Наймем еще одного кота и дело с концом 🙂
Агрегированные функции SQL: SUM(), COUNT(), AVG(), Functions
Агрегатная функция в SQL выполняет вычисление нескольких значений и возвращает одно значение. SQL предоставляет множество агрегатных функций, включая среднее, подсчет, сумму, минимум, максимум и т. д. Агрегатная функция игнорирует значения NULL при выполнении вычислений, за исключением функции подсчета.
Что такое агрегатная функция в SQL?
Агрегатная функция в SQL возвращает одно значение после вычисления нескольких значений столбца. Мы часто используем агрегатные функции с предложениями GROUP BY и HAVING оператора SELECT.
Различные типы агрегатных функций SQL:
- Счет()
- Сумма()
- Среднее()
- Мин()
- Макс()
СЧЕТ() Функция
Функция COUNT() возвращает количество строк в таблице базы данных.
Синтаксис:
СЧЁТ(*)
или
COUNT(выражение [ALL|DISTINCT] )
Пример:
Для демонстрации мы будем использовать таблицу «products» из примера базы данных.
Следующая инструкция SQL извлекает количество продуктов в таблице.
Это приведет к следующему результату.
Приведенная ниже команда отобразит те идентификаторы продуктов, цена за единицу которых больше 4.
Это покажет следующий результат.
Давайте посмотрим, как мы можем использовать функции GROUP BY и HAVING с функцией COUNT.
Рассмотрим следующий набор данных:
Приведенная ниже команда SQL выведет список клиентов в каждом городе.
Это даст следующие результаты:
Читайте также: Полное руководство по основам SQLСУММ() Функция
Функция SUM() возвращает общую сумму числового столбца.
Синтаксис:
СУММ()
или
СУММ(выражение [ALL|DISTINCT] )
Пример:
Следующий оператор SQL находит сумму полей «цена за единицу» в таблице «продукты»:
Это приведет к следующему результату.
Давайте посмотрим, как мы можем использовать функции GROUP BY и HAVING с функцией SUM.
Рассмотрим следующий набор данных:
Приведенная ниже команда SQL выведет список клиентов в каждом городе, сумма баллов которых превышает 3000.
Это даст следующий результат:
Функция AVG()
Функция AVG() вычисляет среднее значение набора значений.
Синтаксис:
АВГ()
или
AVG(выражение [ALL|DISTINCT] )
Пример:
Следующая команда SQL вычисляет среднее количество на складе.
Это приведет к следующему результату.
МИН() Функция
Агрегатная функция MIN() возвращает наименьшее значение (минимум) в наборе значений, отличных от NULL.
Синтаксис:
МИН()
или
МИН(выражение [ALL|DISTINCT] )
Пример:
Приведенный выше код даст нам минимальное количество на складе в таблице продуктов.
Читайте также: Как агрегировать данные с помощью группировки в SQL?
Функция МАКС()
Агрегатная функция MAX() возвращает наибольшее значение (максимум) в наборе значений, отличных от NULL.
Синтаксис:
АВГ()
или
AVG(выражение [ALL|DISTINCT] )
Пример:
Код, изображенный ниже, даст нам максимальное количество на складе в таблице продуктов.
Это приведет к следующему результату.
Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью программы последипломного образования в области бизнес-анализа. Зарегистрируйтесь сейчас!
Заключение
Агрегатная функция в SQL очень эффективна в базе данных. Он служит той же цели, что и их эквиваленты в MS Excel. В этой статье мы видели несколько примеров агрегатных функций.
Если вы хотите узнать больше об SQL, ознакомьтесь с нашим сертификационным курсом по SQL.
Этот сертификационный курс SQL поможет вам правильно освоить основы с помощью практического обучения, необходимого для работы с базами данных SQL и их использования в приложениях, которые вы используете или создаете. По завершении курса у вас будет готовое к работе понимание того, как правильно структурировать вашу базу данных, создавать безошибочные и эффективные операторы и предложения SQL, а также проектировать и управлять своими базами данных SQL для масштабируемого роста.
Если у вас есть какие-либо вопросы, задавайте их в разделе комментариев, и наши специалисты оперативно ответят на них.
СУБД SQL Агрегатная функция — javatpoint
следующий → ← предыдущая
1. ФУНКЦИЯ СЧЕТА
Синтаксис СЧИТАТЬ(*) или же COUNT(выражение [ALL|DISTINCT] ) Образец таблицы: PRODUCT_MAST
Пример: COUNT() ВЫБЕРИТЕ КОЛИЧЕСТВО(*) ОТ PRODUCT_MAST; Вывод: Пример: COUNT с WHERE ВЫБЕРИТЕ КОЛИЧЕСТВО(*) ОТ PRODUCT_MAST; ГДЕ СТАВКА>=20; Вывод: Пример: COUNT() с DISTINCT ВЫБЕРИТЕ СЧЕТ(ОТДЕЛЬНАЯ КОМПАНИЯ) ОТ PRODUCT_MAST; Вывод: Пример: COUNT() с GROUP BY ВЫБЕРИТЕ КОМПАНИЮ, КОЛИЧЕСТВО(*) ОТ PRODUCT_MAST ГРУППА ПО КОМПАНИИ; Вывод: Com1 5 Com2 3 Com3 2 Пример: COUNT() с HAVING ВЫБЕРИТЕ КОМПАНИЮ, КОЛИЧЕСТВО(*) ОТ PRODUCT_MAST ГРУППА ПО КОМПАНИИ СЧЕТ(*)>2; Вывод: Com1 5 Com2 3 2. Функция СУММФункция суммы используется для вычисления суммы всех выбранных столбцов. Работает только с числовыми полями. Синтаксис СУММ() или же SUM(выражение [ALL|DISTINCT] ) Пример: СУММ() ВЫБЕРИТЕ СУММУ (СТОИМОСТЬ) ОТ PRODUCT_MAST; Вывод: Пример: SUM() с WHERE ВЫБЕРИТЕ СУММУ (СТОИМОСТЬ) ОТ PRODUCT_MAST ГДЕ КОЛИЧЕСТВО>3; Вывод: Пример: SUM() с GROUP BY ВЫБЕРИТЕ СУММУ (СТОИМОСТЬ) ОТ PRODUCT_MAST ГДЕ КОЛИЧЕСТВО>3 ГРУППА ПО КОМПАНИИ; Вывод: Com1 150 Com2 170 Пример: SUM() с HAVING ВЫБЕРИТЕ КОМПАНИЮ, СУММА(СТОИМОСТЬ) ОТ PRODUCT_MAST ГРУППА ПО КОМПАНИИ СУММА(СТОИМОСТЬ)>=170; Вывод: Com1 335 Com3 170 3. Функция AVG Функция AVG используется для вычисления среднего значения числового типа. Синтаксис СРЕДНЕЕ() или же AVG(выражение [ALL|DISTINCT] ) Пример: ВЫБЕРИТЕ СРЕДНЮЮ (СТОИМОСТЬ) ОТ PRODUCT_MAST; Вывод: 4. Функция MAXФункцияMAX используется для нахождения максимального значения определенного столбца. Эта функция определяет наибольшее значение из всех выбранных значений столбца. Синтаксис МАКСИМУМ() или же MAX(выражение [ALL|DISTINCT] ) Пример: ВЫБЕРИТЕ МАКС(СТАВКА) ОТ PRODUCT_MAST; 5. МИН ФункцияФункцияMIN используется для поиска минимального значения определенного столбца. Эта функция определяет наименьшее значение из всех выбранных значений столбца. Синтаксис МИН() или же МИН(выражение [ALL|DISTINCT] ) Пример: ВЫБЕРИТЕ МИН. |
Он может работать как с числовыми, так и с нечисловыми типами данных.
Функция AVG возвращает среднее значение всех ненулевых значений.