Определение
Возвращает значение первого ненулевого выражения, если таковое имеется, в противном случае NULL. Остальные выражения не вычисляются. Входное выражение может быть любого типа. Может быть несколько типов входных выражений. Все входные выражения должны быть неявно приведены к общему супертипу.
Синтаксис
SELECT COALESCE('A', 'B', 'C') as result
/*--------*
| result |
+--------+
| A |
*--------*/SELECT COALESCE(NULL, 'B', 'C') as result
/*--------*
| result |
+--------+
| B |
*--------*/Практическая информация
- Часто используется аналог IFNULL.
Как включить значения NULL в мои агрегаты?
В этом случае, если вы хотите включить значения NULL в свои агрегаты, таким агрегатом может быть AVG, вы можете использовать COALESCE для преобразования любых значений NULL в число. В этом столбце мы преобразуем все значения NULL в 1 перед получением SUM, поэтому строка NULL включается в customers, но не в строку normal_sum.
SELECT
AVG(COALESCE(is_customer, 0)) AS customers,
AVG(is_customer) AS normal_sum
FROM
(
SELECT
1 AS is_customer
UNION ALL
( SELECT
NULL AS is_customer)
UNION ALL
( SELECT
0 AS is_customer)
) AS table_3
Вывод:
В этом случае результат AVG одного и того же столбца будет разным независимо от того, включена ли строка NULL как 0 или не включена вообще.
Как ОБЪЕДИНИТЬ 2 таблицы с помощью COALESCE?
Допустим, вы хотите объединить две таблицы, и если можете, вы хотите использовать как основной ключ user_id при объединении, но если user_id имеет значение null, вы хотите поменять ключ на имя. Для этого можно использовать COALESCE , чтобы помочь в этом
with table1 as (
SELECT
1 AS user_id,
'Dave' AS name
UNION ALL
(SELECT
NULL AS user_id,
'Nancy' AS name)
UNION ALL
(SELECT
2 AS user_id,
'Bob' AS name)
),
table2 as (SELECT
1 AS user_id,
'Dave' AS name,
87 AS score
UNION ALL
(SELECT
NULL AS user_id,
'Nancy' AS name,
92 AS score))
SELECT
*
FROM
table1
FULL OUTER JOIN table2 ON (COALESCE(CAST(table1.user_id AS STRING), table1.name) = COALESCE(CAST(table2.user_id AS STRING), table2.name))Вывод:
Устранение распространенных ошибок
No matching signature for function COALESCE for argument types. Supported signature: COALESCE([ANY, …])
Эта ошибка возникает, когда BigQuery обнаруживает разные типы данных. Поскольку необходимо создать столбец одного типа, произойдет ошибка, если иногда его значение будет равно STRING, а иногда — INT64 .
Чтобы решить эту проблему, используйте CAST, чтобы убедиться, что все параметры COALESCE имеют правильный тип.
