Как использовать SQL Data Explorer для анализа игровых данных

Смотрите нашу коллекцию рецептов в SQL Cookbook, чтобы исследовать богатые данные в UGS. Обратите внимание, что UGS использует вариант SQL Snowflake.

Один из запросов в кулинарной книге рассматривает статистику миссий. Давайте адаптируем этот код, чтобы быстро взглянуть на показатели неудач миссий в нашей вымышленной игре. Это использует пользовательские события, которые мы создали для отслеживания вовлеченности игроков в миссии с нашим параметром missionID.

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

Обратите внимание, что мы использовали фильтр по дате здесь, чтобы ограничить наш запрос и сделать его эффективным. Без этого ограничения запрос выполнялся бы по полным 365 дням данных, которые по умолчанию доступны для запроса в SQL Data Explorer. Кроме того, всегда эффективнее указывать, какие столбцы вас интересуют, а не использовать SELECT *.

Фразы вроде EVENT_JSON:missionID::INTEGER могут показаться устрашающими, но если вы введете 'missionID' и используете автозаполнение, SQL Data Explorer сгенерирует для вас синтаксис JSON – при условии, что вы настроили этот параметр в своей игре.

После выполнения запроса мы можем построить наши результаты, чтобы увидеть историю в данных. Графики в настоящее время поддерживают до двух осей Y и одну ось X. Названия осей можно легко переименовать, используя выражение ‘as’ в вашем SQL-запросе; в этом случае наша ось Y принимает имя, которое мы определили: “Игроки не справились %”.

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

Совет: Если у вас есть некоторые значения NULL в ваших данных и вы заметили, что это вызывает странный вид оси, используйте coalesce(yourParameter, 0), чтобы заполнить пробелы.

Когда мы выполняем запрос, мы получаем таблицу наших результатов. Добавьте PLATFORM в наш запрос; на изображении выше вы увидите, как теперь выглядит таблица. Обратите внимание на кнопку «Pivot» справа. Это полезно для изменения формы наших данных без необходимости переписывать наш запрос.

В нашем примере мы могли бы использовать инструмент поворота, чтобы настроить наши данные так, чтобы PLATFORM находился в строках, а MISSIONID — в столбцах.

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

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

Допустим, вы хотите выполнить этот базовый запрос к вашим данным:

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

Здесь мы делим наших пользователей на 100 псевдослучайно назначенных и пронумерованных ведер и смотрим на ведро номер 63.

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

В тех запросах выше мы использовали count(distinct…) для подсчета числа наших отдельных игроков и комбинаций событий. Один из способов улучшить скорость нашего запроса, если нам не нужна 100% точность результатов, — использовать approximate_count_distinct. Наш предыдущий запрос становится:

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

Давайте взглянем на панель Глоссария, чтобы изучить таблицы, доступные для запроса.

Рядом с СОБЫТИЯМИ мы находим все агрегированные таблицы, доступные для запроса. Все они доступны из коробки с UGS.

• Таблица ПОЛЬЗОВАТЕЛИ содержит одну строку на игрока вместе с их метриками за все время в игре, такими как количество событий, общее время игры, общие расходы и т. д.
• FACT_USER_SESSIONS_DAY включает данные о каждой сессии для каждого игрока.
• FACT_EVENT_TYPE_USERS_DAY состоит из строки для каждого события, которое игрок отправил каждый день, вместе с общим количеством.
• FACT_WAU_USERS и FACT_MAU_USERS включают данные профиля для пользователей, которые играли в течение предыдущей недели или месяца в определенный день.

Между FACT_EVENT_TYPE_USERS_DAY и FACT_USER_SESSIONS_DAY вы, вероятно, сможете ответить на 80%+ большинства запросов по меньшим объектам.

Например, в нашем первом запросе мы рассматривали коэффициенты неудач миссий. Мы также могли бы использовать FACT_EVENT_TYPE_USERS_DAY для расчета общих коэффициентов неудач каждый день, с количеством СОБЫТИЙ, хранящимся в этой таблице.

Мы также используем одну из этих таблиц в нашем следующем запросе:

Используйте этот запрос, чтобы увидеть поток событий для игроков, которые соответствуют определенным критериям. Это полезно для QA и отладки, потому что – используя таблицу ПОЛЬЗОВАТЕЛИ, упомянутую выше – вы получите другого пользователя каждый раз, когда запустите его.

Если, например, вы подозреваете, что события не записываются правильно для игроков, установивших определенную версию вашей игры, вы можете выполнить запрос ниже. То, что возвращается, – это поток событий случайного игрока, запускающего версию игры, которая, похоже, испытывает проблемы. Сделайте это несколько раз, и вы быстро начнете замечать закономерности в данных.

Совет: Если вы хотите закомментировать несколько строк, используйте сочетание клавиш CTRL+/

Возможно, вы привыкли писать SQL-запросы на языках, отличных от Snowflake – например, если вы использовали предыдущий инструмент Data Mining deltaDNA, вы, вероятно, писали запросы на Vertica.

Теперь вы можете ссылаться на вновь определенные переменные, не включая их сначала в общее выражение таблицы (CTE). Например, этот запрос успешно выполняется в SQL Data Explorer – но в оригинальном deltaDNA он вызвал бы ошибку «столбец 'rice' не существует»:

В SQL Explorer есть много потенциала. В UGS Analytics есть гораздо больше для открытия, включая множество вариантов диаграмм, таких как круговые и сложенные столбчатые диаграммы. Прямой доступ предоставляет вам прямой доступ к вашим данным Analytics через Snowflake.

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

Дополнительное чтение

• Документация по UGS Analytics
• Кулинарная книга SQL
• Представляем Funnels и SQL Data Explorer
• Справочник функций SQL Snowflake
• Цены UGS