Стандартный SQL в Google BigQuery

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

В 2016 году Google BigQuery представил новый способ взаимодействия с таблицами: стандартный SQL. До этого у BigQuery был собственный язык структурированных запросов под названием BigQuery SQL (теперь он называется Legacy SQL).

На первый взгляд, между Legacy и Standard SQL нет большой разницы: имена таблиц записываются немного по-другому; Стандарт имеет несколько более строгие требования к грамматике (например, вы не можете ставить запятую перед FROM) и больше типов данных. Но если вы присмотритесь, есть несколько незначительных изменений синтаксиса, которые дают маркетологам много преимуществ.

В OWOX мы решили уточнить ответы на следующие вопросы:

Каковы преимущества стандартного SQL перед устаревшим SQL?
Каковы возможности стандартного SQL и как он используется?
Как я могу перейти с устаревшего на стандартный SQL?
С какими еще службами, функциями синтаксиса, операторами и функциями совместим Standard SQL?
Как я могу использовать SQL-запросы для маркетинговых отчетов?

Каковы преимущества стандартного SQL перед устаревшим SQL?

Новые типы данных: массивы и вложенные поля

Стандартный SQL поддерживает новые типы данных: ARRAY и STRUCT (массивы и вложенные поля). Это означает, что в BigQuery стало проще работать с таблицами, загруженными из файлов JSON / Avro, которые часто содержат многоуровневые вложения.

Вложенное поле - это мини-таблица внутри большей:

На диаграмме выше синие и желтые полосы - это линии, в которые встроены мини-таблицы. Каждая строка - это один сеанс. Сессии имеют общие параметры: дату, идентификационный номер, категорию пользовательского устройства, браузер, операционную систему и т. Д. Помимо общих параметров для каждого сеанса, к строке прикрепляется таблица совпадений.

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

Если пользователь размещает заказ на сайте, информация о заказе также будет занесена в таблицу обращений:

transactionId (номер, идентифицирующий транзакцию)
transactionRevenue (общая стоимость заказа)
transactionShipping (стоимость доставки)

Таблицы данных сеанса, собранные с помощью OWOX BI, имеют аналогичную структуру.

Предположим, вы хотите узнать количество заказов от пользователей в Нью-Йорке за последний месяц. Чтобы узнать это, вам нужно обратиться к таблице совпадений и подсчитать количество уникальных идентификаторов транзакций. Для извлечения данных из таких таблиц в Standard SQL есть функция UNNEST:

#standardSQL 
SELECT 
COUNT (DISTINCT hits.transaction.transactionId) -- count the number of unique order numbers; DISTINCT helps to avoid duplication
FROM `project_name.dataset_name.owoxbi_sessions_*` -- refer to the table group (wildcard tables)
WHERE 
  (
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(),   INTERVAL 1 MONTHS)) -- if we don’t know which dates we need, it’s better to use the function FORMAT_DATE INTERVAL 
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) 
  ) 
AND geoNetwork.city = ‘New York’ -- choose orders made in New York City

Если информация о заказе была записана в отдельной таблице, а не во вложенной таблице, вам придется использовать JOIN, чтобы объединить таблицу с информацией о заказе и таблицу с данными сеанса, чтобы узнать, в каких сеансах были сделаны заказы.

Дополнительные параметры подзапроса

Если вам нужно извлечь данные из многоуровневых вложенных полей, вы можете добавить подзапросы с помощью SELECT и WHERE. Например, в таблицах потоковой передачи сеансов OWOX BI другая подтаблица, product, записывается в подтаблицу обращений. Подтаблица продуктов собирает данные о продуктах, которые передаются с помощью массива расширенной электронной торговли. Если на сайте настроена расширенная электронная торговля и пользователь просмотрел страницу продукта, характеристики этого продукта будут записаны в подтаблице продукта.

Чтобы получить эти характеристики продукта, вам понадобится подзапрос внутри основного запроса. Для каждой характеристики продукта в круглых скобках создается отдельный подзапрос SELECT:

SELECT 
  column_name1, -- list the other columns you want to receive
  column_name2,
  (SELECT productBrand FROM UNNEST(hits.product)) AS    hits_product_productBrand,
  (SELECT productRevenue FROM UNNEST(hits.product)) AS hits_product_productRevenue, -- list product features
  (SELECT localProductRevenue FROM UNNEST(hits.product)) AS hits_product_localProductRevenue,
  (SELECT productPrice FROM UNNEST(hits.product)) AS hits_product_productPrice,
FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`

Благодаря возможностям стандартного SQL проще создавать логику запросов и писать код. Для сравнения, в Legacy SQL вам нужно будет написать такой тип лестницы:

SELECT 
  column_name1,
  column_name2, 
  column_name3 
FROM (
  SELECT table_name.some_column AS column1…
  FROM table_name
)

Запросы к внешним источникам

Используя стандартный SQL, вы можете получить доступ к таблицам BigQuery непосредственно из Google Bigtable, Google Cloud Storage, Google Диска и Google Sheets.
То есть, вместо загрузки всей таблицы в BigQuery, вы можете удалить данные одним запросом. , выберите нужные вам параметры и загрузите их в облачное хранилище.

Дополнительные пользовательские функции (UDF)

Если вам нужно использовать формулу, которая не задокументирована, вам помогут определяемые пользователем функции (UDF). В нашей практике такое случается редко, поскольку документация Standard SQL охватывает практически все задачи цифровой аналитики.

В стандартном SQL пользовательские функции могут быть написаны на SQL или JavaScript; Устаревший SQL поддерживает только JavaScript. Аргументами этих функций являются столбцы, а принимаемые ими значения - результат манипулирования столбцами. В стандартном SQL функции могут быть написаны в том же окне, что и запросы.

Дополнительные условия JOIN

В устаревшем SQL условия JOIN могут основываться на равенстве или именах столбцов. В дополнение к этим параметрам диалект Standard SQL поддерживает JOIN по неравенству и по произвольному выражению.

Например, для выявления недобросовестных партнеров CPA мы можем выбрать сеансы, в которых источник был заменен в течение 60 секунд после транзакции. Чтобы сделать это в стандартном SQL, мы можем добавить неравенство к условию JOIN:

#standardSQL
SELECT *
FROM 
  (
  SELECT
  traff.clientId AS clientId,
  traff.page_path AS pagePath,
  traff.traffic_source AS startSource,
  traff.traffic_medium AS startMedium,
  traff.time AS startTime,
  aff.evAction AS evAction,
  aff.evSource AS finishSource,
  aff.evMedium AS finishMedium,
  aff.evCampaign AS finishCampaign,
  aff.time AS finishTime,
  aff.isTransaction AS isTransaction,
  aff.pagePath AS link,
  traff.time-aff.time AS diff
  FROM
    (
    SELECT 
    fullVisitorID AS clientId,
    h.page.pagePath AS page_path,
    trafficSource.source AS traffic_source,
    trafficSource.medium AS traffic_medium,
    trafficSource.campaign AS traffic_campaign,
    date,
    SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time
      FROM `demoproject.google_analytics_sample.ga_sessions_20190301`,
      UNNEST (hits) AS h
      WHERE trafficSource.medium != 'cpa'
      ) AS traff
JOIN (
  SELECT 
  total.date date,
  total.time time,
  total.clientId AS clientId,
  total.eventAction AS evAction,
  total.source AS evSource,
  total.medium AS evMedium,
  total.campaign AS evCampaign,
  tr.eventAction AS isTransaction,
  total.page_path AS pagePath
  FROM 
  (
  SELECT 
  fullVisitorID AS clientId,
  h.page.pagePath AS page_path,
  h.eventInfo.eventAction AS eventAction, 
  trafficSource.source AS source,
  trafficSource.medium AS medium,
  trafficSource.campaign AS campaign,
  date,
  SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time
  FROM `demoproject.google_analytics_sample.ga_sessions_20190301`,
  UNNEST(hits) AS h
    WHERE
    trafficSource.medium ='cpa'
    ) AS total
LEFT JOIN 
  (
  SELECT
  fullVisitorID AS clientId,
  date,
  h.eventInfo.eventAction AS eventAction,
  h.page.pagePath pagePath,
  SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time
  FROM `demoproject.google_analytics_sample.ga_sessions_20190301`,
  UNNEST(hits) AS h
  WHERE h.eventInfo.eventAction = 'typ_page'
  AND h.type = 'EVENT'
  GROUP BY 1, 2, 3, 4, 5
  ) AS tr
ON total.clientId=tr.clientId
AND total.date=tr.date
AND tr.time>total.time -- JOIN tables by inequality. Pass the additional WHERE clause that was needed in Legacy SQL
WHERE tr.eventAction = 'typ_page'
  ) AS aff
ON traff.clientId = aff.clientId
)
WHERE diff> -60
AND diff<0
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
  ORDER BY clientId, finishTime

Единственное ограничение стандартного SQL в отношении JOIN состоит в том, что он не допускает полусоединения с подзапросами в форме WHERE column IN (SELECT…):

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE -- such a construction cannot be used in Standard SQL
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

Меньше шансов на ошибку

Некоторые функции в устаревшем SQL возвращают NULL, если условие неверно. Например, если в ваши вычисления закралось деление на ноль, запрос будет выполнен, и в результирующих строках таблицы появятся записи NULL. Это может скрыть проблемы в запросе или в данных.

Логика стандартного SQL более проста. Если условие или входные данные неверны, запрос выдаст ошибку, например «деление на ноль», чтобы вы могли быстро исправить запрос. В стандартный SQL встроены следующие проверки:

Допустимые значения для +, -, ×, SUM, AVG, STDEV
Деление на ноль

Запросы выполняются быстрее

Запросы JOIN, написанные на стандартном SQL, быстрее, чем запросы, написанные на устаревшем SQL, благодаря предварительной фильтрации входящих данных. Сначала запрос выбирает строки, соответствующие условиям JOIN, а затем обрабатывает их.
В будущем Google BigQuery будет работать над повышением скорости и производительности запросов только для стандартного SQL.

Таблицы можно редактировать: вставлять и удалять строки, обновлять

Функции языка манипулирования данными (DML) доступны в стандартном SQL. Это означает, что вы можете обновлять таблицы и добавлять или удалять из них строки через то же окно, в котором вы пишете запросы. Например, с помощью DML вы можете объединить данные из двух таблиц в одну:

#standardSQL
MERGE dataset.Inventory AS T
USING dataset.NewArrivals AS S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
  UPDATE SET quantity = T.quantity + S.quantity
WHEN NOT MATCHED THEN
  INSERT (ProductID, quantity) VALUES (ProductID, quantity)

Код легче читать и редактировать

С помощью стандартного SQL сложные запросы можно запускать не только с помощью SELECT, но и с помощью WITH, что упрощает чтение, комментирование и понимание кода. Это также означает, что легче предотвращать собственные и исправлять чужие ошибки.

#standardSQL
WITH total_1 AS ( -- the first subquery in which the intermediate indicator will be calculated
    SELECT
        id,
        metric1,
       SUM(metric2) AS total_sum1
    FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`
    GROUP BY
        id, metric
),
total_2 AS ( -- the second subquery
    SELECT
        id,
        metric1,
        SUM(metric2) AS total_sum2
    FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`
           GROUP BY
        id, metric1
),
total_3 AS ( -- the third subquery
    SELECT
        id,
        metric,
       SUM(metric2) AS total_sum3
       FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`
    GROUP BY
        id, metric
)
SELECT *,
ROUND(100*( total_2.total_sum2 - total_3.total_sum3) / total_3.total_sum3, 3) AS difference -- get the difference index: subtract the value of the second subquery from the value of the third; divide by the value of the third 
FROM total_1
ORDER  BY 1, 2

