Использование оконных функций в Google BigQuery

Оконные функции — это отличный инструмент аналитика, который с легкостью помогает решать множество задач.

Чтобы понять, что такое оконные функции давайте вспомним, что такое агрегатные функции. Они работают по принципу, считается некая функция по заданному полю sum, max, min и т.д. и при этом происходит группировка остальных полей. То есть агрегатная функция применяется ко всему возвращаемому результату. А в оконных функциях группировка не происходит, строки делятся на блоки и для каждого отдельно взятого блока работает своя группировка.
Оконные функции — это не то же самое, что GROUP BY. Они не уменьшают количество строк, а возвращают столько же значений, сколько получили на вход. Они не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней.
Партиции (окна из набора строк) - это набор строк, указанный для оконной функции по одному из столбцов или группе столбцов таблицы. Партиции для каждой оконной функции в запросе могут быть разделены по различным колонкам таблицы.

В чем заключается главное отличие оконных функций от функций агрегации с группировкой?
При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.

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

Синтаксис использования в BigQuery

Окно определяется с помощью обязательной инструкции OVER. О Partition у нас есть отдельная статья.


SELECT
Название функции (столбец для вычислений)
OVER (
     PARTITION BY столбец для группировки
     ORDER BY столбец для сортировки
     ROWS или RANGE выражение для ограничения строк в пределах группы
     )

Например, есть простая таблица, дата, источник и количество конверсий по этому источнику

Откроем окно при помощи OVER() и просуммируем столбец "Conversion":


select date, source,conversion, 
  sum(conversion) over() as Sum 
from `united-strategy-279207.Example.conversion`

Мы использовали функцию OVER без уточнения. В таком варианте окном будет весь набор данных без сортировки. Появился новый столбец "Sum" и для каждой строки выводится одно и тоже значение 40. Это сумма всех значений колонки "conversion"

Теперь применим инструкцию PARTITION BY, которая определяет столбец, по которому будет производиться группировка и является ключевой в разделении набора строк на окна, например по date:


select date, source,conversion, 
  sum(conversion) over(PARTITION BY date) as Sum 
from `united-strategy-279207.Example.conversion`

Инструкция PARTITION BY сгруппировала строки по полю "date". Теперь для каждой группы рассчитывается своя сумма значений столбца "Conversion".

Так же можем применить сортировку при помощи ORDER BY


select date, source,conversion, 
  sum(conversion) over(PARTITION BY date ORDER BY source) as Sum 
from `united-strategy-279207.Example.conversion`

Таким образом мы указали, что хотим видеть сумму не всех значений в окне, а для каждого значения "conversion" сумму со всеми предыдущими. То есть мы посчитали нарастающий итог. из первого блока 7+5, 12+2 и т.д.

Виды функций в BigQuery

Оконные функции можно разделить на следующие группы:

Агрегатные функции
Аналитические функции
Ранжирующие функции
Функции смещения

Большая статья по часто используемым функциям.
В одной инструкции SELECT с одним предложением FROM можно использовать сразу несколько оконных функций.

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

Агрегатные функции – это функции, которые выполняют на наборе данных арифметические вычисления.

SUM – возвращает сумму значений в столбце
COUNT — вычисляет количество значений в столбце (значения NULL не учитываются)
AVG — определяет среднее значение в столбце
MAX — определяет максимальное значение в столбце
MIN — определяет минимальное значение в столбце

Пример использования агрегатных функций с оконной инструкцией


select date, source,conversion, 
  SUM (conversion) OVER(PARTITION BY date ORDER BY source) as Sum,
  COUNT(conversion) OVER(PARTITION BY date) AS Count,
  AVG(conversion) OVER(PARTITION BY date) AS Avg,
  MAX(conversion) OVER(PARTITION BY date) AS Max,
  MIN(conversion) OVER(PARTITION BY date) AS Min
from `united-strategy-279207.Example.conversion`

Аналитические функции в BigQuery

Аналитические функции — это функции которые возвращают информацию о распределении данных и используются для статистического анализа.

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


select date, source,conversion, 
  CUME_DIST() OVER(PARTITION BY date ORDER BY conversion) AS cume_dist,
  PERCENT_RANK() OVER(PARTITION BY date ORDER BY conversion) AS percent_rank, 
  PERCENTILE_CONT(conversion, 0.5) OVER (PARTITION BY date) as percentile_cont,
  PERCENTILE_DISC(conversion, 0.5) OVER(PARTITION BY date) AS percentile_disc
from `united-strategy-279207.Example.conversion`

Ранжирующие функции в BigQuery

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

ROW_NUMBER – оконная функция, которая вычисляет последовательность ранг (порядковый номер) строк внутри партиции, независимо от того, есть ли в строках повторяющиеся значения или нет
RANK — оконная функция, которая вычисляет ранг каждой строки. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг.
DENSE_RANK — оконная функция, которая возвращает ранг каждой строки. Но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий;
NTILE – оконная функция, которая позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках.


select date, source,conversion, 
ROW_NUMBER() OVER(PARTITION BY date ORDER BY conversion) AS row_number,
RANK() OVER(PARTITION BY date ORDER BY conversion) AS rank,
DENSE_RANK() OVER(PARTITION BY date ORDER BY conversion) AS dense_rank,
NTILE(4) OVER(PARTITION BY date ORDER BY conversion) AS ntile
from `united-strategy-279207.Example.conversion`

