Числовые функции
Функции для работы с числами
Теория
Достаточно часто числовые значения приходится каким-то образом подготавливать под результат определенной задачи: где-то округлить цену, где-то отбросить дробную часть, где-то посчитать остаток от деления, где-то понять, сколько полных упаковок получится собрать, а где-то получить случайное число для тестового сценария.
Для этого в PostgreSQL существует ряд функций для работы с числовыми значениями. Вы передаёте функции число или числовое выражение, а она возвращает уже обработанный результат.
Наиболее частой операцией при работе с числами является округление. На эту операцию сделаем акцент.
Возможности функции округления ROUND():
• если передать внутрь функции один аргумент, например ROUND(value), функция по умолчанию округлит число до целого;
• при передаче второго аргумента, такого как ROUND(value::numeric, 2), можно регулировать степень точности округления в дробной части (зачем внутри функции мы переводим значение в тип numeric разберем дальше);
• если второй аргумент отрицательный, например ROUND(value::numeric, -2), функция начнёт округлять уже не дробную часть, а десятки, сотни и тысячи;
• для округления значений до нескольких знаков после запятой в PostgreSQL необходимо использовать тип данных numeric. При попытке выполнить округление с типами данных, такими как double precision, система выдаст ошибку. Вспомним, что типы real, float и double precision не гарантируют точность после некоторого количества цифр после запятой, поэтому приходится переводить в точное дробное число с типом numeric.
Для этого в PostgreSQL существует ряд функций для работы с числовыми значениями. Вы передаёте функции число или числовое выражение, а она возвращает уже обработанный результат.
Наиболее частой операцией при работе с числами является округление. На эту операцию сделаем акцент.
Возможности функции округления ROUND():
• если передать внутрь функции один аргумент, например ROUND(value), функция по умолчанию округлит число до целого;
• при передаче второго аргумента, такого как ROUND(value::numeric, 2), можно регулировать степень точности округления в дробной части (зачем внутри функции мы переводим значение в тип numeric разберем дальше);
• если второй аргумент отрицательный, например ROUND(value::numeric, -2), функция начнёт округлять уже не дробную часть, а десятки, сотни и тысячи;
• для округления значений до нескольких знаков после запятой в PostgreSQL необходимо использовать тип данных numeric. При попытке выполнить округление с типами данных, такими как double precision, система выдаст ошибку. Вспомним, что типы real, float и double precision не гарантируют точность после некоторого количества цифр после запятой, поэтому приходится переводить в точное дробное число с типом numeric.
Синтаксис
SELECT ROUND(value),
ROUND(value::numeric, 2),
CEIL(value),
FLOOR(value),
TRUNC(value::numeric, 1),
ABS(value)
FROM table_name;
SELECT POWER(value, 2),
SQRT(value),
MOD(value, 3),
DIV(value::numeric, 3::numeric),
RANDOM(),
RANDOM(1, 100);Компактная шпаргалка по функциям.
| Пример | Результат | Что делает |
|---|---|---|
| ROUND(12.7) | 13 | Округляет до целого |
| ROUND(12.675::numeric, 2) | 12.68 | Округляет до нужного количества знаков после запятой |
| ROUND(12567::numeric, -2) | 12600 | Округляет до десятков, сотен, тысяч |
| CEIL(12.1) | 13 | Округляет число до ближайшего верхнего целого |
| FLOOR(12.9) | 12 | Округляет число до ближайшего нижнего целого |
| TRUNC(12.78::numeric, 1) | 12.7 | Обрезает дробную часть без округления |
| ABS(-25) | 25 | Убирает знак минус |
| POWER(5, 3) | 125 | Возводит число в степень |
| SQRT(144) | 12 | Считает квадратный корень |
| MOD(29, 6) | 5 | Показывает остаток от деления: 29 = 6 * 4 + 5 |
| DIV(29::numeric, 6::numeric) | 4 | Показывает количество полных частей: число 6 помещается в 29 четыре раза |
| RANDOM(), RANDOM(1, 100) | Каждый раз новое число | Возвращает случайное число |
Теперь разберём важные нюансы.
1. Один аргумент у ROUND — это округление до целого
Если написать просто ROUND(value), PostgreSQL по умолчанию округлит число до ближайшего целого.
2. ROUND и TRUNC — это не одно и то же
ROUND(12.78::numeric, 1) даст 12.8, потому что число округляется. TRUNC(12.78::numeric, 1) даст 12.7, потому что лишняя часть просто обрезается без округления.
3. Второй аргумент у ROUND умеет работать в обе стороны
Если он положительный, речь идёт о знаках после запятой. Если он равен нулю, округляем до целого. Если он отрицательный, функция начинает округлять десятки, сотни, тысячи.
4. Почему рядом с ROUND часто появляется numeric
Если нужно округлить число до нескольких знаков после запятой, в PostgreSQL приходится явно писать value::numeric. Причина в том, что дробные типы вроде double precision хранят приближённые значения, а numeric даёт предсказуемую точность. Поэтому запись вроде ROUND(value::numeric, 2) выглядит длиннее, но работает.
5. Что важно помнить про RANDOM()
Без аргументов RANDOM() возвращает случайное число от 0 до почти 1. А запись вроде RANDOM(1, 10000) доступная в PostgreSQL 17 и выше и возвращает случайное число в указанном диапазоне.
6. Функции можно вкладывать друг в друга
Это совершенно нормальная практика. Например, можно сначала получить случайное число, а потом привести его к более удобному виду через другую функцию. То же самое относится и к математическим выражениям вроде POWER(SQRT(81), 2). Сначала выполняется внутренняя функция, то есть извлекается квадратный корень из 81, затем результат этого извлечения возводится функцией POWER() во вторую степень.
7. Результат функции лучше сразу называть через псевдоним
Записи вроде ROUND(value::numeric, -2) AS value_to_hundreds или ABS(value - 100000) AS diff_from_target читаются заметно лучше с понятными псевдонимами.
Примеры
1. Сравниваем функции ROUND, CEIL, FLOOR и TRUNC
Наглядный пример, который демонстрирует различия между функциями. ROUND выполняет округление по стандартным правилам, CEIL — всегда в большую сторону до целого числа, FLOOR — в меньшую, а TRUNC — вообще не производит округления, а просто отсекает дробную часть.
🔄 Попробуйте изменить запрос:
- • Замените 42.56 на 42.44 и сравните, как изменится ROUND
- • Поменяйте TRUNC(42.99::numeric, 1) на ROUND(42.99::numeric, 1) и сравните результат
2. Получаем случайное число в нужном диапазоне
Вызов RANDOM() без аргументов возвращает случайное дробное число от 0 до почти 1. А вызовы вроде RANDOM(1, 10000) и RANDOM(50, 500) выводят случайное число в нужном диапазоне.
🔄 Попробуйте изменить запрос:
- • Поменяйте верхнюю границу 10000 на 2500
- • Запустите запрос несколько раз подряд и посмотрите, как меняются значения
3. Округляем число до двух знаков после запятой
В первом столбце число округляется до двух знаков после запятой, а во втором для сравнения просто обрезается через TRUNC. Пример еще раз показывает разницу между округлением и простым отсечением лишних цифр.
🔄 Попробуйте изменить запрос:
- • Поменяйте 12.675 на 12.674 и сравните результат
- • Замените аргумент 2 на 1 и посмотрите, как изменится округление
4. Округляем не дробную часть, а десятки, сотни и тысячи
Отрицательный второй аргумент указывает на то, что функция теперь оперирует не дробной частью числа, а его порядками. Это позволяет выполнять округление до десятков, сотен и тысяч.
🔄 Попробуйте изменить запрос:
- • Поменяйте число 15432 на 14999 и сравните результат округления до сотен и тысяч
- • Замените -3 на 0 и посмотрите, как функция снова переключится на округление до целого
5. Считаем отклонение зарплаты от заданного уровня
Столбец raw_diff демонстрирует отклонение зарплаты, которое может быть как положительным, так и отрицательным. Функция ABS устраняет знак, оставляя только величину отклонения. Это позволяет сравнивать, у кого зарплата ближе к целевому значению, независимо от того, выше она или ниже у другого сотрудника.
🔄 Попробуйте изменить запрос:
- • Замените 100000 на 85000
- • Добавьте в фильтр только отделы 1 и 2
6. Используем POWER и SQRT в одном выражении
Числовые функции можно сочетать между собой. В выражении POWER(SQRT(81) + 1, 2) вычисления идут так: сначала SQRT(81) выдаёт 9, потом к результату прибавляется 1, и уже после этого POWER(10, 2) выводит в результате 100.
🔄 Попробуйте изменить запрос:
- • Поменяйте POWER(5, 3) на POWER(7, 2)
- • Замените 81 на 49 и посмотрите, как изменится вложенное вычисление
7. Считаем полные упаковки и остаток
Например, если у нас 29 товаров и в каждую коробку можно поместить по 6 штук, то DIV покажет, сколько полных коробок мы сможем собрать, а MOD — сколько товаров останется после этого.
🔄 Попробуйте изменить запрос:
- • Поменяйте 6 на 8 и посмотрите, как изменятся количество полных коробок и остаток
8. Считаем, сколько полных коробок потребуется для количества товара
Если одна коробка вмещает 4 единицы товара, то для 17 единиц товара потребуется больше, чем 4 коробки. Для размещения всех товаров понадобится 5 коробок, поскольку после укладки 16 единиц одна коробка останется пустой. В этом случае используется функция CEIL, которая помогает определить необходимое количество коробок с учётом остатка. Во втором столбце представлена аналогичная логика, но для другого размера коробки.
🔄 Попробуйте изменить запрос:
- • Поменяйте размер коробки с 4.0 на 6.0
- • Попробуйте заменить CEIL на FLOOR и объясните, почему коробок станет не хватать
Типичные ошибки
Путают ROUND и TRUNC
ROUND округляет, а TRUNC просто отрезает лишнее. Это две разные функции, и результат у них может отличаться.
Не учитывают, что для ROUND(number, digits) часто нужен numeric
Если нужно округлить число до нескольких знаков после запятой, в PostgreSQL для избежания ошибки нередко приходится явно писать что-то вроде ROUND(value::numeric, 2).
Не знают, что у ROUND второй аргумент может быть отрицательным
ROUND(12567::numeric, -2) — это не ошибка. Так функция округляет число до сотен. Самое то для отчётов и укрупнённых расчётов.
Думают, что CEIL и FLOOR работают как обычное округление
Нет. CEIL всегда округляет вверх, а FLOOR всегда округляет вниз.
Путают DIV и MOD
DIV показывает количество полных частей, а MOD — то, что осталось после деления.
Путают старый и новый сценарий работы с RANDOM()
Без аргументов RANDOM() возвращает дробное число от 0 до почти 1. А запись вроде RANDOM(1, 10000) доступна в PostgreSQL 17 и выше выдаёт число в указанном диапазоне.
Оставляют вычисляемый столбец без понятного имени
Записи вроде ABS(salary - 100000) или ROUND(price::numeric, -2) лучше называть через AS: например diff_from_target или price_to_hundreds.
Практика
Проверь себя
Ответьте на вопросы, чтобы закрепить материал:
1
В чём главное отличие ROUND от TRUNC?
2
Что делает второй аргумент в выражении ROUND(12567::numeric, -2)?
3
Что вернёт выражение MOD(29, 6)? Введите только число.
4
Какой вариант обычно нужен в PostgreSQL, если вы хотите округлить результат RANDOM() до двух знаков после запятой?
5
Что вернёт выражение ROUND(12567::numeric, -2)? Введите только число.
6
Какая функция всегда округляет число вверх до следующего целого?
7
Напишите запрос к таблице order_items, который выведет order_item_id, order_id, quantity и столбец quantity_remainder. В quantity_remainder нужно показать остаток от деления quantity на 4 через MOD. Оставьте только строки, где quantity >= 3. Сначала должны идти меньшие остатки, потом при одинаковом остатке — большие значения quantity, а потом order_item_id.
8
Напишите запрос к таблице products, который выведет product_id, product_name, price и столбец price_thousands_floor. В price_thousands_floor нужно показать, сколько полных тысяч помещается в цене товара, используя FLOOR(price / 1000.0). Оставьте только товары с ценой от 30000 до 95000. Сначала должны идти большие значения price_thousands_floor, а при равенстве — большие цены.