Загрузка...
SQL 29.03.2022

Синтаксический сахар SQL: функция COALESCE

Алексанян Андрон CEO IT Resume

Если Вы работаете с SQL, то скорее всего Вы сталкивались с выражением COALESCE. Если же Вы с ним не знакомы, то самое время сделать это сейчас - это очень крутая штука, которая пригодится Вам в решении большого количества задач.

Итак, начнем с самого простого - что такое COALESCE?

Определение

COALESCE - это специальное выражение, которое вычисляет по порядку каждый из своих аргументов и на выходе возвращает значение первого аргумента, который был не NULL.

Пример:

SELECT COALESCE(NULL, NULL, 1, 2, NULL, 3) # 1

Этот запрос вернет 1, потому что первые два аргумента NULL, а третий аргумент принимает значение отличное от NULL. Соответственно, выражение COALESCE даже не будет смотреть дальше - главное, что 3 аргумент не NULL.

Другие примеры:

SELECT COALESCE(1, NULL, 2, NULL) # 1

или

SELECT COALESCE(NULL, NULL) # NULL

Наверно, вы поняли основной смысл. Если можно вернуть не NULL - возвращается первое непустое значение. Если нельзя - возвращается NULL.

Стоит отметить, что COALESCE используется во многих популярных СУБД: PostgreSQL, MS SQL Server, Oracle, MySQL и так далее.

Давайте теперь копнем чуть глубже и посмотрим, как же это выражение устроено.

Аналогия с CASE

Мы разобрались, как выражение COALESCE работает, но сможем ли мы написать его с нуля? Допустим, нам дали задание:

Не используя COALESCE, напишите SELECT-запрос, который будет аналогичен запросу SELECT COALESCE(expression1, expression2, expression3, expression4)

Сможете решить такую задачу сходу? На самом деле, еще решение довольно простое: COALESCE - это просто удобная обертка для конструкции CASE. Ее удобно использовать для обработки значений NULL.

Учитывая, что обработка пропусков - типовая задача при написании SQL-запросов, решили выделить эту конструкцию в отдельное выражение COALESCE, чтобы бедные разработчики не мучались каждый раз и не громоздили огромные CASE-выражения.

Давайте же и мы с вами решим эту задачу. Ответ будет такой:

SELECT CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 WHEN (expression3 IS NOT NULL) THEN expression3 ELSE expression4 END

Вот, собственно, и все премудрости. Согласитесь - не сложно. Но значительно удобней писать COALESCE, чем каждый раз записывать такую объемную конструкцию с CASE.

Боевая задача

Давайте на реальном примере рассмотрим, когда использование COALESCE - не просто желание побаловаться, а реальная необходимость.

Вы можете сказать:

Я столько времени пишу SQL-запросы и ни разу не пользовался COALESCE!

Ну, что же, возможно Вам просто везло… Но с высокой долей вероятности некоторые Ваши запросы начнут выдавать неправильные результаты, если таблицы начнут заполняться «некрасивыми» данными.

Рассмотрим простую задачу. Пусть есть бухгалтерская таблица, в которой содержится информация о имени сотрудника и его ежемесячной премии. В конце года перед нами встала тривиальная задачка (на первый взгляд) - посчитать суммарный заработок каждого сотрудника.

+--------+----------+-----------+--------------+ | ID | name | bonus | date | +--------+----------+-----------+--------------+ | 1 | Ivan | NULL | 2020-01-01 | | 1 | Maya | 3500 | 2020-01-03 | | 1 | Dora | 4500 | 2020-01-02 | | 1 | Petr | 5750 | 2020-02-01 | | 1 | Ivan | 3220 | 2020-03-05 | | ... | +--------+----------+-----------+--------------+

Так получилось, что у сотрудника Ivan не прогрузился бонус за январь и в таблице стоит значение NULL. Соответственно, приведенный ниже запрос вернет 3220 для этого сотрудника.

SELECT name, SUM(bonus) FROM table_name GROUP BY name

Мало того, что результат получился неверным - мы об этом даже не узнаем. А если записей много, то различные расчеты и агреграции постоянно будут приводить к скрытым ошибкам (проверено на практике).

Чтобы такой ситуации избежать, можно заменять значения NULL на очень большое число, например. Тогда мы в качестве результата будем получать премию в несколько миллиардов и понимать, что что-то здесь не так.

Естественно, в зависимости от задачи приемы могут быть разные: этот пример иллюстрирует ручную обработку таблицы с премиями.

В нашем же случае решение можно записать так:

SELECT name, SUM(COALESCE(bonus, 1000000)) FROM table_name GROUP BY name

Эпилог

Мы рассмотрели выражение COALESCE и показали его важность. Посмотрите на те запросы, которые Вы пишите каждый день - нет ли там места ошибке? Может и у Вас могут возникнуть какие-то ситуации, когда расчет будет неправильным, а Вы даже не сможете этого увидеть? Чтобы избежать таких ситуаций, используйте COALESCE!

SQL 29.03.2022
Теги: SQL