Как использовать конструкцию SELECT FROM UNNEST для анализа параметров в повторяющихся записях Google BigQuery

В предыдущей статье я показал вам, как использовать функцию UNNEST в BigQuery для анализа параметров событий в данных Google Analytics для Firebase.

Мы использовали функцию UNNEST, потому что обычно параметры события хранятся как повторяющаяся запись (repeated record), которую вы можете рассматривать как массив, напоминающий JSON структуру. Вы можете разбить этот массив, и поместить каждый отдельный его элемент (параметр события) в новую строку, а затем скопировать исходную строку для каждого из этих отдельных параметров. Более понятно будет если посмотреть следующую анимацию:

Рассмотренный ранее пример подходит в тех случаях, когда вам необходимо проанализировать один из параметров событий. Но что делать, если нам необходимо проанализировать сразу несколько параметров события?

Например, давайте взглянем на наш образец игры Flood-it. Вот ссылка на общедоступный набор данных по этой игре, если вы хотите повторить описанные ниже манипуляции.

Если вы посмотрите на событие level_complete_quickplay, то найдёте два интересующие нас параметра: параметр value, который сообщает нам окончательный счет игры (то есть сколько ходов потребовалось пользователю для прохождения игры), и параметр board, в котором хранится размер игрового поля.

Параметр value (количество ходов которое потребовалось пользователю для прохождения игры) вероятно зависит от размера игрового поля (параметра board). Поэтому, перед тем, как приступить к расчёту каких то статистик, например расчёту среднего количества ходов, необходимых для прохождения игры, имеет смысл сгруппировать наши события по размеру игрового поля.

Если мы просто будем использовать функцию UNNEST:

SELECT event_name, event_timestamp, user_pseudo_id, param
FROM `firebase-public-project.analytics_153293282.events_20181003`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND (param.key = "value" OR param.key = "board")

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

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

SELECT 
  MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
  MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
