– среднее арифметическое значений столбца, то значение выборочной дисперсии равно
Например, запрос
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. Результат выполнения запроса
Таким же образом можно подсчитать количество заказов каждого клиента, максимальную, минимальную и среднюю сумму заказа и др.
Другой пример – запрос, возвращающий имена клиентов с одинаковым значением рейтинга:
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.
В следующем подразделе мы рассмотрим алгебраические функции.
Алгебраические функции
В выражениях вы можете использовать следующие алгебраические функции:
• ABS(x).
Возвращает абсолютную величину (модуль) числа x. Например, ABS(10) = ABS(-10) = 10.
• CEIL(x), CEILING(x).
Функция округления в большую сторону. Возвращает наименьшее из целых чисел, которые больше или равны x. Например,:
• CEIL(12345.6789) = 12346,
• CEIL(-12345.6789) = -12345.
• CRC32('Символьное значение').
Функция вычисляет контрольную сумму для последовательности символов с помощью алгоритма CRC32. Подробнее об алгоритмах CRC вы можете прочитать здесь: http://ru.wikipedia.org/wiki/CRC32. Например, CRC32('Beк живи – век учись') = 4171076480.
• EXP(x).
Экспонента. Возвращает ex (экспоненту числа x).