С оператором WITH удобно работать, если у вас есть расчеты, которые выполняются в несколько этапов. Сначала вы можете собрать промежуточные метрики в подзапросах, а затем провести окончательные расчеты.

Облачная платформа Google (GCP), в которую входит BigQuery, представляет собой платформу полного цикла для работы с большими данными, от организации хранилища данных или облака данных до проведения научных экспериментов, а также прогнозной и предписывающей аналитики. С введением стандартного SQL BigQuery расширяет свою аудиторию. Работа с GCP становится все более интересной для маркетологов, продуктовых аналитиков, специалистов по данным и групп других специалистов.

Возможности Standard SQL и примеры использования

В OWOX BI мы часто работаем с таблицами, составленными с помощью стандартного экспорта Google Analytics 360 в Google BigQuery или OWOX BI Pipeline. В приведенных ниже примерах мы рассмотрим особенности SQL-запросов для таких данных.

1. Выберите данные для временного интервала.

В Google BigQuery данные о поведении пользователей на вашем сайте хранятся в таблицах с подстановочными знаками (таблицы со звездочкой); на каждый день формируется отдельная таблица. У этих таблиц одно и то же имя: отличается только суффикс. Суффикс - это дата в формате ГГГГММДД. Например, таблица owoxbi_sessions_20190301 содержит данные о сессиях на 1 марта 2019 года.