FROM (
  SELECT event_name, event_timestamp, user_pseudo_id, param
  FROM `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST(event_params) AS param
  WHERE event_name = "level_complete_quickplay"
  AND (param.key = "value" OR param.key = "board")
) 
GROUP BY user_pseudo_id, event_timestamp

… Теперь мы можем рассчитать среднее количество ходов в зависимости от размера игрового поля.

Задачу мы решили, но такое решение мне кажется не эффективным. Неужели мы действительно собираемся разбить все наши параметры на отдельные строки только для того, чтобы через несколько мгновений снова объединить их вместе? Наверняка должен быть более изящный вариант решения.

SELECT FROM UNNEST в помощь!

К счастью, есть другой вариант решения, конструкция SELECT FROM UNNEST.

Используя конструкцию SELECT FROM UNNEST, вы говорите: «Я хочу применить функцию UNNEST к повторяющейся записи в ее собственной маленькой временной таблице. Далее выбрать одну строку из неё и поместить ее в наши результаты, так же как если бы это было любое другое значение ».

Объяснение приведённое выше звучит сложновато, давайте рассмотрим пример.

Первое, что я собираюсь сделать, это удалить в исходном варианте решения оператор UNNEST, чтобы просмотреть все события прохождения игры.

SELECT event_name, event_timestamp, user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

Затем я запрашиваю столбец value.int_value из развёрнутого массива event_params, где поле key равно value.

SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS score
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

В итоге происходит что-то вроде того, что показывает приведённая ниже анимация. Сначала мы разбиваем массив event_params в его собственную небольшую временную таблицу, фильтруя одну запись, по условию key = "value", а затем забираем полеvalue.int_value.

После чего вы получаете следующий результат:

При использовании конструкции SELECT FROM UNNEST, следует помнить о трех важных моментах:

Во-первых, вы должны убедиться, что вы поместили вызов SELECT FROM UNNEST в круглые скобки. Если этого не сделать, BigQuery выдаст ошибку, т.к. вы используете два оператора SELECT в рамках одного запроса.

Во-вторых, похоже на то, что описанный приём будет работать, только если вы запрашиваете не более одного значения из каждого вызова SELECT FROM UNNEST.

В-третьих, вы можете использовать SELECT FROM UNNEST несколько раз в одном и том же операторе SELECT! Итак, теперь мы можем взять наш предыдущий запрос и добавить в него второй вызовSELECT FROM UNNEST, чтобы получить параметры board и value рядом, в одной строке.

SELECT event_name, event_timestamp, user_pseudo_id,  
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS score,
  (SELECT value.string_value FROM UNNEST(event_params) 
    WHERE key = "board") AS board_size
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

Приведённый выше запрос вернёт ожидаемый нами результат:

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

SELECT AVG(score) AS average, STDDEV(score) as std_dev, board_size
FROM (  
  SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS score,
  (SELECT value.string_value FROM UNNEST(event_params) 
    WHERE key = "board") AS board_size
  FROM `firebase-public-project.analytics_153293282.events_20181003`
  WHERE event_name = "level_complete_quickplay"
) 
GROUP BY board_size

Анализируем параметры событий вместе со свойствами пользователей!

SELECT FROM UNNEST также можно использовать для совместного анализа параметров событий со свойствами пользователей.

Например, разработчики Flood-it используют событие trust_virtual_currency, чтобы отслеживать, когда пользователь тратит «дополнительные шаги» в конце раунда. Параметр value помогает отслеживать, сколько шагов они тратят на событие. Мы также отслеживаем количество дополнительных шагов, выполненных пользователем, с помощью свойства initial_extra_steps.

Допустим, мы хотим выяснить, есть ли какая-либо корреляция между тем, сколько шагов пользователю изначально дано, и сколько дополнительных шагов он делает во время события «use extra steps». Для этого нам необходимо проанализировать параметр value вместе с пользовательским свойством initial_extra_steps. Опять же, эти параметры объеденены в повторяющиеся записи, как нам их правильно развернуть?

Теоретически это можно сделать, объединив два вызова UNNEST.

SELECT event_name, event_timestamp, user_pseudo_id, 
  param.value.int_value AS moves_used,
  userprop.value.string_value AS initial_extra_steps
FROM `firebase-public-project.analytics_153293282.events_20181003`,
UNNEST (event_params) as param,
UNNEST (user_properties) as userprop
WHERE event_name = "spend_virtual_currency"
AND param.key = "value"
AND userprop.key = "initial_extra_steps"

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

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

Вновь нам на помощь приходит SELECT FROM UNNEST. Сначала я получу значение нашего параметра value, как в нашем первом примере.

SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS steps_used
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "spend_virtual_currency"

Затем я могу получить значение нашего пользовательского свойства. (Обратите внимание, что это значение хранится в виде строки, поэтому я конвертирую его в целое число) .

SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS steps_used,
  CAST(
    (SELECT value.string_value FROM UNNEST(user_properties) 
     WHERE key = "initial_extra_steps") 
   AS int64) AS initial_steps
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "spend_virtual_currency"

… Теперь у меня есть все необходимые данные в одной строке!

Этот код не только более эффективен, но и прост для понимания.

Теперь не сложно проанализировать выбранные нами параметры. Мы можем посмотрет, каково среднее значение используемых дополнительных шагов для каждого типа свойств пользователя…

SELECT AVG(steps_used) AS average_steps_used, initial_steps 
FROM (
  SELECT event_name, event_timestamp, user_pseudo_id, 
    (SELECT value.int_value FROM UNNEST(event_params) 
      WHERE key = "value") AS steps_used,
    CAST(
      (SELECT value.string_value FROM UNNEST(user_properties) 
       WHERE key = "initial_extra_steps") 
     AS int64) AS initial_steps
  FROM `firebase-public-project.analytics_153293282.events_20181003`
  WHERE event_name = "spend_virtual_currency"
) 
WHERE initial_steps IS NOT NULL
GROUP BY initial_steps
ORDER BY initial_steps

… Или мы могли бы посмотреть, есть ли какая-то корреляция между этими двумя значениями.

SELECT CORR(steps_used, initial_steps) AS correlation 
FROM (
SELECT event_name, event_timestamp, user_pseudo_id, 
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = "value") AS steps_used,
  CAST(
    (SELECT value.string_value FROM UNNEST(user_properties) 
     WHERE key = "initial_extra_steps") 
   AS int64) AS initial_steps
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "spend_virtual_currency"
) 
WHERE initial_steps IS NOT NULL

Итак, вы можете использовать UNNEST и SELECT FROM UNNEST для быстрой обработки повторяющихся записей, которые Google Analytics для Firebase любит использовать в своей схеме BigQuery. И, как оказалось, это те же самые повторяющиеся записи, которые отображаются в данных Crashlytics и Cloud Messaging. Поэтому я рекомендую потратить время на то, чтобы привыкнуть к этим методам, т.к. они заметно облегчат работу со сложными структурами данных.