ОБСУДИТЬ АНАЛИТИКУ
Перезвоните мне
+7 (499) 350-20-68

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

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

  • Агрегатные функции
    • Статистические агрегатные функции
    • Приблизительные агрегатные функции
  • Функции ранжирования (аналитические)
  • Функции преобразования
  • Математические функции
  • Навигационные функции (смещения)
  • Хэш функции
  • Строковые функции
  • JSON функции
  • Функции массива
  • Функции даты и времени
    • date
    • time
    • datetime
    • timestamp
  • Интервальные функции
  • Географические функции
Нужно понимать различие агрегационных функций от оконных. Подробнее об оконных функциях в нашей статье

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

COUNT

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

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 (*) - вернет количество всех записей в таблице
Описание изображения 

SELECT
  COUNT(*) AS count_table
FROM
  `business-metric.project.new_table1`
COUNT (DISTINCT(название столбца)) - количество уникальных строк в столбце

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

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

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(название столбца)) - считаются только неповторяющиеся значения

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


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

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

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

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

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

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

SELECT
  SUM(conversions) AS sum_conv
FROM
  `business-metric.project.new_table1`
SUM (DISTINCT(название столбца)) - суммируются только уникальные значения

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

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

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

Например, есть такая таблица с цифрами в случайном порядке, и мы хотим присвоить каждой строке/цифре свой порядковый номер
Описание изображения 

SELECT num,
  RANK() OVER (ORDER BY num ASC) AS rank
FROM Numbers
Для примера рассмотрим задачу, найти первого человека из определенной группы совершившего конверсию/покупку
Нам нужно разбить пользователей на две группы (PARTITION BY):
  • источник 1 = google
  • источник 2 = yandex
После чего сортировать по времени покупки от меньшего к большему (ORDER BY ASC)

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 в том, что увеличение происходит на единицу и не зависит от самого числа.

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

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

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

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

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


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

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

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


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

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

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 (название столбца, количество знаков после запятой/точки)
Например, есть таблица
Округлим значение до одного знака после точки

SELECT
  name,ROUND(coef,1) as r_coef
FROM
  `business-metric.project.new_table`
Если мы хотим округлить значение, например, среднего значения, то просто обворачиваем функцию AVG в скобки:

SELECT
  ROUND(AVG(conversions),1) AS avg_conv
FROM
  `business-metric.project.new_table1`
Если хотим посчитать не повторяющиеся средние значения, то:

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

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

FIRST_VALUE и LAST_VALUE

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

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

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

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

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 — вычисляет определенный процентиль для отсортированных значений в наборе данных. В качестве параметра принимает процентиль, который необходимо вычислить.

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 (название столбца)

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

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

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

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

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

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

SELECT CONCAT('google',' / ', 'cpc') as source_medium
Например, есть таблица с столбцами источник и канал

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

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

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

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

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

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

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

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


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

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


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

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

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

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

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

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

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

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. Можно совмещать разные варианты отображения.

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_ приводит в единый формат и нужную структуру отображения.

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)
Синтаксис 


TIMESTAMP_MICROS(event_timestamp) AS good_time
Описание изображения
Made on
Tilda