Читать интересную книгу MySQL 5.0. Библиотека программиста - Виктор Гольцман

END

FROM Orders

ORDER BY customer_id,amount DESC;

выводит классификацию заказов в зависимости от их стоимости (табл. 3.22). Таблица 3.22. Результат выполнения запроса

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

3.2. Групповые функции

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

Перечень групповых функций

Для вычисления обобщающего значения столбца таблицы предназначены следующие функции.

SUM()

Данная функция возвращает сумму значений в столбце. Неопределенные значения при этом не учитываются. Если запросом не найдено ни одной строки или все значения в столбце равны NULL, то функция возвращает значение NULL.

Например, запрос

SELECT SUM(rating) FROM Customers;

возвращает сумму рейтингов клиентов – величину, полученную при сложении значений 1000 + 1500 + 1000 (табл. 3.23). Таблица 3.23. Результат выполнения запроса

Исключить повторяющиеся значения при подсчете суммы можно с помощью параметра DISTINCT. Если указан этот параметр, то каждое значение столбца будет учтено в сумме только один раз, даже если в столбце оно встречается несколько раз.

Например, запрос

SELECT SUM(DISTINCT rating) FROM Customers;

подсчитывает сумму только несовпадающих рейтингов (табл. 3.24). Таблица 3.24. Результат выполнения запроса

Число, возвращаемое этим запросом, является суммой значений 1000 и 1500; еще одно значение 1000, имеющееся в столбце rating, запросом игнорируется.

Если в запросе вы укажете какое-либо условие отбора, то суммирование произойдет только по тем строкам, которые удовлетворяют условию отбора.

Например, запрос

SELECT SUM(amount) FROM Orders WHERE customer_id = 533;

вычисляет общую сумму заказов клиента с идентификатором 533 (табл. 3.25). Таблица 3.25. Результат выполнения запроса

Далее мы рассмотрим функцию вычисления среднего значения.

AVG()

Данная функция возвращает среднее арифметическое значений в столбце (сумму значений, деленную на количество значений). Неопределенные значения при этом не учитываются. Если в запросе вы укажете какое-либо условие отбора, то суммирование произойдет только по тем строкам, которые удовлетворяют условию отбора. Если запросом не найдено ни одной строки или все значения в столбце равны NULL, то функция возвращает значение NULL.

Например, запрос

SELECT AVG(rating) FROM Customers;

возвращает средний рейтинг клиентов – величину (1000 + 1500 + 1000) /3 (табл. 3.26). Таблица 3.26. Результат выполнения запроса

Исключить повторяющиеся значения при подсчете среднего можно с помощью параметра DISTINCT.

Например, запрос

SELECT AVG(DISTINCT rating) FROM Customers;

подсчитывает среднее только несовпадающих рейтингов – величину (1000 + + 1500) / 2; еще одно значение 1000, имеющееся в столбце rating, запросом игнорируется (табл. 3.27). Таблица 3.27. Результат выполнения запроса

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

Например, запрос

SELECT * FROM Customers WHERE rating > (SELECT AVG(rating) FROM Customers);

выводит информацию о клиентах, чей рейтинг выше среднего (см. результат запроса в табл. 3.12). Вложенный запрос возвращает средний рейтинг клиента (см. табл. 3.26), а внешний – отбирает строки таблицы Customers, в которых значение столбца rating больше значения, возвращенного подзапросом. Отметим, что в данном случае вложенный запрос возвращает единственное значение, поэтому с оператором «больше» нет необходимости использовать ключевое слово ANY или ALL (о них рассказывалось в подразделе «Операторы сравнения с результатами вложенного запроса»).

Теперь мы рассмотрим функцию нахождения максимального значения столбца.

MAX()

Данная функция возвращает максимальное значение в столбце. Если в запросе вы укажете какое-либо условие отбора, то максимальное значение выбирается из строк, удовлетворяющих условию отбора. Если запросом не найдено ни одной строки или все значения в столбце равны NULL, то функция возвращает значение NULL.

Например, запрос

SELECT MAX(rating) FROM Customers;

возвращает наибольший из рейтингов клиентов – 1500 (табл. 3.28). Таблица 3.28. Результат выполнения запроса

Функцию MAX() можно использовать для поиска строк, в которых достигается максимальное значение столбца.

