Основные функции SQL в BigQuery

Какие бывают функции в BigQuery SQL:

Агрегатные функции

Статистические агрегатные функции

Приблизительные агрегатные функции

Функции ранжирования (аналитические)

Функции преобразования

Математические функции

Навигационные функции (смещения)

Хэш функции

Строковые функции

JSON функции

Функции массива

Функции даты и времени date time datetime timestamp

Интервальные функции

Географические функции Нужно понимать различие агрегационных функций от оконных. Подробнее об оконных функциях в нашей статье

Иллюстрация: Основные функции SQL в BigQuery

Функции для агрегации данных в BigQuery

COUNT COUNT() — функция возвращающая количество записей (строк) из таблицы. Запись функции с указанием столбца вернет количество записей конкретного столбца за исключением NULL записей. Для примера возьмем таблицу:

Например, хотим узнать сколько у нас записей в таблице с источником "google" Синтаксис функции: COUNT (название столбца) Если мы хотим посчитать что-то конкретное, то нужно это указать в условии.

Результат

SQL

SELECT
  COUNT(SOURCE) AS SOURCE
FROM
  `business-metric.project.new_table1`
WHERE
  SOURCE = 'google'
 ИЛИ
SELECT
  COUNT(*) AS SOURCE
FROM
  `business-metric.project.new_table1`
WHERE
  SOURCE = 'google'

COUNT (* ) - вернет количество всех записей в таблице

SQL

SELECT
  COUNT(*) AS count_table
FROM
  `business-metric.project.new_table1`

COUNT (DISTINCT (название столбца) ) - количество уникальных строк в столбце

SQL

SELECT
  COUNT(DISTINCT(source)) AS c_source
FROM
  `business-metric.project.new_table1`

COUNTIF COUNTIF() — функция возвращающая количество записей (строк) при определенном условии. Подсчитаем количество событий с названием "page_view" и количество покупок на сайте и в приложении "in_app_purchase & purchase"

SQL

SELECT
  COUNTIF(event_name = 'page_view') AS count_page_view,
  COUNTIF(event_name IN ('in_app_purchase','purchase')) AS count_purchase
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20201201'
  AND '20201202'

AVG AVG() — функция возвращающая среднее значение столбца. Данная функция применима только для числовых столбцов. Обычно используется вместе с ROUND, рассмотрим ниже. Синтаксис: AVG (название столбца) AVG (DISTINCT(название столбца)) - считаются только неповторяющиеся значения

SQL

SELECT
  AVG(conversions) AS avg_conv
FROM
  `business-metric.project.new_table1`

SQL

SELECT
  AVG(distinct(conversions)) AS avg_conv
FROM
  `business-metric.project.new_table1`

MIN MIN() — функция возвращающая минимальное значение столбца. Синтаксис: MIN ( название столбца )

SQL

SELECT
  MIN(conversions) AS min_conv
FROM
  `business-metric.project.new_table1`

MAX MAX() — функция возвращающая максимальное значение столбца таблицы. Синтаксис: MAX (название столбца )

SQL

SELECT
  MAX(conversions) AS max_conv
FROM
  `business-metric.project.new_table1`

SUM SUM() — функция, возвращающая сумму значений столбца таблицы. Синтаксис: SUM (название столбца)

SQL

SELECT
  SUM(conversions) AS sum_conv
FROM
  `business-metric.project.new_table1`

SUM ( DISTINCT (название столбца)) - суммируются только уникальные значения

SQL

SELECT
  SUM(DISTINCT(conversions)) AS sum_conv
FROM
  `business-metric.project.new_table1`

Функции ранжирования (аналитические) в Google BigQuery

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

SQL

SELECT num,
  RANK() OVER (ORDER BY num ASC) AS rank
FROM Numbers

Для примера рассмотрим задачу, найти первого человека из определенной группы совершившего конверсию/покупку

Нам нужно разбить пользователей на две группы (PARTITION BY): источник 1 = google источник 2 = yandex После чего сортировать по времени покупки от меньшего к большему (ORDER BY ASC)

Результат

SQL

SELECT name,
  purchase_time,
  source,
  RANK() OVER (PARTITION BY source ORDER BY purchase_time ASC) AS ranks
FROM first_visitor

У нас получились две группы пользователей которым присвоили ранги. Теперь мы видим когда была совершена первая покупка из источника google или yandex.

DENSE_RANK Возвращает порядковый номер каждой строки. Все одноранговые строки получают одинаковый номер, а последующее значения увеличивается на единицу.

