В разделе Агрегатные функции, часть 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