Условие

В корабельном балансе много параметров, которые тюнятся ежедневно. Хотим дашборд для мониторинга core-метрик кораблей в Tableau / Power BI. Использовать сырые таблицы боёв — слишком тяжело.

Дано 4 SQLite-таблицы:

arenas — данные боёв (режим, карта, длительность, winner_team_id).
arena_members — участники боёв (id игрока, корабль, урон, фраги, выживание).
glossary_ships — справочник кораблей (страна, класс, уровень).
catalog_items — справочник идентификаторов (team_build_type, arena_type).

Напишите SQL для агрегационной fact-таблицы, удовлетворяющей требованиям:

Гранулярность: день.
Только реальные игроки (без ботов).
Исключить CUSTOM и TUTORIAL игры.
Объединить PVE и PVE_PREMADE в единое значение PVE.
Возможность фильтрации по: team_build_type, ship, ship_class, ship_level, map.
Метрики:
- Win rate
- Survivability
- Avg damage per battle
- Frags-to-deaths ratio
- Avg credits income
- Avg experience income
Метрики должны считаться на уровне дня + корабля (или класса).

Решение

Подход

Главное — выбрать правильную гранулярность fact-таблицы:

Не «строка = бой» (это сырые данные).
Не «строка = ship» (теряем дату).
Гранулярность = (date, team_build_type, ship_id, map_id) — все возможные оси фильтрации сжимаются по этой комбинации.

Дальше — расчёт метрик по этой группировке так, чтобы дальнейшая агрегация в BI давала корректные результаты (additive vs derived).

Реализация

WITH base AS (
  SELECT
    DATE(a.start_dt)                                AS dt,
    -- Объединяем PVE и PVE_PREMADE
    CASE
      WHEN ci_tbt.name = 'PVE_PREMADE' THEN 'PVE'
      ELSE ci_tbt.name
    END                                             AS team_build_type,
    a.map_type_id                                   AS map_id,
    am.vehicle_type_id                              AS ship_id,
    gs.ship_name,
    gs.ship_class,
    gs.ship_level,
 
    -- Победа: 1 если команда игрока выиграла
    CASE WHEN a.winner_team_id = am.team_id THEN 1 ELSE 0 END AS won,
    am.is_alive                                     AS survived,
    am.damage                                       AS damage,
    am.ships_killed                                 AS frags,
    am.credits                                      AS credits,
    am.exp                                          AS exp
  FROM arena_members am
  JOIN arenas         a       ON a.arena_id     = am.arena_id
                              AND a.periphery_id = am.periphery_id
  JOIN glossary_ships gs      ON gs.id          = am.vehicle_type_id
  JOIN catalog_items  ci_tbt  ON ci_tbt.id      = a.team_build_type_id
                              AND ci_tbt.category = 'TEAM_BUILD_TYPE'
  JOIN catalog_items  ci_arena ON ci_arena.id   = a.map_type_id
                              AND ci_arena.category = 'ARENA_TYPES'
  WHERE
        am.account_db_id > 0                        -- только реальные игроки (не боты)
    AND ci_tbt.name NOT IN ('CUSTOM', 'TUTORIAL')   -- исключаем кастомы и туторы
)
SELECT
  dt,
  team_build_type,
  ship_id, ship_name, ship_class, ship_level,
  map_id,
 
  -- Аддитивные счётчики (для корректной агрегации в BI)
  COUNT(*)                AS battles,
  SUM(won)                AS wins,
  SUM(survived)           AS survivals,
  SUM(damage)             AS total_damage,
  SUM(frags)              AS total_frags,
  SUM(credits)            AS total_credits,
  SUM(exp)                AS total_exp,
  COUNT(*) - SUM(survived) AS deaths,
 
  -- Derived-метрики уровня группы (для удобства просмотра, в BI лучше пересчитывать из аддитивных)
  AVG(won)               AS win_rate,
  AVG(survived)          AS survival_rate,
  AVG(damage)            AS avg_damage_per_battle,
  CASE WHEN COUNT(*) - SUM(survived) > 0
       THEN SUM(frags) * 1.0 / (COUNT(*) - SUM(survived))
       ELSE NULL END     AS frags_to_deaths,
  AVG(credits)           AS avg_credits,
  AVG(exp)               AS avg_exp
FROM base
GROUP BY 1, 2, 3, 4, 5, 6, 7;

Обоснование выбора аддитивных полей

Главная идея: в BI пользователь будет фильтровать (например, выбрал ship_class = 'BB' и map_id = 5) и хочет получить корректный win rate за этот срез. Если хранить только win_rate, его придётся усреднять — а усреднение средних с разными знаменателями даёт неверный результат:

Корабль X: 100 боёв, 70% win → 70 побед.
Корабль Y: 10 боёв, 100% win → 10 побед.
AVG win_rate = 85%.
Правильный = 80 / 110 = 72.7%.

Поэтому хранить нужно числители и знаменатели (wins, battles, total_damage, deaths и т.д.), а derived-метрики пересчитывать в BI как SUM(wins) / SUM(battles). Это classic mistake в дашбордах.

Анализ / интерпретация

Получаем компактную fact-таблицу: 1 запись на (день × корабль × режим × карта). Объём данных — на 2 порядка меньше сырых битв. BI работает быстро, фильтры по любой оси.

Срезы, на которых пользователь будет резать:

День / неделя / месяц.
Класс / уровень / страна корабля.
Режим (PVE, PVP, Random, Ranked).
Карта.
Конкретный корабль.

Все они доступны без изменения структуры.

Подводные камни

Хранить только derived-метрики (win_rate, avg_damage) — приведёт к арифметически неверным агрегациям при фильтрации в BI. Храните числители и знаменатели.
Деление на ноль: SUM(deaths) = 0 — frags_to_deaths хочет null или infinity. В SQL — NULLIF(...).
Гранулярность по дате — DATE(a.start_dt) — не забываем о tz. В прод-системах фактом является UTC-дата.
JOIN по двум ключам: arena_id + periphery_id — забыть второй ключ = декартово произведение.
team_build_type нормализация: CASE WHEN name = 'PVE_PREMADE' THEN 'PVE' — обязательно.
Боты (account_db_id < 0): не отфильтровать = win rate взлетит / просядет искусственно.
Кастомы / туториалы — формально это «бои», но они портят бизнес-метрики.
Уровень корабля в glossary_ships — храним для фильтрации в BI; не пересчитываем в metric.
Производительность: для большой базы — индексы на (arena_id, periphery_id) и партиции по dt.

Альтернативы

Materialized view в проде: автоматически пересчитывается раз в день.
Cube для BI (например, AtScale / Druid) — если нужны быстрые срезы по N измерениям.
Хранить «сырые» measure-поля без derived и пересчитывать всё в BI — самая чистая архитектура (single source of truth).

Эталонный ответ

Главные SQL-практики в ответе:

Гранулярность fact-таблицы = (date, ship, team_build_type, map).
Хранить числители и знаменатели, derived-метрики пересчитывать в BI.
Фильтр ботов, custom/tutorial исключены, PVE_PREMADE объединён с PVE.
JOIN по двум ключам (arena_id, periphery_id).
NULLIF(deaths, 0) для безопасного деления.

WoWS Data Engineer — fact-таблица для дашборда метрик кораблей