Давайте поговорим о еще одной функции для работы с загадочным NULL — NULLIF(). Она поможет нам в ситуациях, когда определенные значения нужно заменять на NULL, чтобы корректно обрабатывать данные. Пристегивайте ремни, это будет увлекательное путешествие!
Функция NULLIF() — это отличный инструмент в PostgreSQL, который позволяет вам сравнить два значения и вернуть NULL, если они совпадают, или вернуть первое значение, если они не равны. Она словно говорит: "Если эти значения одинаковые, забудем про них!"
Вот простой синтаксис функции:
NULLIF(value1, value2)
Если value1 и value2 равны, то результатом будет NULL. Если они различны, результатом будет value1.
Пример:
SELECT NULLIF(10, 10); -- Результат: NULL
SELECT NULLIF(10, 20); -- Результат: 10
SELECT NULLIF('hello', 'hello'); -- Результат: NULL
SELECT NULLIF('hello', 'world'); -- Результат: 'hello'
Просто, правда? Теперь давайте попробуем применить эту функцию в реальных задачах.
Пример 1: Предотвращение деления на ноль
Деление на ноль — это кошмар любого программиста. В SQL попытка разделить на ноль выдаст ошибку. Вот тут-то и приходит на помощь NULLIF().
Представьте ситуацию: у нас есть таблица sales с двумя столбцами: revenue (доходы) и expenses (расходы). Мы хотим вычислить рентабельность расходов (revenue / expenses), но иногда расходы равны нулю. Если мы просто попробуем выполнить запрос без какой-либо проверки, это приведёт к ошибке деления на ноль.
Таблица sales:
| revenue | expenses |
|---|---|
| 1000 | 200 |
| 1500 | 0 |
| 2000 | 250 |
Ошибочный запрос:
SELECT revenue / expenses AS profitability
FROM sales;
-- Ошибка: деление на ноль!
Решение с NULLIF():
SELECT revenue / NULLIF(expenses, 0) AS profitability
FROM sales;
Результат:
| profitability |
|---|
| 5.00 |
| NULL |
| 8.00 |
Теперь, если expenses равно 0, NULLIF(expenses, 0) возвращает NULL, а деление на NULL не вызывает ошибки, а просто возвращает NULL.
Пример 2: Замена одинаковых значений на NULL
Представьте, что у вас есть таблица с данными о студентах, где есть столбцы first_name и preferred_name. Иногда студенты предпочитают своё настоящее имя, а иногда выбирают что-то другое. Если оба имени совпадают, нам нет смысла отображать preferred_name.
Таблица students:
| first_name | preferred_name |
|---|---|
| John | Johnny |
| Anna | Anna |
| Alex | Lex |
Запрос с NULLIF():
SELECT first_name,
NULLIF(preferred_name, first_name) AS display_name
FROM students;
Результат:
| first_name | display_name |
|---|---|
| John | Johnny |
| Anna | NULL |
| Alex | Lex |
Когда preferred_name и first_name равны, результатом будет NULL.
Пример 3: Фильтрация данных
Иногда нужно выбрать только те строки, где значения в двух столбцах различны. Например, у нас есть таблица заказов с original_price и discounted_price. Нам нужно найти заказы, где была применена скидка.
Таблица orders:
| order_id | original_price | discounted_price |
|---|---|---|
| 1 | 100 | 100 |
| 2 | 200 | 180 |
| 3 | 150 | 150 |
Запрос с NULLIF():
SELECT order_id, original_price, discounted_price
FROM orders
WHERE NULLIF(original_price, discounted_price) IS NOT NULL;
Результат:
| order_id | original_price | discounted_price |
|---|---|---|
| 2 | 200 | 180 |
Только заказы со скидкой попали в результат.
Практические кейсы использования NULLIF()
Кейc 1: Выбор опционального значения
При работе с данными иногда нужно выбрать значение из нескольких столбцов, исключая те, которые равны определённому значению. Например, у нас есть список сотрудников с их зарплатами: base_salary и bonus. Если бонус равен 0, мы хотим его установить его в 999.
Таблица employees:
| employee_id | base_salary | bonus |
|---|---|---|
| 1 | 50000 | 10000 |
| 2 | 40000 | 0 |
| 3 | 60000 | 5000 |
Запрос с NULLIF():
SELECT employee_id,
base_salary + COALESCE(NULLIF(bonus, 0), 999) AS total_salary
FROM employees;
Результат:
| employee_id | total_salary |
|---|---|
| 1 | 60000 |
| 2 | 40999 |
| 3 | 65000 |
Если bonus равен 0, он превратиться в 999.
Кейс 2: Подсчет строк
Мы можем использовать NULLIF() для упрощения сложных условий фильтрации. Например, у нас есть таблица пользователей и мы хотим за один запрос узнать и их общее количество и количество зарегистрированных пользователей (без статуса guest).
SELECT
COUNT(*) AS total_users,
COUNT(NULLIF(status, 'guest')) AS registered_users
FROM users;
Вот так, с помощью всего одной функции NULLIF() мы можем решить множество задач: от предотвращения ошибок до простого улучшения читаемости кода. Теперь, когда вы знаете, как она работает, вы уже на шаг ближе к мастерству в работе с данными в PostgreSQL.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