Отличие от RANK в том, что увеличение происходит на единицу и не зависит от самого числа.

SQL

SELECT num,
  DENSE_RANK() OVER (ORDER BY num ASC) AS dense_rank
FROM Numbers

ROW_NUMBER Возвращает последовательный порядковый номер, независимо от того повторяются ли значения или нет.

SQL

SELECT num,
  ROW_NUMBER() OVER (ORDER BY num) AS row_num
FROM Numbers

CUME_DIST Вычисляет интегральное распределение (относительное положение) значений в окне. Возвращает относительный ранг строки. Определяется как количество строк, которые либо предшествуют текущей строке, либо являются равноправными с ней.

SQL

select date,source,conversion, 
  CUME_DIST() OVER(PARTITION BY date ORDER BY conversion) AS cume_dist
from `united-strategy-279207.Example.conversion`

SQL

SELECT name,
  purchase_time,
  source,
  CUME_DIST() OVER (PARTITION BY source ORDER BY purchase_time ASC) AS ranks
FROM first_visitor

PERCENT_RANK Вычисляет относительный ранг строки в окне

SQL

select date,source,conversion, 
  PERCENT_RANK() OVER(PARTITION BY date ORDER BY conversion) AS percent_rank
from `united-strategy-279207.Example.conversion`

SQL

SELECT name,
  purchase_time,
  source,
  PERCENT_RANK() OVER (PARTITION BY source ORDER BY purchase_time ASC) AS p_rank
FROM first_visitor

NTILE Оконная функция, которая позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках.

SQL

SELECT date, source, conversion,
  NTILE(4) OVER(PARTITION BY date ORDER BY conversion) AS n_tile
FROM
  `united-strategy-279207.Example.conversion`

Математические функции в Google BigQuery

ROUND ROUND() — функция для округления десятичных чисел. Синтаксис: ROUND (название столбца, количество знаков после запятой/точки) Например, есть таблица

Округлим значение до одного знака после точки

SQL

SELECT
  name,ROUND(coef,1) as r_coef
FROM
  `business-metric.project.new_table`

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

SQL

SELECT
  ROUND(AVG(conversions),1) AS avg_conv
FROM
  `business-metric.project.new_table1`

Если хотим посчитать не повторяющиеся средние значения, то:

SQL

SELECT
  ROUND(AVG(DISTINCT(conversions)),2) as r_avg_conv
FROM
  `business-metric.project.new_table1`

Навигационные функции в Google BigQuery

FIRST_VALUE и LAST_VALUE C помощью этих функций можно получить первое и последнее значение в окне. В качестве параметра принимает столбец, значение которого необходимо вернуть.

SQL

select date, source,conversion, 
  FIRST_VALUE(conversion) OVER(PARTITION BY date ORDER BY date) AS f_value, 
  LAST_VALUE(conversion) OVER(PARTITION BY date ORDER BY date) AS l_value
from `united-strategy-279207.Example.conversion`

LAG Оконная функция, которая возвращает предыдущее значение столбца по порядку сортировки.

SQL

select date, source,conversion, 
  LAG(conversion) OVER(PARTITION BY date ORDER BY date) AS lag
from `united-strategy-279207.Example.conversion`

LEAD Функция LAG обращается к данным из предыдущей строки окна, а LEAD к данным из следующей строки. Функцию можно использовать для того, чтобы сравнивать текущее значение строки с предыдущим или следующим. Имеет три параметра: столбец, значение которого необходимо вернуть, количество строк для смещения (по умолчанию 1), значение, которое необходимо вернуть если после смещения возвращается значение NULL

SQL

select date, source,conversion, 
  LEAD(conversion) OVER(PARTITION BY date ORDER BY date) AS lead
from `united-strategy-279207.Example.conversion`

PERCENTILE_CONT и PERCENTILE_DISC PERCENTILE_CONT - вычисляет процентиль на основе постоянного распределения значения столбца. В качестве параметра принимает процентиль, который необходимо вычислить PERCENTILE_DISC — вычисляет определенный процентиль для отсортированных значений в наборе данных. В качестве параметра принимает процентиль, который необходимо вычислить.

SQL

select date,source,conversion, 
  PERCENTILE_CONT(conversion, 0.5) OVER (PARTITION BY date) as p_cont,
  PERCENTILE_DISC(conversion, 0.5) OVER(PARTITION BY date) AS p_disc