Функции смещения в BigQuery
Функции смещения – это функции, которые позволяют перемещаться и обращаться к разным строкам в окне, относительно текущей строки, а также обращаться к значениям в начале или в конце окна.

LAG - оконная функция, которая возвращает предыдущее значение столбца по порядку сортировки.
LEAD - оконная функция, возвращающая следующее значение столбца по порядку сортировки.
FIRST_VALUE или LAST_VALUE — с помощью функции можно получить первое и последнее значение. В качестве параметра принимает столбец, значение которого необходимо вернуть.


select date, source,conversion, 
LAG(conversion) OVER(PARTITION BY date ORDER BY date) AS lag,
LEAD(conversion) OVER(PARTITION BY date ORDER BY date) AS lead, 
FIRST_VALUE(conversion) OVER(PARTITION BY date ORDER BY date) AS first_value, 
LAST_VALUE(conversion) OVER(PARTITION BY date ORDER BY date) AS last_value
from `united-strategy-279207.Example.conversion`

Пример построения модели атрибуции с помощью оконных функций в BigQuery

Для примера возьмем кейс с моделями атрибуции.
Благодаря модели атрибуции можно четко оценить вклад каждого канала в достижение конверсии.
У нас есть таблица с cid (Client_id), датами и количеством посещений и конверсиями:

Первый клик

В Google Analytics стандартной моделью атрибуции является последний непрямой клик. И в данном случае 100% ценности конверсии присваивается последнему каналу в цепочке взаимодействий.
Посчитаем модель по первому взаимодействию, когда 100% ценности конверсии присваивается первому каналу в цепочке при помощи функции FIRST_VALUE (функция смещения)


select date,cid,source_medium,
FIRST_VALUE(source_medium) OVER(PARTITION BY cid ORDER BY date ASC) AS first_click,
sessions,conversions
from `united-strategy-279207.Example.conversions`

Справа столбца source_medium появился новый столбец first_click, в котором указан источник/канал, который в первый раз привел к нам на сайт посетителя и вся ценность приписана данному каналу.

Произведем агрегацию чтобы получить отчет о источниках и конверсиях.


with first as 
(
select date,cid,source_medium,
FIRST_VALUE(source_medium) OVER(PARTITION BY cid ORDER BY date ASC) AS first_click,
sessions,conversions
from `united-strategy-279207.Example.conversions`
)
select first_click, sum (conversions) as conversions
from first 
group by first_click

Учет давности взаимодействий

Получается чем ближе к конверсии находится точка взаимодействия c каналом трафика, тем более ценной она считается. Попробуем рассчитать эту модель при помощи функции DENSE_RANK (ранжирующая функция)


select date,cid,source_medium,
-- Присваиваем ранг каждой строчке в партиции 
DENSE_RANK() OVER(PARTITION BY cid ORDER BY date ASC) AS rank,
sessions,conversions
from `united-strategy-279207.Example.conversions`

Справа появился новый столбец rank, в котором указан ранг каждой строки в зависимости от близости к дате конверсии.

Теперь чтобы распределить ценность равную 1 (100%) по всем касаниям каналов на пути к конверсии делим ранг определенной строки на сумму рангов конкретного пользователя и потом присваиваем ранг в зависимости от даты до конверсии


select date,cid,source_medium,
-- Делим ранг определенной строки на сумму рангов по пользователю
ROUND(CAST(DENSE_RANK() OVER(PARTITION BY cid ORDER BY date) AS float64) / CAST(SUM(rank) OVER(PARTITION BY cid) AS float64), 2) AS distribution,
sessions,conversions
from (
select date,cid,source_medium,
-- Присваиваем ранг каждой строчке в партиции 
DENSE_RANK() OVER(PARTITION BY cid ORDER BY date) AS rank,
sessions,conversions
from `united-strategy-279207.Example.conversions`)

Теперь распределение ценности обозначается десятичной дробью

И если сделать агрегацию, можно увидеть как распределилась ценность по каналам.


with rank as (
select date,cid,source_medium,
-- Делим ранг определенной строки на сумму рангов по пользователю
ROUND(CAST(DENSE_RANK() OVER(PARTITION BY cid ORDER BY date) AS float64) / CAST(SUM(rank) 
OVER(PARTITION BY cid) AS float64), 2) AS distribution,
sessions,conversions
from (
select date,cid,source_medium,
-- Присваиваем ранг каждой строчке в партиции 
DENSE_RANK() OVER(PARTITION BY cid ORDER BY date) AS rank,
sessions,conversions
from `united-strategy-279207.Example.conversions`
) table_rank
)
select source_medium, sum (distribution) as value, sum(conversions) as conversions
from rank 
group by source_medium
order by value desc

Данным запросом мы построили аналог модели атрибуции "Временной спад", чем ближе канал к конверсии тем больше у него коэффициент, далее в итоговой таблице, мы просуммировали эти коэффициенты, чтобы понять у какого канала наибольший вклад в рамках "Временного спада". При этом могут быть каналы без конверсии, но с высоким коэффициентом, так как они являются поддерживающими, как правило предпоследними на пути к конверсии!