Урок 3.3 · Время чтения: ~8 мин

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

Основные математические функции в 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.

PI() - Возвращает математическую константу π.

Синтаксис:

PI()

Пример:

SELECT PI() AS pi_value;

Результат: Возвращает значение π (примерно 3.141592653589793).

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

Синтаксис:

MOD(dividend, divisor)

Пример:

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

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

Пример использования функции в WHERE (поиск четных значений):

SELECT payment_id, amount
FROM payment
WHERE MOD(payment_id, 2) = 0
LIMIT 10;

Результат: Возвращает только записи с четными payment_id.

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) в WHERE (например, MOD(payment_id, 2) = 0) для поиска чётных платежей.

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

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

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

Часто задаваемые вопросы

Чем отличаются ROUND(), CEIL() и FLOOR()?

ROUND() округляет до ближайшего значения (или до указанного количества знаков после запятой), CEIL() всегда округляет вверх, а FLOOR() всегда вниз.

Почему результат вычислений может отличаться для похожих формул?

Причина обычно в типах данных. Целочисленные и дробные типы по-разному обрабатывают деление, округление и точность хранения.

Когда лучше использовать MOD()?

MOD() полезен, когда нужно проверить периодичность или четность значений, например разделить записи на группы или фильтровать четные/нечетные идентификаторы.

Зачем учитывать особенности СУБД для GREATEST() и LEAST()?

Потому что поведение с NULL может отличаться между MySQL/MariaDB и PostgreSQL. Для предсказуемого результата часто используют COALESCE().

Вопросы для собеседования

Как выбрать функцию округления для бизнес-задачи?

Сначала определяют правило: математическое округление (ROUND), всегда вверх (CEIL) или всегда вниз (FLOOR). Затем проверяют, как это влияет на итоговые расчеты в отчетах.

Какие риски есть при вычислениях без явного приведения типов?

Можно получить неожиданные результаты из-за целочисленного деления или потери точности. Поэтому в критичных расчетах лучше явно использовать CAST(... AS DECIMAL(...)).

Что делает SIGN() и где это может пригодиться?

SIGN() возвращает -1, 0 или 1 в зависимости от знака числа. Это удобно для быстрой классификации отклонений как отрицательных, нулевых или положительных.

Почему RAND() стоит использовать осторожно в аналитических запросах?

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

Ключевые выводы этого урока:

• Математические функции SQL помогают выполнять вычисления и преобразования прямо в запросе.
• ROUND, CEIL и FLOOR решают разные задачи округления, и выбор зависит от бизнес-правила.
• MOD, POWER и SQRT полезны для прикладной аналитики и проверки данных.
• Типы данных существенно влияют на точность и итог вычислений.
• Для кросс-СУБД запросов важно учитывать различия поведения функций с NULL.

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