from `united-strategy-279207.Example.conversion`

Строковые функции в Google BigQuery

Строковые функции позволяют изменять строки, выделять и заменять подстроки, рассчитывать длину строки и порядковый номер. На

пример: Обработать URL или UTM метку для удобного отображения. Заменить или удалить ненужные данные из текста, исправить ошибку, опечатку в ячейке. Изменить регистр текста, привести к единому формату. UPPER UPPER() — функция, возвращающая значения столбца или столбцов в верхнем регистре. Синтаксис: UPPER (название столбца)

SQL

SELECT
  UPPER(SOURCE) AS u_source
FROM
  `business-metric.project.new_table1`

LOWER LOWER() - функция, возвращающая значения столбца или столбцов в нижнем регистре. Синтаксис: LOWER (название столбца)

LENGTH LENGTH() - функция, возвращающая длину значения в поле записи. Функция исключает из подсчета конечные пробелы. Синтаксис: LENGTH (название столбца)

SQL

SELECT
  LOWER(SOURCE) AS low_source
FROM
  `business-metric.project.new_table1`

SQL

SELECT
  source,LENGTH(SOURCE) AS l_source
FROM
  `business-metric.project.new_table1`

CONCAT CONCAT() - функция, которая объединяет одно или несколько значений в одно. Все значения должны быть в формате, который можно преобразовать в STRING

SQL

SELECT CONCAT('google',' / ', 'cpc') as source_medium

Например, есть таблица с столбцами источник и канал

SQL

SELECT
  CONCAT(source, ' / ', medium) AS source_medium
FROM source_m

REPLACE REPLACE() - Заменяет одно значение на другое, если поле пустое или нет совпадения, то замена не происходит. Синтаксис: REPLACE (название столбца, "значение которое хотим изменить", "значение на которое меняем") Например, есть таблица с источником в которой мы хотим изменить "social" на "cpm"

SQL

SELECT
  REPLACE(source_medium,'social','cpm') as new_source
FROM source_m

FORMAT Изменяем формат отображения значения в ячейке. Каждый спец символ вводится с "%" Более подробно в справке от google

SQL

SELECT
  FORMAT("%'d", 123456789) AS f_1,
  FORMAT("%'x", 123456789) AS f_2,
  FORMAT("%'o", 33333) AS f_3

Функции для регулярных выражений REGEXP Для работы с регулярными выражениями у нас есть отдельная статья JSON функции По работе с файлами с форматом и типом строк JSON есть отдельная статья.

Функции даты и времени в Google BigQuery

Работа с датами в BigQuery (статья) CURRENT_(DATE | DATETIME | TIME | TIMESTAMP) CURRENT_DATE - возвращает текущую дату в формате ГГГГ-ММ-ДД CURRENT_DATETIME - возвращает текущую дату в формате ГГГГ-ММ-ДД ЧЧ-ММ-СС.мс CURRENT_TIME - возвращает текущее время CURRENT_TIMESTAMP - возвращает текущую дату с временем по UTC

SQL

SELECT
  CURRENT_DATE() AS now_d,
  CURRENT_DATETIME() AS now_dt,
  CURRENT_TIME() AS now_t,
  CURRENT_TIMESTAMP() AS c_timestamp

DATE | DATETIME | TIME | TIMESTAMP DATE - создает дату состоящую из года, месяца и дня. Также можно создать и дату с временем, но время отображаться не будет. Тоже и с временной зоной. DATETIME - создает дату состоящую из года, месяца, дня, часа, минуты и секунды. Создает дату с указанием timestamp.

При указании временной зоны, время, которое вы указали будет изменено на время в зоне которую вы указали, в нашем случае это Дубай. TIME - создает время в формате ЧЧ:ММ:СС TIMESTAMP - создает дату и время в формате UTC

SQL

SELECT
-- DATE
  DATE(2022, 08, 30) AS date_,
  DATE(DATETIME "2022-08-30 23:59:59") AS date_dt,
-- DATETIME
  DATETIME(2022, 08, 30, 12, 33, 11) AS datetime,
  DATETIME(TIMESTAMP "2022-08-30 11:30:00+00", "Asia/Dubai") AS datetime_tz,
-- TIME
  TIME(12, 45, 10) AS time_,
-- TIMESTAMP
  TIMESTAMP("2022-08-30 10:25:15+00") AS timestamp_