Например, запрос

SELECT * FROM Customers WHERE rating = (SELECT MAX(rating) FROM Customers);

выводит информацию о клиентах, чей рейтинг равен максимальному (см. результат запроса в табл. 3.12).

Далее мы рассмотрим функцию нахождения минимального значения столбца.

MIN()

Данная функция возвращает минимальное значение в столбце. Если в запросе вы укажете какое-либо условие отбора, то минимальное значение выбирается из строк, удовлетворяющих условию отбора. Если запросом не найдено ни одной строки или все значения в столбце равны NULL, то функция возвращает значение NULL.

Например, запрос

SELECT MIN(rating) FROM Customers;

возвращает наименьший из рейтингов клиентов – 1000 (табл. 3.29). Таблица 3.29. Результат выполнения запроса

Функцию MIN() можно использовать для поиска строк, в которых достигается минимальное значение столбца.

Например, запрос

SELECT * FROM Customers WHERE rating = (SELECT MIN(rating) FROM Customers);

выводит информацию о клиентах, чей рейтинг равен минимальному (см. результат запроса в табл. 3.11).

Далее мы рассмотрим функцию подсчета количества значений.

COUNT()

Данная функция возвращает количество отличных от NULL значений, содержащихся в столбце. Если в запросе вы укажете какое-либо условие отбора, то в подсчете участвуют только строки, удовлетворяющие условию отбора. Если не найдено ни одного отличного от NULL значения, то функция возвращает значение 0.

Например, запрос

SELECT COUNT(rating) FROM Customers;

возвращает количество отличных от NULL значений в столбце rating таблицы Customers (табл. 3.30). Таблица 3.30. Результат выполнения запроса

Параметр DISTINCT позволяет подсчитать количество различных (уникальных) значений в столбце (при этом неопределенные значения также игнорируются).

Например, запрос

SELECT COUNT(DISTINCT rating) FROM Customers;

подсчитывает количество различных значений рейтинга в таблице Customers (табл. 3.31). В таблице есть две строки с одинаковым рейтингом – 1000, поэтому результат подсчета будет меньше, чем в предыдущем запросе. Таблица 3.31. Результат выполнения запроса

Если в качестве аргумента функции COUNT() указать не имя столбца, а звездочку, то функция возвращает общее число строк, удовлетворяющих условию отбора, включая строки, содержащие неопределенные значения. Так, если столбец rating содержит неопределенные значения, то значение, выводимое запросом

SELECT COUNT(*) FROM Customers;

будет больше, чем значение, выводимое запросом

SELECT COUNT(rating) FROM Customers;

(разность этих значений совпадает с количеством строк, в которых значение в столбце rating равно NULL).

Функцию COUNT() можно использовать для отбора тех строк родительской таблицы, с которыми связано заданное количество строк дочерней таблицы.

Например, запрос

SELECT * FROM Customers

WHERE 2 < = (SELECT COUNT(*) FROM Orders

WHERE Customers.id = customer_id);

выводит список клиентов, сделавших не менее двух заказов (результат запроса см. в табл. 3.14). Для каждого клиента вложенный запрос выдает количество заказов этого клиента, и если это количество не меньше двух, то текущая запись о клиенте включается в результат, выводимый внешним запросом.

Рассмотрим функции вычисления среднеквадратичного отклонения.

Функции VAR_POP(), VARIANCE(), VAR_SAMP(), STDDEV_POP(), STD(), STDDEV() и STDDEV_SAMP()

Функция VAR_POP() вычисляет дисперсию значений столбца. Дисперсия характеризует колебание значений от среднего. Если a1, a2, …, an – значения столбца,

– среднее арифметическое значений столбца, то дисперсия равна

Например, запрос

SELECT VAR_POP(rating) FROM Customers;

возвращает величину дисперсии рейтингов клиентов:

(табл. 3.32).

Таблица 3.32. Результат выполнения запроса

Функция VARIANCE() является синонимом функции VAR_POP().

Функция VAR_SAMP() возвращает величину выборочной, или несмещенной, дисперсии (в математической статистике выборочная дисперсия является оценкой дисперсии всей изучаемой совокупности значений, при этом значения, по которым вычисляется несмещенная дисперсия, рассматриваются как выборка из изучаемой совокупности). Если a1, a2, … an – значения столбца,