В разделе Агрегатные функции, часть 1: COUNT () - с примерами в MySQL мы рассмотрели примеры агрегатной функции COUNT()
и исследовали различные варианты использования ее приложения.
По мере того, как я продолжаю эту серию статей по агрегатным функциям, эта запись в блоге будет посмотрите еще 2 функции: SUM()
и AVG()
. Оба помогают в вычислении числовых значений.
Примечание: все данные, имена или наименования, найденные в базе данных, представленной в этом посте, строго используются для практики, обучения, обучения и тестирования. Он ни в коем случае не отображает фактические данные, принадлежащие или используемые какой-либо стороной или организацией.
ОС и БД используются:
- Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
- MySQL 5.7.21
СУММ () и СРЕДНЕЕ ()
SUM(some_expression)
вычисляет итог для выражения, а AVG(some_expression)
возвращает среднее значение.
Ключевое слово DISTINCT
применимо к обеим функциям, возвращая только те уникальные значения для агрегированного столбца или выражения.
Если совпадающие строки не найдены, возвращается NULL
. Обе функции имеют эту характеристику.
Рабочие примеры.
Целевая таблица - это та же таблица-макет сотрудников отдела кадров, которую мы использовали в предыдущем сообщении в блоге:
Столбец SALARY
- отличный выбор для тестирования этих функций. Поскольку это числовой decimal(8,2)
тип данных, мы можем использовать обе функции для значений этого столбца и лучше понять, что каждая из них выполняет.
Если вы хотите определить общую сумму всех значений для этого столбца, какую функцию вы бы использовали?
Вы догадались, SUM()
.
mysql> SELECT SUM(SALARY) -> FROM employees; + — — — — — — -+ | SUM(SALARY) | + — — — — — — -+ | 691400.00 | + — — — — — — -+ 1 row in set (0.00 sec)
А как насчет среднего значения для столбца SALARY
?
И снова ты прав (голосом Йоды).
Вот где AVG()
полезно:
mysql> SELECT AVG(SALARY) -> FROM employees; + — — — — — — -+ | AVG(SALARY) | + — — — — — — -+ | 6461.682243 | + — — — — — — -+ 1 row in set (0.00 sec)
Как видно из приведенных выше примеров, использовать эти функции относительно просто.
Вы можете дополнительно ограничить возвращаемые наборы результатов путем фильтрации с помощью предложения WHERE
или HAVING
в зависимости от ситуации.
Я вкратце вернусь к некоторым подводным камням, затронутым в предыдущем сообщении в блоге, относительно фильтрации с помощью агрегатной функции.
* Напоминание:
- Агрегатные функции нельзя использовать при фильтрации с предложением
WHERE
. - Вместо этого используйте
HAVING
для фильтрации наборов результатов с помощью агрегатных функций.
Теперь давайте проверим эти функции на нескольких примерах.
Чтобы определить среднее значение SALARY
для всех строк, объединенных для JOB_ID
роли 'IT_PROG'
:
mysql> SELECT AVG(SALARY) -> FROM employees -> WHERE JOB_ID = ‘IT_PROG’; + — — — — — — -+ | AVG(SALARY) | + — — — — — — -+ | 5760.000000 | + — — — — — — -+ 1 row in set (0.00 sec)
Какая SALARY
сумма в сумме для всех сотрудников с 'IT_PROG'
должностью? SUM()
- отличный кандидат, чтобы помочь ответить на этот тип вопросов.
mysql> SELECT SUM(SALARY) -> FROM employees -> WHERE JOB_ID = ‘IT_PROG’; + — — — — — — -+ | SUM(SALARY) | + — — — — — — -+ | 28800.00 | + — — — — — — -+ 1 row in set (0.01 sec)
Как насчет использования агрегата в качестве фильтра вместо JOB_ID
?
Мы можем определить, в каком отделе (ах) общее количество на SALARY
больше, чем 30000
, путем фильтрации с помощью предложения HAVING
:
mysql> SELECT DEPARTMENT_ID -> FROM employees -> GROUP BY DEPARTMENT_ID -> HAVING SUM(SALARY) > 30000; + — — — — — — — -+ | DEPARTMENT_ID | + — — — — — — — -+ | 50 | | 80 | | 90 | | 100 | + — — — — — — — -+ 4 rows in set (0.00 sec)
Помните, что мы должны использовать GROUP BY
, поскольку мы называем неагрегированный столбец –DEPARTMENT_ID
– в списке SELECT
.
Давайте применим DISTINCT
, чтобы узнать, как это влияет на SUM()
и AVG()
.
Используя COUNT()
, мы можем определить количество SALARY
строк столбца в дополнение к этим уникальным значениям, используя DISTINCT
со следующими SELECT
запросами:
mysql> SELECT COUNT(SALARY) -> FROM employees; + — — — — — — — -+ | COUNT(SALARY) | + — — — — — — — -+ | 107 | + — — — — — — — -+ 1 row in set (0.00 sec) mysql> SELECT COUNT(DISTINCT SALARY) -> FROM employees; + — — — — — — — — — — — — + | COUNT(DISTINCT SALARY) | + — — — — — — — — — — — — + | 57 | + — — — — — — — — — — — — + 1 row in set (0.00 sec)
В заключение, ниже приведен пример каждой из агрегатных функций, на которые мы нацелились в этом сообщении в блоге, используя DISTINCT
:
mysql> SELECT SUM(DISTINCT SALARY) -> FROM employees; + — — — — — — — — — — — + | SUM(DISTINCT SALARY) | + — — — — — — — — — — — + | 397900.00 | + — — — — — — — — — — — + 1 row in set (0.00 sec) mysql> SELECT AVG(DISTINCT SALARY) -> FROM employees; + — — — — — — — — — — — + | AVG(DISTINCT SALARY) | + — — — — — — — — — — — + | 6980.701754 | + — — — — — — — — — — — + 1 row in set (0.00 sec)
Прежде чем закрыть это сообщение в блоге, давайте укрепим наше понимание функций, предоставляемых статьями HAVING
и WHERE
. Эти примеры призваны провести различие между ними и устранить любую потенциальную путаницу.
Предложение WHERE
не выполняет этот тип фильтрации:
mysql> SELECT EMPLOYEE_ID -> FROM employees -> WHERE AVG(SALARY) > 25000; ERROR 1111 (HY000): Invalid use of group function
Точно так же HAVING
не работает таким образом:
mysql> SELECT EMPLOYEE_ID -> FROM employees -> HAVING JOB_ID = ‘IT_PROG’; ERROR 1054 (42S22): Unknown column ‘JOB_ID’ in ‘having clause’
Скоро
До сих пор в этой серии я посетил: агрегатные функции _42 _, _ 43_ и AVG()
. Далее я исследую две другие мощные функции из совокупного семейства: MAX()
и MIN()
. Надеюсь увидеть тебя там.
Посетите официальное Интерактивное руководство по MySQL 5.7 для получения дополнительной информации.
Призыв к действию!
Спасибо, что нашли время прочитать этот пост. Я искренне надеюсь, что вы открыли для себя что-то интересное и поучительное. Пожалуйста, поделитесь своими выводами здесь с кем-нибудь, кого вы знаете, кто тоже получит от этого такую же ценность.
Посетите страницу Портфолио-проекты, чтобы увидеть сообщения в блоге / технические статьи, которые я написал для клиентов.
Я уже упоминал, как я люблю чашку кофе?!?!
Чтобы получать уведомления о последних сообщениях от Digital Owl's Prose по электронной почте, подпишитесь, нажав кнопку Нажмите, чтобы подписаться! На боковой панели!
Обязательно посетите страницу Best Of за сборник моих лучших сообщений в блоге, пока вы там!
Джош Отвелл хочет учиться и расти как разработчик SQL и блогер. Другие любимые занятия находят его, уткнувшись носом в хорошую книгу, статью или командную строку Linux. Среди них он разделяет любовь к настольным ролевым играм, чтению фэнтезийных романов и проведению времени с женой и двумя дочерьми.
Отказ от ответственности: примеры, представленные в этом посте, представляют собой гипотетические идеи о том, как достичь аналогичных результатов. Это не самое лучшее решение. Ваши конкретные цели и потребности могут отличаться. Используйте те методы, которые лучше всего подходят для ваших нужд и целей. Мнения мои собственные.
Первоначально опубликовано на joshuaotwell.com 11 апреля 2018 г.
✉️ Подпишитесь на рассылку еженедельно Email Blast от CodeBurst 🐦 Подпишитесь на CodeBurst на Twitter , просмотрите 🗺️ Дорожная карта веб-разработчиков на 2018 год и 🕸️ Изучите Full Stack Web Development