SQL лайфхаки в BigQuery, о которых вы точно не знали

3 min readOct 25, 2019

Список полезных возможностей в BigQuery, неочевидных даже для опытных пользователей:
(добавляйте в закладки - список будет обновляться)

1) Time travel

В BigQuery можно запрашивать содержание таблиц по состоянию на конкретное время. Если перезаписали или удалили часть нужных данных - не беда, все можно восстановить. Пример запроса количества записей в таблице сутками ранее:

2) EXCEPT

Оператор EXCEPT исключает поля таблицы из запрашиваемых данных. Это целесообразно, когда проще исключить несколько полей, чем перечислять все нужные:

3) `SAFE_DIVIDE`

Оператор SAFE_DIVIDE позволяет игнорировать деление на 0. К примеру SAFE_DIVIDE(X,Y)=x/y и если y=0, то оператор вернет null вместо ошибки. Эту же функцию выполняет префикс SAFE вместе с другими операторами, а именно SAFE.function_name() возвращает null вместо ошибки.

4) `SUM IF`

Есть оператор COUNTIF. Пример использования: COUNTIF(event='purchase') - подсчет количества определенных действий пользователя. Часто есть соблазн по аналогии использовать SUMIF, но такого оператора нет. Его функциональность можно заменить существующими операторами, например: SUM(IF(value > 2, value, 0).

5) Persistent UDF

User Defined Functions — кастомные функции JavaScript, добавляющие функциональности SQL. С недавних пор можно не декларировать функции при выполнении запроса, так как их можно сделать постоянно доступными или даже публичными. Ниже SQL-запрос с общедоступной функцией перевода текста в числа fhoffa.x.parse_number('text') :

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

6) BigQuery Scripting

BigQuery Scripting позволяет программировать на языке SQL прямо в BigQuery. Можно декларировать переменные, использовать циклы, условные операторы, программные модули, функции и процедуры. Есть отличная статья с полезными аналитику примерами.

7) Analytical functions

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

8) HyperLogLog++

Использование алгоритма HyperLogLog++ позволяет заменить некоторые стандартные функции агрегирования на аналогичные более быстрые, работающие с определенной погрешностью. Еще алгоритм позволяет довольно точно агрегировать уже агрегированные данные. Интерфейс Google Analytics использует эти алгоритмы для подсчета уникального количества пользователей, из-за чего данные в Google Analytics и BigQuery могут отличаться. Пример использования:

9) Public data

В BigQuery есть полезные таблицы в публичном доступе, которые могут помочь дополнить ваши данные. Например, есть множество способов подтянуть geo-данные по IP, но мало кто знает, что это можно сделать прямо в BigQuery, пример запроса:

Еще один простой пример добавления флага страны emoji к их названию для более удобной визуализации:

10) ORDINAL

Оператор ORDINAL возвращает элемент массива по его позиции, начиная с единицы. К примеру, рассмотрим SQL-запрос для визуализации распределения метрик по дням недели и времени суток:

Его можно переписать с использованием ORDINAL таким образом:

11) Transpose Rows to Column

Иногда данные записываются в таблицу в BigQuery в json-образной структуре. Их можно распарсить и транспонировать из строчек в колонки. Пример преобразования данных:

Ниже SQL-запрос, помогающий это сделать:

12) `RANGE_BUCKET`

Оператор RANGE_BUCKET сканирует указанный отсортированный массив и возвращает позицию минимального элемента массива, который больше указанного значения. Если значение больше или равно последнему элементу в массиве, возвращает длину массива. Это может быть полезно, если вам нужно сгруппировать данные для построения гистограмм, бизнес-правил и пр.

_____________

Если понравилась подборка, подписывайтесь на мой Телеграм-канал BigQuery Insights, где я делюсь интересными решениями аналитики в Google BigQuery.

_____________