EXTRACT (DATE | DATETIME | TIME | TIMESTAMP ) DATE Возвращает значение указанной части даты выбранное в условии Возвращаемое значение может быть: DAY - возвращает день из даты. Принимает значения от 1 до 31 включительно DAYOFWEEK - возвращает значение дня (от 1 до 7), начало недели - воскресенье DAYOFYEAR WEEK - возвращает номер недели (от 0 до 53) WEEK(<WEEKDAY>) - возвращает номер недели (от 0 до 53) с указанием дня недели: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY MONTH - возвращает порядковый номер месяца из даты. Принимает значения от 1 до 12 включительно QUARTER - квартал, возвращает значение от 1 до 4 YEAR - возвращает год из даты ISOWEEK ISOYEAR

SQL

SELECT EXTRACT(DAY FROM DATE '2022-08-30') AS day

SQL

SELECT EXTRACT(DAYOFWEEK FROM DATE '2022-08-30') AS day_w

DATETIME Возвращает значение указанной части даты выбранное в условии. Возвращаемое значение может быть: MICROSECOND MILLISECOND SECOND MINUTE HOUR DAYOFWEEK DAY DAYOFYEAR WEEK WEEK(<WEEKDAY>) MONTH QUARTER YEAR DATE TIME ISOWEEK ISOYEAR

SQL

SELECT EXTRACT(HOUR FROM DATETIME(2022, 08, 30, 22, 15, 00)) as hour

SQL

SELECT EXTRACT(MINUTE FROM DATETIME("2022-08-30 23:40:59")) as minute

TIME Возвращает значение указанной части даты выбранное в условии. Возвращаемое значение может быть: MICROSECOND MILLISECOND SECOND MINUTE HOUR

SQL

SELECT EXTRACT(HOUR FROM TIME "10:25:40") as hour

TIMESTAMP Возвращает значение указанной части даты выбранное в условии. Возвращаемое значение может быть: MICROSECOND MILLISECOND SECOND MINUTE HOUR DAYOFWEEK DAY DAYOFYEAR WEEK WEEK(<WEEKDAY>) MONTH QUARTER YEAR TIME DATE DATETIME ISOWEEK ISOYEAR

SQL

SELECT
  EXTRACT(DAY FROM TIMESTAMP("2022-08-30 15:40:00+00") AT TIME ZONE "UTC") AS day_utc

DATE_ADD | DATETIME_ADD | TIME_ADD | TIMESTAMP_ADD Возвращает дату, время , увеличивая ее на указанный интервал Возможные значения для: DATE - DAY, WEEK, MONTH, QUARTER, YEAR DATETIME - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR TIME - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR TIMESTAMP - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY

SQL

SELECT 
-- DATE
DATE_ADD(DATE "2022-08-27", INTERVAL 3 DAY) AS three_days,
DATE_ADD(DATE "2022-08-27", INTERVAL 1 WEEK) AS one_week,
-- DATETIME
DATETIME_ADD(DATETIME "2022-08-30 20:22:11", INTERVAL 20 MINUTE) as min_later,
-- TIME
TIME_ADD(TIME "20:30:00", INTERVAL 1 HOUR) as h_later,
-- TIMESTAMP
TIMESTAMP_ADD(TIMESTAMP "2022-08-30 22:10:10+00", INTERVAL 2 DAY) AS d_later

DATE_SUB | DATETIME_SUB | TIME_SUB | TIMESTAMP_SUB Возвращает дату, время , уменьшая ее на указанный интервал Возможные значения для: DATE - DAY, WEEK, MONTH, QUARTER, YEAR DATETIME - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR TIME - MICROSECOND, MILLISECOND, SECOND, MINUTE ,HOUR TIMESTAMP - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY

SQL

SELECT 
-- DATE
DATE_SUB(DATE "2022-08-27", INTERVAL 3 DAY) AS three_days,
DATE_SUB(DATE "2022-08-27", INTERVAL 1 WEEK) AS one_week,
-- DATETIME
DATETIME_SUB(DATETIME "2022-08-30 20:22:11", INTERVAL 20 MINUTE) as min_earlier,
-- TIME
TIME_SUB(TIME "20:30:00", INTERVAL 1 HOUR) as h_earlier,
-- TIMESTAMP
TIMESTAMP_SUB(TIMESTAMP "2022-08-30 22:10:10+00", INTERVAL 2 DAY) AS d_earlier

