Функция COALESCE в SQL: Почему мы её любим

В области аналитической инженерии неизбежно столкновение с моментами, когда в ваших данных появляются загадочные или бесполезные пустые значения. Null-значения, безусловно, имеют своё время и место, но когда вам нужно, чтобы эти null-значения были заполнены более значимыми данными, на помощь приходит COALESCE.

COALESCE — это невероятно полезная функция, которая позволяет заполнить бесполезные пустые значения, которые могут появиться в ваших данных. Как говорит аналитический инженер Лорен Бенезра, вы, вероятно, почти никогда не увидите модель данных, которая не использует COALESCE где-то.

Что такое SQL-функция? На высоком уровне функция принимает входные данные (или несколько входных данных) и возвращает манипуляцию с этими данными. Некоторые распространённые SQL-функции — это EXTRACT, LOWER и DATEDIFF. Например, функция LOWER принимает строковое значение и возвращает его в виде строки в нижнем регистре.

Как использовать функцию COALESCE

В формальных терминах, использование функции COALESCE на серии значений вернёт первое ненулевое значение.

Общий синтаксис для использования функции COALESCE выглядит следующим образом:

coalesce(<input_1>, <input_2>,...<input_n>)

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

Посмотрите, как это работает: Функция COALESCE используется в макросе surrogate_key для замены null-значений столбца.

Поддержка функции COALESCE в хранилищах данных

Большинство, если не все, современные хранилища данных поддерживают функцию COALESCE; Google BigQuery, Amazon Redshift, Snowflake, Postgres и Databricks все поддерживают функцию COALESCE. Кроме того, синтаксис использования COALESCE одинаков для всех них.

Пример использования функции COALESCE в SQL

Давайте рассмотрим реальный пример использования COALESCE. Ниже у нас есть таблица orders с тремя значениями столбцов: order_id, order_date и order_status.

order_id	order_date	order_status
12389	2022-01-02
34553	2020-04-23	returned
78411	2022-06-06

Если вы немного исследуете эту таблицу, вы увидите, что для order_status есть только два уникальных значения: NULL и returned. Как мы уже говорили, null-значения имеют своё время и место, но если вы впервые смотрите на эту таблицу, null-значение для заказа может означать многое — был ли заказ обработан? Был ли заказ успешным?

В этой таблице orders вы можете предположить, что любое значение NULL в order_status означает, что заказ не был возвращён. Чтобы сделать это более понятным для любого, кто смотрит на эту таблицу, вы можете использовать функцию COALESCE, чтобы вернуть более новое, более читаемое order_status.

select
	order_id,
	order_date,
	coalesce(order_status, 'not_returned') as order_status
from {{ ref('orders') }}

Выполнение этого запроса вернёт следующее:

order_id	order_date	order_status
12389	2022-01-02	not_returned
34553	2020-04-23	returned
78411	2022-06-06	not_returned

Теперь в столбце order_status нет null-значений, так как любое null-значение было заменено строкой not_returned. Статус заказа 34553 остался неизменным, потому что его исходный order_status был первым ненулевым значением, переданным в функцию COALESCE. Предоставляя больше контекста о том, что означают эти null-значения, любой, кто смотрит на эту таблицу, может быстро понять статус заказа для конкретного заказа.

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

Почему мы её любим

Мы проверили проект dbt нашей команды данных и использовали функцию COALESCE более 100 раз. Мы настолько любим функцию COALESCE, что назвали ежегодную конференцию по аналитической инженерии в её честь.

В своей основе функция COALESCE — это эффективный способ заполнения отсутствующих значений столбцов указанными вами значениями. Вы также можете использовать COALESCE в большинстве, если не во всех, современных хранилищах данных, и нет сложного синтаксиса для разных баз данных, как это бывает с DATEADD.

Спасибо, COALESCE, за то, что всегда находишь наши моменты пустоты и заполняешь их ценными данными.