PostgreSQL: возврат 0, если avg(price) пусто (NULL)

Быстрый ответ

Заменить NULL на 0 в PostgreSQL поможет функция COALESCE(ваша_колонка, 0):

SELECT COALESCE(ваша_колонка, 0) FROM ваша_таблица;

Следовательно, если значение в ваша_колонка равно NULL, будет возвращён 0.

Тонкости обращения с null-значениями

Давайте рассмотрим некоторые интересные особенности обработки NULL в SQL, приводя в примеры вычисление средних значений и суммы.

COALESCE и AVG: злоупотребление дружественными отношениями?

Когда вы рассчитываете среднее значение, может появиться следующая проблема:

SELECT AVG(COALESCE(цена, 0)) FROM продукты;

В данном примере продукты с ценой NULL будут учтены как 0, что снизит среднее значение. Это ведь неправильно по отношению к дорогим продуктам Apple, не так ли? 🍎💰

Подробнее об этом расскажет наш спикер на видео

Безоблачно: история о SUM и COALESCE

Функция SUM сходится с COALESCE, заменяя NULL на 0:

SELECT COALESCE(SUM(цена), 0) FROM продукты;

Продукты ещё не продавались? Без переживаний, сумма продаж будет нулевой! Как и затраты на невидимые продукты! 💸👻

WHERE и NULL: справедливый бой

Если вы предпочли игнорировать NULL:

SELECT AVG(цена) FROM продукты WHERE цена IS NOT NULL;

Такой запрос включает только те продукты, у которых цена указана. Таким образом, ваши дорогие Mac-устройства будут защищены от NULL-значений. 🛡️💻

Визуализация

Понятие значений по умолчанию в SQL можно сравнить с ситуацией, связанной с фруктами 🧺🍇🍈🍉:

Представим, что в корзине нет яблока 🍎:

SELECT COALESCE(недостающий_фрукт, '🍎') FROM корзина;

Функция COALESCE заменяет пустое место (🚫) яблоком (🍎), благодаря чему корзина всегда выглядит полной:

До: [🍇, 🍈, 🚫, 🍉]  После: [🍇, 🍈, 🍎, 🍉]
// COALESCE: Наполняет пустые места с 1974 года 🍏🍎

Если вместо яблока ожидается ноль:

До: [🍇, 🍈, 🚫, 🍉]  После: [🍇, 🍈, 0️⃣, 🍉]
// Нет яблок? Значит, яблок нет. Такова судьба. 🍅

Разбор функции COALESCE

Любому SQL-специалисту полезно будет уметь использовать COALESCE для обеспечения достоверности получаемых результатов при наборе неполных данных.

COALESCE совместно с объединенными таблицами

Если после выполнения LEFT JOIN появляются значения NULL, функция COALESCE поможет заменить их на 'значениепоумолчанию':

SELECT a.id, COALESCE(b.атрибут, 'значение_по_умолчанию') FROM таблица_a a
LEFT JOIN таблица_b b ON a.id = b.внешний_ключ;

Опасаетесь пустых результатов после объединения? COALESCE справится с этим! 🍻🥳

COALESCE основательно взаимодействует с CASE

Объединяя функции COALESCE и CASE, можно достичь высокой точности в назначении значений по умолчанию:

SELECT id, COALESCE(
    CASE WHEN условие THEN расчет ELSE NULL END,
    значение_по_умолчанию
) FROM таблица;

Сработавшись с COALESCE, вы получите результат мечты! 🧳🤝

COALESCE управляет нечисловыми данными

COALESCE идеально подходит для работы с различными типами данных, будь то строки или даты:

SELECT COALESCE(колонка_строка, 'стандартная_строка') FROM таблица;
SELECT COALESCE(колонка_дата, CURRENT_DATE) FROM таблица;

Строки, даты, числа... Все они встретились в баре. Кого повстречают первым? COALESCE! 🥂🍺

Распространенные ловушки: исчезающий ноль в среднем

Помните, что включение 0 в вычисление AVG вместо NULL, которое обозначает отсутствие данных, может серьёзно исказить искомое среднее значение.

SELECT AVG(цена) FROM продукты WHERE цена IS NOT NULL;

Исчезающие нули в средних значениях могут исказить картину: 🕵️🎱

Полезные материалы

1. PostgreSQL: Documentation: 9.18. Условные выражения — Подробное описание функции COALESCE в официальной документации PostgreSQL.
2. sql – Как преобразовать строки в столбцы в индексированном виде? – Stack Overflow — Пример применения функции COALESCE в вопросе на Stack Overflow.
3. Обработка NULL – PostgreSQL wiki — Обзор методов работы со значениями NULL на вики-странице PostgreSQL.
4. Конкатенация значений рядов в Transact-SQL – Simple Talk — Узнайте больше о работе функции COALESCE с функциями обработки строк.