DATE_DIFF| DATETIME_DIFF | TIME_DIFF | TIMESTAMP_DIFF Возвращает разницу между двумя датами в зависимости от указанного значения. Возможные значения для: DATE - DAY, WEEK , WEEK(<WEEKDAY>), MONTH, QUARTER, YEAR ,ISOWEEK, ISOYEAR DATETIME - MICROSECOND, MILLISECOND, SECOND ,MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK, WEEK(<WEEKDAY>), MONTH, QUARTER, YEAR, DATE, TIME, ISOWEEK, ISOYEAR TIME - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR TIMESTAMP - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY

SQL

SELECT 
-- DATE
DATE_DIFF(DATE '2022-08-30', DATE '2022-08-25', DAY) AS diff_d,
-- DATETIME
DATETIME_DIFF(DATETIME "2022-08-30 15:30:00", DATETIME "2022-07-05 22:45:15", DAY) as diff_dt,
-- TIME
TIME_DIFF(TIME "20:30:00", TIME "19:50:00", MINUTE) as diff_time,
-- TIMESTAMP
TIMESTAMP_DIFF(TIMESTAMP "2022-08-30 11:30:00+00", TIMESTAMP "2022-08-29 20:40:00+00", HOUR) AS diff_tms

DATE_TRUNC | DATETIME_TRUNC | TIME_TRUNC | TIMESTAMP_TRUNC Округление даты и времени до ближайшего значения Возможные значения для: DATE - DAY, WEEK, WEEK(WEEKDAY), MONTH, QUARTER, YEAR, ISOWEEK, ISOYEAR DATETIME - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK , DAY, DAYOFYEAR, WEEK, WEEK(<WEEKDAY>), MONTH, QUARTER, YEAR, DATE, TIME, ISOWEEK, ISOYEAR TIME - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR TIMESTAMP - MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAYOFWEEK, DAY, DAYOFYEAR, WEEK, WEEK(<WEEKDAY>), MONTH, QUARTER, YEAR, TIME, DATE, DATETIME, ISOWEEK, ISOYEAR

SQL

SELECT 
-- DATE
DATE_TRUNC(DATE '2022-08-30', MONTH) AS trunc_d,
DATE_TRUNC(DATE '2022-08-30', WEEK(WEDNESDAY)) AS trunc_dww,
-- DATETIME
DATETIME_TRUNC(DATETIME "2022-08-30 12:50:00", DAY) as trunc_dt,
-- TIME
TIME_TRUNC(TIME "07:45:33", HOUR) as trunc_th,
-- TIMESTAMP
TIMESTAMP_TRUNC(TIMESTAMP "2022-08-30 05:10:00+00", DAY, "UTC") AS trunc_utc

FORMAT_(DATE | DATETIME | TIME | TIMESTAMP ) Изменяет формат отображения даты, времени Подробнее о вариантах отображения даты и времени в справке от google . Можно совмещать разные варианты отображения.

SQL

SELECT
-- DATE
  FORMAT_DATE("%D", DATE "2022-08-30") AS f_date,
  FORMAT_DATE("%b %d %Y", DATE "2022-09-01") AS f_date1,
-- DATETIME
  FORMAT_DATETIME("%c", DATETIME "2022-09-02 12:10:30") AS f_dt,
-- TIME
  FORMAT_TIME("%R", TIME "20:10:10") AS f_time,
-- TIMESTAMP
  FORMAT_TIMESTAMP("%F %R", TIMESTAMP "2022-08-31 18:45:00+00", "UTC") AS f_tms

PARCE_(DATE | DATETIME | TIME | TIMESTAMP) Преобразует дату написанную в формате STRING в формат DATE и т.д. Отличие от FORMAT_ в том, что изменяется поле с типом DATE, DATETIME, TIME, TIMESTAMP, а PARCE_ приводит в единый формат и нужную структуру отображения.

SQL

SELECT
-- DATE
  PARSE_DATE("%x", "08/31/22") AS p_date,
  PARSE_DATE("%Y%m%d", "20220830") AS p_date1,
-- DATETIME
  PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/31/2022 5:30:55 pm') AS p_dt,
-- TIME
  PARSE_TIME("%H", "20") AS p_t,
-- TIMESTAMP 
  PARSE_TIMESTAMP("%c", "Wed Aug 31 10:40:33 2022") AS p_tms

TIMESTAMP_MICROS Превращает timestamp (1606921983931087) в понятное отображение даты и времени (2020-12-02 15:13:03.931087 UTC) Синтаксис

SQL

TIMESTAMP_MICROS(event_timestamp) AS good_time