Урок 3.3: Основные математические функции в SQL

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

Важно: числовые данные в SQL бывают разных типов (INTEGER, REAL/FLOAT, DECIMAL/NUMERIC). Одна и та же формула может давать разный результат в зависимости от типа данных (например, из-за целочисленного деления, округления и точности хранения). Если не учитывать тип, итог вычислений может отличаться от ожидаемого.

Общие математические функции

ABS() - Возвращает абсолютное значение числа.

Синтаксис:

ABS(number)

Пример:

SELECT ABS(amount - 5) AS abs_difference
FROM payment
LIMIT 3;

Результат: Возвращает абсолютную разницу между значением amount и 5.

CEIL() / CEILING() - Округляет число в большую сторону (до ближайшего целого).

Синтаксис:

CEIL(number)
CEILING(number)

Пример:

SELECT CEIL(amount) AS rounded_up
FROM payment
LIMIT 3;

Результат: Округляет значение amount вверх до ближайшего целого.

FLOOR() - Округляет число в меньшую сторону (до ближайшего целого).

Синтаксис:

FLOOR(number)

Пример:

SELECT FLOOR(amount) AS rounded_down
FROM payment
LIMIT 3;

Результат: Округляет значение amount вниз до ближайшего целого.

ROUND() - Округляет число до заданного количества знаков после запятой.

Синтаксис:

ROUND(number, decimals)

Пример:

SELECT ROUND(amount, 1) AS rounded_amount
FROM payment
LIMIT 3;

Результат: Округляет значение amount до одного знака после запятой.

POWER() / POW() - Возводит число в степень.

Синтаксис:

POWER(number, exponent)
POW(number, exponent)

Пример:

SELECT POWER(amount, 2) AS squared_amount
FROM payment
LIMIT 3;

Результат: Возводит значение amount в квадрат.

SQRT() - Возвращает квадратный корень числа.

Синтаксис:

SQRT(number)

Пример:

SELECT SQRT(amount) AS sqrt_amount
FROM payment
LIMIT 3;

Результат: Возвращает квадратный корень из значения amount.

MOD() - Возвращает остаток от деления одного числа на другое.

Синтаксис:

MOD(dividend, divisor)

Пример:

SELECT MOD(payment_id, 5) AS mod_result
FROM payment
LIMIT 3;

Результат: Возвращает остаток от деления payment_id на 5.

SIGN() - Возвращает знак числа (-1, 0 или 1).

Синтаксис:

SIGN(number)

Пример:

SELECT SIGN(amount - 5) AS sign_value
FROM payment
LIMIT 3;

Результат: Возвращает -1, если разница отрицательная, 0 - если равна нулю, 1 - если положительная.

GREATEST() - Возвращает наибольшее из переданных значений (MySQL, PostgreSQL).

Синтаксис:

GREATEST(value1, value2, ...)

Пример:

SELECT GREATEST(amount, 5) AS max_value
FROM payment
LIMIT 3;

Результат: Возвращает большее из двух значений: amount или 5.

Важно (NULL): поведение GREATEST() зависит от СУБД.

• В MySQL/MariaDB, если хотя бы один аргумент равен NULL, результатом обычно будет NULL.
• В PostgreSQL NULL-аргументы игнорируются, и NULL возвращается только если все аргументы равны NULL.

LEAST() - Возвращает наименьшее из переданных значений (MySQL, PostgreSQL).

Синтаксис:

LEAST(value1, value2, ...)

Пример:

SELECT LEAST(amount, 5) AS min_value
FROM payment
LIMIT 3;

Результат: Возвращает меньшее из двух значений: amount или 5.

Важно (NULL): для LEAST() действуют те же различия между СУБД, что и для GREATEST().

Чтобы сделать поведение предсказуемым в кросс-СУБД запросах, часто используют COALESCE(), например:

SELECT GREATEST(COALESCE(value1, 0), COALESCE(value2, 0));

RAND() - Возвращает случайное число от 0 до 1.

Синтаксис:

RAND()

Пример:

SELECT RAND() AS random_value
FROM payment
LIMIT 3;

Результат: Возвращает случайное число от 0 до 1.

Важно: не стоит полагаться на то, что RAND() обязательно будет пересчитываться для каждой строки в любом контексте. В зависимости от СУБД, плана выполнения, использования CTE/подзапросов и других факторов одно и то же случайное значение может быть повторно использовано для нескольких строк.

Если вам принципиально нужны разные значения по строкам, проверяйте поведение на вашей СУБД и в конкретной форме запроса.

Практическое применение

1. Округление суммы платежа: Используйте ROUND(amount, 0) для округления суммы до целого числа.

2. Поиск платежей с остатком от деления: Используйте MOD(payment_id, 2) для поиска чётных и нечётных платежей.

3. Вычисление квадратного корня: Используйте SQRT(amount) для анализа распределения платежей.

4. Сравнение значений: Используйте GREATEST() и LEAST() для выбора максимального или минимального значения из нескольких столбцов.

5. Контроль типа данных: Если важна точность результата, явно приводите значения к нужному типу (например, CAST(value AS DECIMAL(10,2))), чтобы избежать неожиданностей из-за целочисленных вычислений и округления.

Основные выводы из этого урока

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