Как использовать 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 в столбцах.

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

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

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

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

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

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

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

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

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

Наряду с EVENTS здесь представлены все таблицы агрегатов, доступные для запросов. Все это доступно в UGS из коробки.

• Таблица USERS содержит одну строку для каждого игрока, в которой указаны показатели его жизни в игре, такие как количество событий, общее время игры, общие расходы и т. д.
• 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 для расчета общего количества отказов за каждый день, при этом в этой таблице будет храниться счетчик NUMBER_OF_EVENTS.

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

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

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

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

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

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

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

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

Дальнейшее чтение

• Документация UGS Analytics
• Поваренная книга SQL
• Представляем Funnels и SQL Data Explorer
• Ссылка на функцию Snowflake SQL
• Ценообразование в ПХГ