Данная функция возвращает наименьший из своих аргументов (либо NULL, если один из аргументов равен NULL). Например, выражение LEAST(000 "Кускус",\'Петров\',\'Крылов\') возвращает значение "Крылов". Отметим, что в функции LEAST() можно указать только фиксированное количество аргументов. Например, невозможно получить первое в алфавитном порядке имя клиента с помощью запроса SELECT LEAST(name) FROM Customers; вместо этого необходимо использовать групповую функцию MIN() (о ней вы узнаете в разделе "Групповые функции").
• GREATEST( a 1, a 2,…, an ).
Данная функция возвращает наибольший из своих аргументов (либо NULL, если по крайней мере один из аргументов равен NULL). Например, выражение GREATEST (ООО "Кускус", Петров, Крылов) возвращает значение "Петров". Как и в функции LEAST(), в функции GREATEST() можно указать только фиксированное количество аргументов. Например, невозможно получить последнее в алфавитном порядке имя клиента с помощью запроса SELECT GREATEST(name) FROM Customers; вместо этого необходимо использовать групповую функцию MAX() (см. раздел "Групповые функции").
• INTERVAL( a , b 1, b 2,…, bn ), где b 1 < b 2 < … < bn.
Функция INTERVAL возвращает порядковый номер наибольшего из чисел b., не превосходящих числа a:
• bi ≤ a < bi +1 – функция возвращает номер i ;
• a < b 1 – функция возвращает значение 0:
• a > bn – функция возвращает значение n ;
• a равно NULL – функция возвращает значение -1.
Все аргументы этой функции являются целыми числами (если вы укажете аргумент с другим типом данных, он будет преобразован в целочисленное значение). Чтобы функция возвращала корректный результат, необходимо, чтобы значения bi были упорядочены, то есть выполнялось условие b1 < b2 <… < bn. Например, выражение INTERVAL(1500, 1000, 2000, 3000) возвращает значение 1.
• GOALESCE ( a 1, a 2,…, an )
Данная функция возвращает первый из аргументов, который отличен от NULL (а если все аргументы равны NULL, то возвращает значение NULL). Например, выражение COALESCE(NULL,1/0,\'TeKCT\') возвращает значение "Текст", поскольку это первый аргумент, отличный от NULL (при делении на 0 результатом является NULL).
• IF(a,b,c).
Данная функция проверяет, является ли истинным логическое значение или выражение a. Если a истинно (то есть является числом, датой или временем, отличным от нулевых), то функция возвращает значение b, а если a ложно или равно NULL, функция возвращает значение c. Например, если требуется удвоить те рейтинги клиентов, которые превышают 1000, это можно сделать с помощью команды
UPDATE Customers SET rating = IF(rating>1000,rating*2,rating);
• IFNULL(a,b). Данная функция возвращает значение a, если это значение отлично от NULL, и значение b, если a равно NULL. Например, если требуется всем клиентам, чей рейтинг не указан (равен NULL), присвоить рейтинг 500, это можно сделать с помощью команды
UPDATE Customers SET rating = IFNULL(rating,500);
• NULLIF(a,b). Данная функция возвращает значение NULL, если a = b, и значение a в противном случае. Например, если требуется выполнить операцию, обратную операции из предыдущего пункта, то есть всем клиентам с рейтингом 500 присвоить неопределенный рейтинг, это можно сделать с помощью команды
UPDATE Customers SET rating = NULLIF(rating,500);
• CASE x WHEN a1 THEN b1.
[WHEN a2 THEN b2]
…
[WHEN an THEN bn]
[ELSE b0]
END
или
CASE WHEN x1 THEN b1
[WHEN x2 THEN b2]
…
[WHEN xn THEN bn]
[ELSE b0]
END
Оператор CASE обеспечивает последовательную проверку списка условий и возвращает значение в зависимости от того, какое из условий выполнено. В первом варианте значение выражения х сравнивается со значениями a1, a2,…, an :
• если х = ai , то оператор возвращает значение bi;
• если значение выражения х не совпало ни с одним из a, то оператор возвращает значение b0 , заданное с помощью параметра ELSE;
• если значение выражения х не совпало ни с одним из ai , а параметр ELSE не задан, то оператор возвращает значение NULL.
Во втором варианте последовательно проверяется истинность логических выражений хi:
• если хi истинно, то оператор возвращает значение bi;
• если ни одно из выражений хi не является истинным, то оператор возвращает значение b0 , заданное с помощью параметра ELSE;
• если ни одно из выражений хi не является истинным, а параметр ELSE не задан, то оператор возвращает значение NULL.
Например, запрос
SELECT date,customer_id,amount,
CASE WHEN amount< = 5000 THEN \'Малый\'
WHEN amount BETWEEN 5000 AND 15000 THEN \'Средний\'
WHEN amount>15000 THEN \'Крупный\'
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;