MySQL 5.0. Библиотека программиста

Гольцман Виктор

подсчитывает количество различных значений рейтинга в таблице 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 – значения столбца,

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

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

SELECT VAR_SAMP(rating) FROM Customers;

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

(табл. 3.33).

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

Функция STDDEV_POP вычисляет среднеквадратичное отклонение значений столбца, которое является квадратным корнем из дисперсии.

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

SELECT STDDEV_POP(rating) FROM Customers;

возвращает величину

(табл. 3.34).

Таблица 3.34. Результат

выполнения запроса

Функции STD и STDDEV являются синонимами функции STDDEV_POP.

Функция STDDEV_SAMP вычисляет квадратный корень из выборочной дисперсии.

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

SELECT STDDEV_SAMP(rating) FROM Customers;

возвращает величину

(табл. 3.35).

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

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

Далее мы рассмотрим функцию объединения строк.

GROUP_CONCAT

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

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

SELECT GROUP_CONCAT(name) FROM Customers;

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

При использовании функции GROUP_CONCAT вы также можете указать дополнительные параметры:

• DISTINCT – исключает при объединении повторяющиеся значения;

• ORDER BY – упорядочивает объединяемые значения;

• SEPARATOR – задает разделитель значений.

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

SELECT GROUP_CONCAT(DISTINCT name ORDER BY name ASC SEPARATOR \\') FROM Customers;

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

Итак, вы изучили все основные групповые функции (за рамками нашего рассмотрения остались функции BIT_AND – побитовое «и», BIT_OR – побитовое «или» и BIT_XOR – побитовое «исключающее или»). В следующем подразделе мы рассмотрим ключевое слово GROUP BY, с помощью которого можно вычислять групповые функции одновременно для нескольких групп строк.

Параметр GROUP BY

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

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

SELECT customer_id, SUM(amount) FROM Orders GROUP BY customer_id;

возвращает общую сумму заказов отдельно для каждого клиента (табл. 3.38). В этом запросе заказы сгруппированы по значению столбца customer_id (клиент), поэтому каждая группа состоит из заказов одного клиента, а функция SUM(amount) вычисляет сумму заказов в каждой из групп. Таблица 3.38. Результат выполнения запроса

Таким же образом можно подсчитать количество заказов каждого клиента, максимальную, минимальную и среднюю сумму заказа и др.

Другой пример – запрос, возвращающий имена клиентов с одинаковым значением рейтинга: