В последнее время я обнаружил, что часто использую ряд оконных функций в BigQuery. Это действительно удобные функции, но их сложно осмыслить. В этом посте я расскажу вам об основных принципах оконных функций, а затем в последующих постах я поделюсь некоторыми примерами того, как вы можете использовать их с данными Google Analytics.
Оконные функции SQL позволяют запрашивать либо подмножество, либо полный набор строк в наборе данных и возвращать значение в каждой строке результатов. Вы можете использовать их для расчета промежуточных итогов, скользящих средних или просто для удаления повторяющихся строк из набора данных.
Есть много типов функций, которые можно использовать с окном, и они делятся на три основные категории:
- Нумерация: RANK, DENSE_RANK, ROW_NUMBER
- Агрегирование: SUM, AVG, COUNT, MIN, MAX.
- Навигация: LEAD, LAG, FIRST_VALUE, LAST_VALUE
Оконная функция следует этому общему формату, хотя вам не обязательно использовать каждое предложение в каждом операторе:
1. Имя_функции: это выбранная вами аналитическая функция, например SUM, RANK, LEAD.
2. Выражение: это запрашиваемый столбец или может быть логическим оператором CASE WHEN. Он оставлен пустым для некоторых функций нумерации, таких как RANK и ROW_NUMBER.
3. OVER: определяет окно или набор строк, в которых будет работать функция. Если вы оставите поле пустым между скобками, он запросит все строки в наборе данных.
4. PARTION BY: Это предложение по сути похоже на группу по. Если вы разделите по fullvisitorid , функция будет запрашивать все строки для каждого пользователя за раз. Если вы разделите на fullvisitorid и visitstarttime , функция будет запрашивать все строки в каждом сеансе для каждого пользователя.
5. ORDER BY: Иногда вам нужно будет упорядочить строки в вашем разделе. Это важно для функций навигации и нумерации, чтобы запрос знал, где начать и где закончить. Вы можете упорядочить по нескольким столбцам и в порядке возрастания (ASC) или убывания (DESC).
6. Frame_clause: этот раздел позволяет вам выбрать подмножество строк в вашем разделе. Если вы хотите запросить промежуточную сумму или скользящее среднее, укажите здесь.
Все еще со мной? Вот несколько простых примеров, чтобы проиллюстрировать эти функции в действии.
Пример 1. Найдите общий объем продаж по всему каталогу.
Если вы хотите запросить все строки в наборе данных, все, что вам нужно сделать, это опустить PARTITION BY / ORDER BY и оставить между скобками предложения OVER пустым (). В приведенном ниже примере суммируются продажи для всех продуктов в наборе данных.
Пример 2 – Найдите общий объем продаж в каждой категории
Запрос похож на функцию GROUP BY, за исключением того, что на этот раз вы получаете агрегированную сумму по каждой строке набора данных. В приведенном ниже отчете суммируются продажи по каждой категории.
Пример 3. Найдите общий объем продаж в каждой подкатегории.
Вы можете разделить по нескольким столбцам с помощью простой запятой между ними, и он будет суммировать все значения в каждой комбинации разделенных столбцов, в данном случае каждой комбинации категорий и подкатегорий.
Теперь, когда у вас есть итоги / промежуточные итоги в каждой строке, вы можете выполнять вычисления по столбцам. Например, какой процент от общего объема продаж приходится на каждый продукт.
Пример 4. Найдите самые продаваемые товары в каждой категории.
Еще одна оконная функция, которую вы можете использовать, – RANK. Для этого требуется предложение ORDER BY. В приведенном ниже примере каждый продукт в каждой категории ранжируется от самых высоких до самых низких (ORDER BY Sales DESC).
Рейтинг может быть полезен для ограничения ваших результатов до X лучших продуктов.
Пример 5 – Найдите промежуточную сумму
Предложение кадра следует этому формату: ROWS BETWEEN X И Y (где X – начальная точка, а Y – конечная точка окна).
Вы можете либо указать заданное количество строк (например, 5 ПРЕДЫДУЩИХ), либо использовать НЕОГРАНИЧЕННЫЙ, что означает неограниченное количество строк. Если вы хотите запросить полный набор строк, нужно указать UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, что означает просто все строки до и после текущей строки.
Ниже приведен пример промежуточной суммы. В этом примере я использовал ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, потому что я хочу добавить все продажи которые были до текущей строки. На 3 августа текущая сумма в 25 400 долларов является результатом сложения 9 000 + 15 000 долларов ( UNBOUNDED PRECEDING ) и 1400 долларов (CURRENT ROW).
Предложение frame дает вам гибкость в зависимости от вашего варианта использования. Вы можете добавить продажи из текущей строки и следующей строки, используя ROWS BETWEEN CURRENT ROW И 1 FOLLOWING . В качестве альтернативы, если вы хотите добавить 5 строк до и после текущей строки, ваш оператор будет выглядеть так: ROWS BETWEEN 5 PRECEDING и 5 FOLLOWING.
Пример 6 – Скользящее среднее
Ниже приведен пример использования предложения кадра для расчета скользящего среднего за 3 дня продаж. Функция изменяется на AVG (вместо SUM), а предложение кадра смотрит на СТРОКИ МЕЖДУ 2 ПРЕДЫДУЩИМИ И ТЕКУЩИМИ СТРОКАМИ.
Примечание: первые две строки на самом деле не являются средним значением за 3 дня, потому что в наборе данных нет двух предыдущих строк. Еще одна вещь, о которой следует помнить, – это количество строк в ваших разделах, потому что это может привести к неожиданным результатам, если они будут неравномерными. Например, если у вас была дата без продаж, в наборе данных может отсутствовать строка. Когда оператор просматривает 2 строки назад, он фактически просматривает 3 даты, в результате чего получается 4-дневное скользящее среднее.
Итак, это основные принципы оконных функций. Вы можете найти полный список совместимых функций, которые могут иметь предложение OVER в BigQuery, на этих страницах ( Нумерация , Агрегирование , Навигация ).
Подписывайтесь на наш блог и не упускайте краткие инструкции и обновления BigQuery
