Примеры использования функций
| Задача | Функция | Пример использования | Расшифровка примера |
|---|---|---|---|
| Извлечь числовое id из utm-метки (или иной параметр). | REGEXP_EXTRACT(value, regex) | REGEXP_EXTRACT(ga_adcontent, "\\\\\|cid\\\\\|(\\\\d+)") | Извлечет id кампании из метки utm_content. |
| Извлечь и заменить любое значение из utm-метки (или из названия кампании, и т.д.). | REGEXP_REPLACE(value, regex, replacement) | REGEXP_REPLACE(ga_campaign, "\\\\\|(\\w+)","Название кампании") | Заменит в стандартной utm-метке К50 "техническое" название кампании на заданное пользователем. |
| Обрезать лишнюю временную детализацию. | DATETIME_TRUNC(Название столбца, Уровень "обрезки" - напр., DAY) | DATETIME_TRUNC(date, DAY) | Обрежет часы на звонках. |
| Посчитать разницу в днях (и др. временных промежутках) между стартовой и конечной датой. | DATETIME_DIFF(datetime_expression, datetime_expression, part) | Calls_plan/(DATETIME_DIFF(Date_end, Date_start, DAY)+1) | Считает разницу между date_end и date_start в днях, +1 добавлено чтобы корректно учесть возможный 0; все выражение считает среднее кол-во звонков на один день флайта. |
| Извлечь номер недели в текстовом формате. | CAST(EXTRACT(ISOWEEK FROM date) AS STRING) | CAST(EXTRACT(ISOWEEK FROM date) AS STRING) | Извлечет номер недели в текстовом формате. |
| Извлечь номер месяца в текстовом формате. | CAST(EXTRACT(MONTH FROM date) AS STRING) | CAST(EXTRACT(MONTH FROM date) AS STRING) | Извлечет номер месяца в текстовом формате. |
| Используется, чтобы при разных условиях совершать разные действия. | CASE WHEN - THEN - ELSE - END | Пример использования CASE WHEN utmsource LIKE "yandex" AND utmmedium LIKE "cpc" THEN REGEXP_EXTRACT(utmcontent, "\\\\\|cid_(\\d+)") WHEN utmsource LIKE "google" AND utmmedium LIKE "cpc" THEN REGEXP_EXTRACT(utmcontent, "\\\\\|cid_(\\d+)") ELSE "No CampaignId" END | Выводим название системы, вместо "технических" значений. |
| Используется, чтобы при разных условиях совершать разные действия. | CASE WHEN - THEN - ELSE - END | Пример использования CASE WHEN utmsource LIKE "yandex" AND utmmedium LIKE "cpc" THEN "Директ" WHEN utmsource LIKE "google" AND utmmedium LIKE "cpc" THEN "AdWords" ELSE "Else" END | Декомпозиция id кампании. |
| Используется, чтобы при разных условиях совершать разные действия. | CASE WHEN - THEN - ELSE - END | CASE WHEN uniqtargetcall THEN 1 ELSE 0 END | Подсчет ун-цел звонков в системе колл-трекинга, для дальнейшего получения суммы звонков. |
| Объединить 2 строки в одну (аналог СЦЕПИТЬ в EXCEL). | CONCAT(value1[, ...]) | - | - |
| Узнать количество символов в строке. | LENGTH(value) | - | - |
| Привести строку к нижнему регистру. | LOWER(value) | LOWER(ga_campaign) | Используется, чтобы привести все к одному регистру для дальнейшей работы с данными и игнорирования фактора регистра. |
| Заменить одно значение на другое. | REPLACE(original_value, from_value, to_value) | - | - |
| Привести строку к верхнему регистру. | UPPER(value) | UPPER(ga_campaign) | Используется реже, обратная функция от LOWER. |
| Посчитать среднее значение (только для настраиваемых метрик). | AVG(expression) [OVER (...)"] | AVG(cost) | Средний расход по сегментам. Можно использовать с OVER, чтобы считать среднее по заданному массиву (например, по кампаниям, которые содержат "Msk"). |
| Посчитать количество (строк) (только для настраиваемых метрик). | COUNT(*) [OVER (...)] / COUNT([DISTINCT] expression) [OVER (...)] | - | - |
| Посчитать количество (строк), удовлетворяющих условию (только для настраиваемых метрик). | COUNTIF(expression) [OVER (...)] | - | - |
| Узнать максимальное значение (только для настраиваемых метрик). | MAX(expression) [OVER (...)] | - | - |
| Узнать минимальное значение (только для настраиваемых метрик). | MIN(expression) [OVER (...)] | - | - |
| Посчитать сумму (только для настраиваемых метрик). | SUM([DISTINCT] expression) [OVER (...)] /> | - | - |
| Округление числовых значений. | ROUND(expression,количество знаков до запятой). | - | - |
| Поменять тип данных для значения. | CAST(expression as тип данных) | CAST(EXTRACT(MONTH FROM date) AS STRING) | Преобразует месяц, извлеченный из даты, и номер месяца конвертирует в текст. Например, чтобы избежать в дальнейшем суммирования значений. |
| Извлечь значение из JSON-объекта. | JSON_EXTRACT(json_string_expr, json_path_string_literal) | Пример использования \[{"tag_name":"обрыв связи","tag_id":62249,"tag_employee_full_name":null"} JSON_EXTRACT(tags, '$.tag_name') {"tag_name":"обрыв связи","tag_id":62249,"tag_employee_full_name":null"}] /> JSON_EXTRACT(tags, '$.tag_name') | Обращение происходит как в JSON-объекту. Начинается со знака $. В первом примере мы сразу обращаемся к нужному значению. Во втором случае мы работаем с массивом, поэтому необходимо указать какой по очереди объект нам нужен. |
| Задача | Функция | Пример использования | Расшифровка примера |
|---|---|---|---|
| Извлечь числовое id из utm-метки (или иной параметр). | REGEXP_EXTRACT(value, regex) | REGEXP_EXTRACT(ga_adcontent, "\\\\\|cid\\\\\|(\\\\d+)") | Извлечет id кампании из метки utm_content. |
| Извлечь и заменить любое значение из utm-метки (или из названия кампании, и т.д.). | REGEXP_REPLACE(value, regex, replacement) | REGEXP_REPLACE(ga_campaign, "\\\\\|(\\w+)","Название кампании") | Заменит в стандартной utm-метке К50 "техническое" название кампании на заданное пользователем. |
| Обрезать лишнюю временную детализацию. | DATETIME_TRUNC(Название столбца, Уровень "обрезки" - напр., DAY) | DATETIME_TRUNC(date, DAY) | Обрежет часы на звонках. |
| Посчитать разницу в днях (и др. временных промежутках) между стартовой и конечной датой. | DATETIME_DIFF(datetime_expression, datetime_expression, part) | Calls_plan/(DATETIME_DIFF(Date_end, Date_start, DAY)+1) | Считает разницу между date_end и date_start в днях, +1 добавлено чтобы корректно учесть возможный 0; все выражение считает среднее кол-во звонков на один день флайта. |
| Извлечь номер недели в текстовом формате. | CAST(EXTRACT(ISOWEEK FROM date) AS STRING) | CAST(EXTRACT(ISOWEEK FROM date) AS STRING) | Извлечет номер недели в текстовом формате. |
| Извлечь номер месяца в текстовом формате. | CAST(EXTRACT(MONTH FROM date) AS STRING) | CAST(EXTRACT(MONTH FROM date) AS STRING) | Извлечет номер месяца в текстовом формате. |
| Используется, чтобы при разных условиях совершать разные действия. | CASE WHEN - THEN - ELSE - END | Пример использования CASE WHEN utmsource LIKE "yandex" AND utmmedium LIKE "cpc" THEN REGEXP_EXTRACT(utmcontent, "\\\\\|cid_(\\d+)") WHEN utmsource LIKE "google" AND utmmedium LIKE "cpc" THEN REGEXP_EXTRACT(utmcontent, "\\\\\|cid_(\\d+)") ELSE "No CampaignId" END | Выводим название системы, вместо "технических" значений. |
| Используется, чтобы при разных условиях совершать разные действия. | CASE WHEN - THEN - ELSE - END | Пример использования CASE WHEN utmsource LIKE "yandex" AND utmmedium LIKE "cpc" THEN "Директ" WHEN utmsource LIKE "google" AND utmmedium LIKE "cpc" THEN "AdWords" ELSE "Else" END | Декомпозиция id кампании. |
| Используется, чтобы при разных условиях совершать разные действия. | CASE WHEN - THEN - ELSE - END | CASE WHEN uniqtargetcall THEN 1 ELSE 0 END | Подсчет ун-цел звонков в системе колл-трекинга, для дальнейшего получения суммы звонков. |
| Объединить 2 строки в одну (аналог СЦЕПИТЬ в EXCEL). | CONCAT(value1[, ...]) | - | - |
| Узнать количество символов в строке. | LENGTH(value) | - | - |
| Привести строку к нижнему регистру. | LOWER(value) | LOWER(ga_campaign) | Используется, чтобы привести все к одному регистру для дальнейшей работы с данными и игнорирования фактора регистра. |
| Заменить одно значение на другое. | REPLACE(original_value, from_value, to_value) | - | - |
| Привести строку к верхнему регистру. | UPPER(value) | UPPER(ga_campaign) | Используется реже, обратная функция от LOWER. |
| Посчитать среднее значение (только для настраиваемых метрик). | AVG(expression) [OVER (...)"] | AVG(cost) | Средний расход по сегментам. Можно использовать с OVER, чтобы считать среднее по заданному массиву (например, по кампаниям, которые содержат "Msk"). |
| Посчитать количество (строк) (только для настраиваемых метрик). | COUNT(*) [OVER (...)] / COUNT([DISTINCT] expression) [OVER (...)] | - | - |
| Посчитать количество (строк), удовлетворяющих условию (только для настраиваемых метрик). | COUNTIF(expression) [OVER (...)] | - | - |
| Узнать максимальное значение (только для настраиваемых метрик). | MAX(expression) [OVER (...)] | - | - |
| Узнать минимальное значение (только для настраиваемых метрик). | MIN(expression) [OVER (...)] | - | - |
| Посчитать сумму (только для настраиваемых метрик). | SUM([DISTINCT] expression) [OVER (...)] /> | - | - |
| Округление числовых значений. | ROUND(expression,количество знаков до запятой). | - | - |
| Поменять тип данных для значения. | CAST(expression as тип данных) | CAST(EXTRACT(MONTH FROM date) AS STRING) | Преобразует месяц, извлеченный из даты, и номер месяца конвертирует в текст. Например, чтобы избежать в дальнейшем суммирования значений. |
| Извлечь значение из JSON-объекта. | JSON_EXTRACT(json_string_expr, json_path_string_literal) | Пример использования \[{"tag_name":"обрыв связи","tag_id":62249,"tag_employee_full_name":null"} JSON_EXTRACT(tags, '$.tag_name') {"tag_name":"обрыв связи","tag_id":62249,"tag_employee_full_name":null"}] /> JSON_EXTRACT(tags, '$.tag_name') | Обращение происходит как в JSON-объекту. Начинается со знака $. В первом примере мы сразу обращаемся к нужному значению. Во втором случае мы работаем с массивом, поэтому необходимо указать какой по очереди объект нам нужен. |
Дополнительно вы можете воспользоваться справочными материалами BigQuery.