Конструкция CASE WHEN в Google BigQuery

Конструкция CASE WHEN в GBQ предназначена для выбора значения по одному или нескольким условия.

Когда условий несколько (IF ELSEIF) то конструкция выглядит следующим образом:

CASE
WHEN (УСЛОВИЕ_1) THEN 'значение_1'
WHEN (УСЛОВИЕ_2) THEN 'значение_2'
WHEN (УСЛОВИЕ_3) THEN 'значение_3' ELSE 'значение по-умолчанию'
END AS `название_столбца`

Читается данная конструкция так

если случилось УСЛОВИЕ_1, то значение установить значение_1, иначе
если случилось УСЛОВИЕ_2, то значение установить значение_2, иначе
если случилось УСЛОВИЕ_3, то значение установить значение_3, иначе
значение значение по умолчанию

При этом последовательность выполнения условий идет сверху вниз, при срабатывании одно из условий проверка условий ниже сработавшего не происходит.

Пример ниже, считает число событий web_add_to_cart (добавлений в корзину) для отдельных страниц и определяет тип страниц по условию в конструкции CASE WHEN:

SELECT `e`.value.string_value AS `url`, COUNT(`user_pseudo_id`) AS `cnt`,
CASE
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'product') IS NOT NULL THEN 'product'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'catalog') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/l/') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/s/') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'actions') IS NOT NULL THEN 'actions'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'blogs') IS NOT NULL THEN 'blogs'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/search') IS NOT NULL THEN 'search'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/cart') IS NOT NULL THEN 'cart'
ELSE 'other'
END AS `url_type`
FROM `firebase-buysiberian.analytics_XXX.events_2023*`,
UNNEST(`event_params`) AS `e`
WHERE `event_name` = 'web_add_to_cart'
AND `e`.value.string_value LIKE 'https://mysite.ru/%'
AND `e`.key = 'page_location'
GROUP BY `e`.value.string_value

Результат запроса

Изображение 1. Результат определения типа URL конструкцией CASE WHEN

Практический пример

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

WITH
`web_add_to_cart_cnt` AS (SELECT `e`.value.string_value AS `url`, COUNT(`user_pseudo_id`) AS `cnt`,
CASE
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'product') IS NOT NULL THEN 'product'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'catalog') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/l/') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/s/') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'actions') IS NOT NULL THEN 'actions'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'blogs') IS NOT NULL THEN 'blogs'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/search') IS NOT NULL THEN 'search'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/cart') IS NOT NULL THEN 'cart'
ELSE 'other'
END AS `url_type`
FROM `firebase-buysiberian.analytics_XXX.events_20230710`,
UNNEST(`event_params`) AS `e`
WHERE `event_name` = 'web_add_to_cart'
AND `e`.key = 'page_location'
GROUP BY `e`.value.string_value),
`page_view_cnt` AS (SELECT `e`.value.string_value AS `url`, COUNT(`user_pseudo_id`) AS `cnt`,
CASE
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'product') IS NOT NULL THEN 'product'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'catalog') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/l/') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/s/') IS NOT NULL THEN 'catalog'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'actions') IS NOT NULL THEN 'actions'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'blogs') IS NOT NULL THEN 'blogs'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/search') IS NOT NULL THEN 'search'
WHEN REGEXP_EXTRACT(`e`.value.string_value, r'shop/cart') IS NOT NULL THEN 'cart'
ELSE 'other'
END AS `url_type`
FROM `firebase-buysiberian.analytics_XXX.events_20230710`,
UNNEST(`event_params`) AS `e`
WHERE `event_name` = 'page_view'
AND `e`.key = 'page_location'
GROUP BY `e`.value.string_value)
SELECT `web_add_to_cart_cnt`.`url_type`, SUM(`web_add_to_cart_cnt`.`cnt`) / SUM(`page_view_cnt`.`cnt`) FROM `web_add_to_cart_cnt`
LEFT JOIN `page_view_cnt` ON (`page_view_cnt`.`url` = `web_add_to_cart_cnt`.`url`) 
GROUP BY `url_type`

Таблица web_add_to_cart_cnt – количество событий web_add_to_cart по URL с указанием его типа, таблица page_view_cnt – количество событий page_view по URL с указанием его типа и агрегирующая итоговая таблица с подсчетом конверсии сгруппированная по типам страниц.

Результат запроса:

Изображение 2. Результат подсчета конверсии корзины к просмотрам по типа URL конструкцией CASE WHEN