Базовый курс SQL. Функции() для обработки результатов запроса. SUM, COUNT, AVG

[к оглавлению]

Помимо обработки данных в конкретных столбцах, существуют функции, которые применяются ко всему результату запроса и выполняют какое-то обобщающее действие. Например, находят наибольшее или сумму всех значений столбца. Их следует использовать в случаях, когда нам не требуется знать конкретные данные, а интересен только результат.

Само собой, мы можем произвести нехитрые вычисления на стороне клиентского приложения, но, как мы помним, действия с данными намного эффективнее производит СУБД. Также это помогает избежать передачи большого объёма данных по сети и снижает риск утечки секретной информации.

Итоговые функции - функции, принимающие выборку из строк и возвращающие только одно обобщающее значение.

Вот их список:

• AVG() -- Среднее значение
• COUNT() -- Количество строк
• MAX() -- Максимальное значение
• MIN() -- Минимальное значение
• SUM() -- Сумма всех значений выборки

В отличии от функций, работающих с данными (из прошлого урока), они переносимы в большинство СУБД.

AVG()

Это функция, вычисляющая среднее значение полученных строк по определённому столбцу. Все значения суммируются и делятся на количество строк в выборке. Например, давайте найдём среднее значение баллов, заработанных студентами на первом туре фотовыставки:

SELECT AVG(first_round_points) as avg_points
FROM Participants
WHERE contest_id = 165;

Результат выведен в виде вычисляемого поля:

avg_points
---------------
84.8

Условие WHERE здесь может быть любое, которое соответствует логике вашего запроса, или же вообще может отсутствовать - тогда получим среднее значение баллов по всей таблице (в нашем случае - по всем проходившим когда-либо конкурсам).

Аргументом функции AVG() всегда является один столбец. Если бы мы захотели найти, например, среднее значение баллов одного студента по всем этапам конкурса (хранятся в разных столбцах), нам пришлось бы использовать несколько функций AVG().

NULL. Строки, содержащие NULL, не учитываются.

COUNT()

Эта функция определяет количество строк, соответствующих условию выборки, игнорируя их значение. Или общее число строк в таблице, если мы не добавим никакого ограничивающего условия WHERE.

Рассмотрим два примера.

Определение количества студентов в таблице Students:

SELECT COUNT(*) as students_count
FROM Students;

Результат:

students_count
---------------
10

NULL. Обычно функции не учитывают поля NULL, но при таком запросе СУБД вернёт полное количество строк таблицы.

А теперь рассчитаем количество студентов, e-mail которых нам известен. Для этого достаточно добавить поле, которое должно быть заполнено, в качестве аргумента функции:

SELECT COUNT(student_email) as students_count
FROM Students;

Здесь уже мы работаем с определённым одним столбцом, и строки с NULL не считаются:

students_count
---------------
8

MAX()

Эта функция производит поиск максимального значения столбца, указанного в качестве аргумента:

SELECT MAX(first_round_points) as max_points
FROM Participants
WHERE contest_id = 165;

С помощью данного запроса мы нашли наибольшее число баллов, заработанных одним из участников конкурса:

max_points
--------------
148

Обычно поиск максимального и минимального значений используются для числовых значений или дат, но многие СУБД смогут произвести данные операции и для текстовых полей. В этом случае, будет выбрана та строка, которая была бы последней в списке при сортировке данного столбца по возрастанию.

NULL. Значения NULL не учитываются.

MIN()

Всё то же самое, что и с MAX(), только наоборот: найдёт минимальное значение в указанном столбце:

SELECT MIN(first_round_points) as min_points
FROM Participants
WHERE contest_id = 165;

Результат:

min_points
--------------
13

При запросе над текстовым полем, будет выбрана первая строка в списке, отсортированном по возрастанию.

NULL. Значения NULL не учитываются.

SUM()

Эта функция возвращает сумму значений данного столбца всех строк выборки. Например, рассчитаем суммарный балл, заработанный студентами на экзамене по Технологиям программирования:

SELECT SUM(grade_value) AS total_exam_grade
FROM Grades
WHERE exam_id = 20119;

Получим:

total_exam_grade
--------------------
10

Функцию SUM() можно использовать даже для вычисляемых полей. Например, для расчёта суммы накопленных одним из студентов баллов преподаватель применяет увеличивающий коэффициент в случае, если студент присутствовал на всех занятиях:

SELECT SUM(grade_value * 1.2) AS total_student_grade
FROM Grades
WHERE student_id = 92571;

Получим:

total_student_grade
--------------------
14.4

Вычисления также можно производить и между двумя и более столбцами таблицы, а не с числом, как в примере выше.

NULL. Значения NULL не учитываются.

Итоговые вычисления с уникальными значениями. DISTINCT

В главе 5 мы узнали, что можно извлечь из таблицы не все данные, удовлетворяющие условию запроса, а только уникальные значения. Сделать это можно с помощью DISTINCT. Аналогично, если добавить это вспомогательное слово внутрь итоговой функции, она будет считать только уникальные строки, остальные - отбросит:

SELECT COUNT(DISTINCT student_id) AS students_count
FROM Participants
WHERE contest_id = 165 AND first_round_points > 70;

Получим количество студентов, набравших более 70 баллов в первом туре фотовыставки "Технологии будущего". DISTINCT позволяет исключить те случаи, когда один студент выставил одновременно несколько работ. и все они попали в выборку:

students_count
---------------------
3

Противоположностью DISTINCT является ключевое слово ALL, но оно обычно не указывается явно, т.к. во всех СУБД подразумевается по умолчанию. Если мы хотим посчитать все значения в таблице, соответствующие нашим условиям, в т.ч. повторяющиеся, можно добавить ALL перед аргументом функции. Однако. повторюсь, делать это не обязательно.

Помните, что DISTINCT можно использовать только если вы указываете конкретный столбец в качестве аргумента итоговой функции. Код SELECT SUM(DISTINCT *) выдаст ошибку, т.к. СУБД не сможет понять, по какому столбцу определять уникальность.

Что ещё кроме уникальности?

В некоторых СУБД могут быть наравне с DISTINCT и ALL использоваться такие ключевые слова как TOP и TOP PERCENT и др. Обратитесь к документации вашей СУБД, чтобы узнать, все возможные действия над подмножествами результатов запроса.

Комбинирование итоговых функций

Также можно произвести все вышеперечисленные вычисления (или несколько) прямо в одном запросе. К примеру:

SELECT COUNT(first_round_points) AS point_count,
    MIN(first_round_points) AS min_points,
    MAX(first_round_points) AS max_points,
    AVG(first_round_points) AS avg_points
FROM Participants
WHERE contest_id = 165;

Получим сводную таблицу:

point_count  |  min_points  |  max_points  |  avg_points
--------------------------------------------------
   5         |   13         |   148        |   84.4

Если вычисляемых столбцов несколько, обязательно используйте псевдонимы, иначе СУБД выдаст ошибку.

Используйте встроенные функции SQL, которые мы изучили в данном уроке, для статистических расчётов: они выполняются быстрее, чем аналогичные вычисления вручную.

Следующая страница. Группировка данных.
Оглавление.