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

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

(табл. 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. Результат выполнения запроса

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

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

SELECT GROUP_CONCAT(name),rating FROM Customers GROUP BY rating;

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

Если указано ключевое слово WITH ROLLUP, то обобщенные значения выводятся как для отдельных групп строк, так и для всех в совокупности строк.

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

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

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

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

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

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

SELECT customer_id, COUNT(amount) FROM Orders

WHERE amount>5000

GROUP BY customer_id;

позволяет подсчитать, сколько заказов на сумму более 5000 сделал каждый клиент (табл. 3.41). Сначала выбираются строки таблицы Orders (Заказы), для которых выполнено условие amount>5 0 0 0, далее эти строки группируются по значению столбца customer_id (клиент), и после этого вычисляется количество строк в каждой из групп. Таблица 3.41. Результат выполнения запроса

Для отбора групп строк служит параметр HAVING, о котором будет идти речь в следующем подразделе.

Параметр HAVING

Параметр HAVING позволяет задать условие отбора для групп строк. Он аналогичен параметру WHERE, но указывается после параметра GROUP BY и применяется к агрегированным строкам. В условии отбора параметра HAVING можно использовать значения столбцов, выводимых запросом, в том числе значения агрегатных функций.

Например, если требуется вывести общую сумму заказов для каждого клиента, кроме тех клиентов, для кого эта сумма меньше 20 000, выполните запрос

SELECT customer_id, SUM(amount) FROM Orders

GROUP BY customer_id

HAVING SUM(amount)> = 20000;

Условие SUM(amount) > = 20 000 позволяет отобрать только те группы строк, в которых общая сумма заказа равна или превышает 20 000 (табл. 3.42). Таблица 3.42. Результат выполнения запроса

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

3.3. Числовые операторы и функции

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

Арифметические операторы

В выражениях вы можете использовать следующие арифметические операторы:

•  а + b.

Оператор сложения. Возвращает сумму операндов a и b.

•  а – b.

Оператор вычитания. Возвращает разность операндов a и b.

При использовании с одним операндом меняет его знак, например – (3 + 2) = -5.

•  а * b.

Оператор умножения. Возвращает произведение операндов a и b.

•  а / b.

Оператор деления. Возвращает частное от деления a на b.

•  а DIV b.

Оператор деления c остатком, или целочисленного деления. Возвращает целую часть частного от деления a на b. Например,:

• 7 DIV 2 = 3,

• (-7) DIV 2 = -3,

• 7 DIV (-2) = - 3,

• (-7) DIV (-2) = 3.

• a % b.

Оператор вычисления остатка. Возвращает остаток от целочисленного деления a на b: величину a % b = a – b x (a DIV b). Например,:

• 7 % 2 = 1,

• (– 7) % 2 = -1,

• 7 % (-2) = 1,

• (-7) % (-2) = - 1.

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

Алгебраические функции

В выражениях вы можете использовать следующие алгебраические