Мы можем напрямую обратиться к группе таких таблиц в одном запросе, чтобы получить данные, например, с 1 по 28 февраля 2019 г. Для этого нам нужно заменить ГГГГММДД на * в FROM, а в WHERE, нам нужно указать суффиксы таблицы для начала и конца временного интервала:

#standardSQL
SELECT sessionId, 
FROM `project_name.dataset_name.owoxbi_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN �' AND �'

Конкретные даты, за которые мы хотим собирать данные, нам не всегда известны. Например, каждую неделю нам может понадобиться анализировать данные за последние три месяца. Для этого мы можем использовать функцию FORMAT_DATE:

#standardSQL
SELECT
 <enumerate field names>
FROM `project_name.dataset_name.owoxbi_sessions_*`
WHERE 
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTHS))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

После BETWEEN мы записываем суффикс первой таблицы. Фраза CURRENT_DATE (), INTERVAL 3 MONTHS означает «выбрать данные за последние 3 месяца с текущей даты». Второй суффикс таблицы форматируется после AND. Конец интервала нужно отметить как вчера: CURRENT_DATE (), INTERVAL 1 DAY.

2. Получение пользовательских параметров и индикаторов.

Пользовательские параметры и показатели в таблицах экспорта Google Analytics записываются во вложенную таблицу обращений и во вложенные таблицы customDimensions и customMetrics. Все специальные параметры записываются в два столбца: один для количества параметров, собранных на сайте, второй для их значений. Вот как выглядят все параметры, передаваемые одним обращением:

Чтобы распаковать их и записать необходимые параметры в отдельные столбцы, воспользуемся следующим SQL-запросом:

-- Custom Dimensions (in the line below index - the number of the user variable, which is set in the Google Analytics interface; dimension1 is the name of the custom parameter, which you can change as you like. For each subsequent parameter, you need to write the same line:
  (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1, 
-- Custom Metrics: the index below is the number of the user metric specified in the Google Analytics interface; metric1 is the name of the metric, which you can change as you like. For each of the following metrics, you need to write the same line: 
  (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metric1

Вот как это выглядит в запросе:

#standardSQL
SELECT <column name1>,
<column_name2>, -- list column names
(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS page_type,
(SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS visitor_type, -- produce the necessary custom dimensions
(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metric1 -- produce the necessary custom metrics
<column_name3> -- if you need more columns, continue to list
FROM `project_name.dataset_name.owoxbi_sessions_20190201`

На скриншоте ниже мы выбрали параметры 1 и 2 из демонстрационных данных Google Analytics 360 в Google BigQuery и назвали их page_type и client_id. Каждый параметр записывается в отдельную колонку:

3. Рассчитайте количество сеансов по источнику трафика, каналу, кампании, городу и категории устройства.

Такие расчеты полезны, если вы планируете визуализировать данные в Google Data Studio и фильтровать их по городу и категории устройства. Это легко сделать с помощью оконной функции COUNT:

#standardSQL
SELECT
<column_name 1>, -- choose any columns 
COUNT (DISTINCT sessionId) AS total_sessions, -- summarize the session IDs to find the total number of sessions
COUNT(DISTINCT sessionId) OVER(PARTITION BY date, geoNetwork.city, session.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign) AS part_sessions -- summarize the number of sessions by campaign, channel, traffic source, city, and device category
FROM `project_name.dataset_name.owoxbi_sessions_20190201`

4. Объедините одинаковые данные из нескольких таблиц.

Предположим, вы собираете данные о выполненных заказах в нескольких таблицах BigQuery: одна собирает все заказы из магазина A, другая собирает заказы из магазина B. Вы хотите объединить их в одну таблицу со следующими столбцами:

client_id - число, идентифицирующее уникального покупателя
Transaction_created - время создания заявки в формате TIMESTAMP
transaction_id - номер заказа
is_approved - подтвержден ли заказ
transaction_revenue - сумма заказа

В нашем примере заказы с 1 января 2018 г. по вчерашний день должны быть в таблице. Для этого выберите соответствующие столбцы из каждой группы таблиц, присвойте им одно и то же имя и объедините результаты с помощью UNION ALL:

#standardSQL
SELECT 
cid AS client_id, 
order_time AS transaction_created,
order_status AS is_approved,
order_number AS transaction_id
FROM `project_name.dataset_name.table1_*`
WHERE (
  _TABLE_SUFFIX BETWEEN �'
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  )
UNION ALL 
SELECT
userId AS client_id,
created_timestamp AS transaction_created,
operator_mark AS  is_approved,
transactionId AS transaction_id
FROM `project_name.dataset_name.table1_*`
WHERE (
  _TABLE_SUFFIX BETWEEN �'
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  )
ORDER BY transaction_created DESC

5. Создайте словарь групп каналов трафика.

Когда данные поступают в Google Analytics, система автоматически определяет группу, к которой принадлежит конкретный переход: прямой, обычный поиск, платный поиск и т. Д. Чтобы идентифицировать группу каналов, Google Analytics просматривает UTM-теги переходов, а именно utm_source и utm_medium. Вы можете узнать больше о группах каналов и правилах определения в справке Google Analytics.

Если клиенты OWOX BI хотят присвоить группам каналов свои имена, мы создаем словарь, переход которого принадлежит конкретному каналу. Для этого мы используем условный оператор CASE и функцию REGEXP_CONTAINS. Эта функция выбирает значения, в которых встречается указанное регулярное выражение.

Мы рекомендуем брать имена из вашего списка источников в Google Analytics. Вот пример того, как добавить такие условия в тело запроса:

#standardSQL
SELECT 
CASE 
WHEN (REGEXP_CONTAINS (source, 'yandex') AND medium = 'referral' THEN 'Organic Search' 
WHEN (REGEXP_CONTAINS (source, 'yandex.market')) AND medium = 'referral' THEN 'Referral'
WHEN (REGEXP_CONTAINS (source, '^(go.mail.ru|google.com)$') AND medium = 'referral') THEN 'Organic Search'
WHEN medium = 'organic' THEN 'Organic Search'
WHEN (medium = 'cpc') THEN 'Paid Search'
WHEN REGEXP_CONTAINS (medium, '^(sending|email|mail)$') THEN 'Email'
    WHEN REGEXP_CONTAINS (source, '(mail|email|Mail)') THEN 'Email'
    WHEN REGEXP_CONTAINS (medium, '^(cpa)$') THEN 'Affiliate'
    WHEN medium = 'social' THEN 'Social'
    WHEN source = '(direct)' THEN 'Direct'
 WHEN REGEXP_CONTAINS (medium, 'banner|cpm') THEN 'Display'
    ELSE 'Other'
  END channel_group -- the name of the column in which the channel groups are written
FROM `project_name.dataset_name.owoxbi_sessions_20190201`

Как перейти на стандартный SQL

Если вы еще не перешли на стандартный SQL, вы можете сделать это в любое время. Главное - не смешивать диалекты в одном запросе.

Вариант 1. Включите интерфейс Google BigQuery.

Устаревший SQL по умолчанию используется в старом интерфейсе BigQuery. Чтобы переключаться между диалектами, нажмите Показать параметры под полем ввода запроса и снимите флажок Использовать устаревший SQL рядом с диалектом SQL.

В новом интерфейсе по умолчанию используется стандартный SQL. Здесь вам нужно перейти на вкладку Еще, чтобы переключить диалекты:

Вариант 2. Пишите префикс в начале запроса.

Если вы не отметили настройки запроса, вы можете начать с желаемого префикса (#standardSQL или #legacySQL):

#standardSQL
SELECT
  weight_pounds, state, year, gestation_weeks
FROM
  `bigquery-public-data.samples.natality`
ORDER BY weight_pounds DESC
LIMIT 10;

В этом случае Google BigQuery проигнорирует настройки в интерфейсе и выполнит запрос, используя диалект, указанный в префиксе.

Если у вас есть представления или сохраненные запросы, которые запускаются по расписанию с помощью скрипта приложений, не забудьте изменить значение useLegacySql на false в скрипте:

var job = {
configuration: {
  query: {
    query: 'INSERT INTO MyDataSet.MyFooBarTable (Id, Foo, Date) VALUES (1, \'bar\', current_Date);',
    useLegacySql: false
    }

Вариант 3. Переход на стандартный SQL для представлений

Если вы работаете с Google BigQuery не с таблицами, а с представлениями, эти представления не будут доступны на стандартном диалекте SQL. То есть, если ваша презентация написана на устаревшем SQL, вы не можете писать к ней запросы на стандартном SQL.

Чтобы передать представление в стандартный SQL, необходимо вручную переписать запрос, по которому оно было создано. Проще всего это сделать через интерфейс BigQuery.

1. Откройте представление:

2. Щелкните Подробности. Должен открыться текст запроса, и ниже появится кнопка «Изменить запрос»:

Теперь вы можете редактировать запрос в соответствии с правилами Standard SQL.
Если вы планируете и дальше использовать запрос в качестве презентации, нажмите «Сохранить вид» после того, как закончите редактирование.

Совместимость, особенности синтаксиса, операторы, функции

Совместимость

Благодаря реализации Standard SQL вы можете напрямую обращаться к данным, хранящимся в других сервисах, прямо из BigQuery:

Файлы журнала Google Cloud Storage
Записи транзакций в Google Bigtable
Данные из других источников

Это позволяет использовать продукты Google Cloud Platform для любых аналитических задач, включая прогнозную и предписывающую аналитику на основе алгоритмов машинного обучения.

Синтаксис запроса

Структура запроса на диалекте Standard почти такая же, как и в Legacy:

Имена таблиц и представления разделены точкой (точка), а весь запрос заключен в серьезные акценты: `имя_проекта.data_name_name.table_name``bigquery-public-data.samples.natality`

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

Особенности синтаксиса стандартного SQL:

Запятые необходимы для перечисления полей в операторе SELECT.
Если вы используете оператор UNNEST после FROM, запятая или JOIN ставится перед UNNEST.
Перед FROM нельзя ставить запятую.
Запятая между двумя запросами равна CROSS JOIN, поэтому будьте осторожны.
JOIN может выполняться не только по столбцу или равенству, но и по произвольным выражениям и неравенствам.
Можно писать сложные подзапросы в любой части выражения SQL (в SELECT, FROM, WHERE и т. Д.). На практике пока невозможно использовать такие выражения, как WHERE column_name IN (SELECT…), как в других базах данных.

Операторы

В стандартном SQL операторы определяют тип данных. Например, массив всегда указывается в квадратных скобках []. Операторы используются для сравнения, сопоставления логических выражений (НЕ, ИЛИ, И) и в арифметических вычислениях.

Функции

Стандартный SQL поддерживает больше функций, чем Legacy: традиционное агрегирование (сумма, число, минимум, максимум); математические, строковые и статистические функции; и редкие форматы, такие как HyperLogLog ++.

В стандартном диалекте больше функций для работы с датами и TIMESTAMP. Полный список функций представлен в документации Google. Чаще всего используются функции для работы с датами, строками, агрегированием и окном.

1. Функции агрегирования

COUNT (DISTINCT имя_столбца) подсчитывает количество уникальных значений в столбце. Например, предположим, что нам нужно подсчитать количество сеансов с мобильных устройств на 1 марта 2019 г. Поскольку номер сеанса может повторяться в разных строках, мы хотим подсчитать только уникальные значения номера сеанса:

#standardSQL
SELECT 
COUNT (DISTINCT sessionId) AS sessions
FROM  `project_name.dataset_name.owoxbi_sessions_20190301`
WHERE device.deviceCategory = 'mobile'

SUM (column_name) - сумма значений в столбце

#standardSQL
SELECT 
SUM (hits.transaction.transactionRevenue) AS revenue
FROM  `project_name.dataset_name.owoxbi_sessions_20190301`,
UNNEST (hits) AS hits -- unpacking the nested field hits
WHERE device.deviceCategory = 'mobile'

MIN (имя_столбца) | MAX (column_name) - минимальное и максимальное значение в столбце. Эти функции удобны для проверки разброса данных в таблице.

2. Оконные (аналитические) функции

Аналитические функции рассматривают значения не для всей таблицы, а для определенного окна - набора строк, которые вас интересуют. То есть вы можете определять сегменты в таблице. Например, вы можете рассчитать СУММ (доход) не для всех линий, а для городов, категорий устройств и т. Д. Вы можете включить аналитические функции SUM, COUNT и AVG, а также другие функции агрегирования, добавив к ним условие OVER (PARTITION BY column_name).

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

SELECT
        date,
        geoNetwork.city,
        t.device.deviceCategory,
        trafficSource.source,
        trafficSource.medium,
        trafficSource.campaign,
COUNT(DISTINCT sessionId) OVER(PARTITION BY date, geoNetwork.city, session.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign) AS segmented_sessions
FROM  `project_name.dataset_name.owoxbi_sessions_20190301` t

OVER определяет окно, для которого будут производиться расчеты. PARTITION BY указывает, какие строки следует сгруппировать для расчета. В некоторых функциях необходимо указать порядок группировки с помощью ORDER BY.

Полный список оконных функций см. В документации BigQuery.

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

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

SUBSTR обрезает часть струны. В запросе эта функция записывается как SUBSTR (string_name, 0.4). Первое число указывает, сколько символов нужно пропустить с начала строки, а второе число указывает, сколько цифр нужно вырезать. Например, предположим, что у вас есть столбец даты, который содержит даты в формате STRING. В этом случае даты выглядят так: 20190103. Если вы хотите извлечь год из этой строки, SUBSTR поможет вам:

#standardSQL
SELECT
SUBSTR(date,0,4) AS year
FROM `project_name.dataset_name.owoxbi_sessions_20190301`

CONCAT (имя_столбца и т. Д.) Склеивает значения. Давайте воспользуемся столбцом даты из предыдущего примера. Предположим, вы хотите, чтобы все даты были записаны следующим образом: 2019–03–01. Чтобы преобразовать даты из текущего формата в этот формат, можно использовать две строковые функции: сначала вырезать необходимые части строки с помощью SUBSTR, а затем склеить их через дефис:

#standardSQL
SELECT
CONCAT(SUBSTR(date,0,4),"-",SUBSTR(date,5,2),"-",SUBSTR(date,7,2)) AS date
FROM `project_name.dataset_name.owoxbi_sessions_20190301`

REGEXP_CONTAINS возвращает значения столбцов, в которых встречается регулярное выражение:

#standardSQL
SELECT 
CASE
WHEN REGEXP_CONTAINS (medium, '^(sending|email|mail)$') THEN 'Email'
    WHEN REGEXP_CONTAINS (source, '(mail|email|Mail)') THEN 'Email'
    WHEN REGEXP_CONTAINS (medium, '^(cpa)$') THEN 'Affiliate'
ELSE 'Other'
END Channel_groups
FROM `project_name.dataset_name.owoxbi_sessions_20190301`

Эта функция может использоваться как в SELECT, так и в WHERE. Например, в WHERE вы можете выбрать с его помощью определенные страницы:

WHERE REGEXP_CONTAINS(hits.page.pagePath, 'land[123]/|/product-order')

4. Функции даты

Часто даты в таблицах записываются в формате STRING. Если вы планируете визуализировать результаты в Google Data Studio, даты в таблице необходимо преобразовать в формат DATE с помощью функции PARSE_DATE.

PARSE_DATE преобразует СТРОКУ формата 1900–01–01 в формат ДАТЫ.
Если даты в ваших таблицах выглядят иначе (например, 19000101 или 01_01_1900), вы должны сначала преобразовать их в указанный формат.

#standardSQL
SELECT 
PARSE_DATE('%Y-%m-%d', date)  AS date_new
FROM `project_name.dataset_name.owoxbi_sessions_20190301`

DATE_DIFF вычисляет, сколько времени прошло между двумя датами в днях, неделях, месяцах или годах. Это полезно, если вам нужно определить интервал между просмотром рекламы пользователем и размещением заказа. Вот как функция выглядит в запросе:

#standardSQL 
SELECT DATE_DIFF( 
PARSE_DATE('%Y%m%d', date1), PARSE_DATE('%Y%m%d', date2), DAY 
) days -- convert the date1 and date2 lines to the DATE format; choose units to show the difference (DAY, WEEK, MONTH, etc.)
FROM `project_name.dataset_name.owoxbi_sessions_20190301`

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

SQL-запросы для маркетинговых отчетов

Стандартный диалект SQL позволяет предприятиям извлекать максимум информации из данных с помощью глубокой сегментации, технического аудита, маркетингового анализа KPI и выявления недобросовестных подрядчиков в сетях CPA. Вот примеры бизнес-задач, в которых вам помогут SQL-запросы к данным, собранным в Google BigQuery.

1. Анализ ROPO: оцените вклад онлайн-кампаний в офлайн-продажи. Чтобы выполнить ROPO-анализ, вам необходимо объединить данные о поведении пользователей в Интернете с данными из вашей CRM, системы отслеживания звонков и мобильного приложения.

Если в одной и второй базе есть ключ - общий параметр, уникальный для каждого пользователя (например, User ID) - вы можете отслеживать:
какие пользователи заходили на сайт перед покупкой товаров в магазине
как пользователи вели себя на сайте;
сколько времени потребовалось пользователям, чтобы принять решение о покупке;
в каких кампаниях было больше всего офлайн-покупок.

2. Сегментируйте клиентов по любой комбинации параметров, от поведения на сайте (посещенные страницы, просмотренные продукты, количество посещений сайта до покупки) до номера карты лояльности и приобретенных товаров.

3. Узнайте, какие партнеры CPA работают недобросовестно и заменяют теги UTM.

4. Анализируйте продвижение пользователей по воронке продаж.

Мы подготовили подборку запросов на стандартном диалекте SQL. Если вы уже собираете данные со своего сайта, из рекламных источников и из своей CRM-системы в Google BigQuery, вы можете использовать эти шаблоны для решения своих бизнес-задач. Просто замените название проекта, набор данных и таблицу в BigQuery своими собственными. В коллекции вы получите 11 SQL-запросов.

Для данных, собранных с помощью стандартного экспорта из Google Analytics 360 в Google BigQuery:

Действия пользователя в контексте любых параметров
Статистика по ключевым действиям пользователей
Пользователи, которые просматривали страницы определенных продуктов
Действия пользователей, купивших тот или иной товар
Настройте воронку с любыми необходимыми шагами
Эффективность внутреннего поиска на сайте

Для данных, собранных в Google BigQuery с помощью OWOX BI:

Приписанное потребление по источнику и каналу
Средняя стоимость привлечения посетителя по городам
ROAS для валовой прибыли по источникам и каналам
Количество заказов в CRM по способам оплаты и способам доставки
Среднее время доставки по городам

Стандартный SQL в Google BigQuery