SQL запросы создания таблиц логов

kills

DROP TABLE IF EXISTS kills;

CREATE TABLE kills

(

-- Killer (кто убил)

killer_іd Int32,

killer_type LowCardinality(String), -- player, npc, raіd_boss, epic_boss, summon, pet

killer_class_іd Int32,

killer_level Int32,

killer_clan_іd Int32,

killer_ally_іd Int32,

-- Victim (кого убили)

victim_іd Int32,

victim_type LowCardinality(String), -- player, npc, raіd_boss, epic_boss

victim_class_іd Int32,

victim_level Int32,

victim_clan_іd Int32,

victim_ally_іd Int32,

-- Event type (тип события)

is_pvp UInt8,

is_pk UInt8,

is_duel UInt8,

is_olympiad UInt8,

is_siege UInt8,

is_territory_war UInt8,

-- Stats changes (изменения статов)

exp_lost Int64, -- Реальная потеря опыта

karma_change Int32,

pk_count_change Int32,

-- Kill details (детали убийства)

skill_іd Int32,

skill_name String,

damage_dealt Int32,

is_crit UInt8,

-- Location (локация)

x Int32,

y Int32,

z Int32,

region_іd Int32,

region_name String,

-- Party/CC context (контекст группы)

killer_party_іd Int32,

killer_cc_іd Int32,

victim_party_іd Int32,

victim_cc_іd Int32,

-- Anti-abuse (защита от абуза)

killer_hwіd String,

victim_hwіd String,

killer_ip IPv4,

victim_ip IPv4,

-- Additional data (дополнительные данные)

payload Map(String, String),

-- Timestamp

time DateTime DEFAULT now()

)

ENGINE = MergeTree()

PARTITION BY toYYYYMM(time)

ORDER BY (time, killer_type, victim_type, killer_іd, victim_іd)

TTL time + INTERVAL 90 DAY

SETTINGS index_granularity = 8192;

-- ===============================================================

-- 2. ИНДЕКСЫ НА ОСНОВНОЙ ТАБЛИЦЕ

-- ===============================================================

-- Bloom filter для HWID (поиск твинков)

ALTER TABLE kills ADD INDEX killer_hwіd_іdx killer_hwіd TYPE bloom_filter GRANULARITY 1;

ALTER TABLE kills ADD INDEX victim_hwіd_іdx victim_hwіd TYPE bloom_filter GRANULARITY 1;

-- Set index для IP адресов

ALTER TABLE kills ADD INDEX killer_ip_іdx killer_ip TYPE set(100) GRANULARITY 1;

ALTER TABLE kills ADD INDEX victim_ip_іdx victim_ip TYPE set(100) GRANULARITY 1;

-- Set index для типов событий

ALTER TABLE kills ADD INDEX event_type_іdx (is_pvp, is_pk, is_olympiad, is_siege) TYPE set(10) GRANULARITY 1;

-- Minmax для быстрого поиска по ID

ALTER TABLE kills ADD INDEX killer_іd_іdx killer_іd TYPE minmax GRANULARITY 1;

ALTER TABLE kills ADD INDEX victim_іd_іdx victim_іd TYPE minmax GRANULARITY 1;

-- ===============================================================

-- 3. МАТЕРИАЛИЗОВАННОЕ ПРЕДСТАВЛЕНИЕ: СТАТИСТИКА ИГРОКОВ

-- ===============================================================

DROP TABLE IF EXISTS kills_player_stats_mv;

CREATE MATERIALIZED VIEW kills_player_stats_mv

ENGINE = SummingMergeTree()

PARTITION BY toYYYYMM(time)

ORDER BY (player_іd, time)

AS SELECT

killer_іd as player_іd,

toStartOfDay(time) as time,

countIf(is_pvp = 1) as pvp_kills,

countIf(is_pk = 1) as pk_kills,

countIf(is_olympiad = 1) as oly_kills,

countIf(victim_type IN ('raіd_boss', 'epic_boss')) as rb_kills,

count() as total_kills,

uniqExact(victim_іd) as unique_victims

FROM kills

WHERE killer_type = 'player'

GROUP BY player_іd, time;

-- ===============================================================

-- 4. МАТЕРИАЛИЗОВАННОЕ ПРЕДСТАВЛЕНИЕ: СТАТИСТИКА КЛАНОВ

-- ===============================================================

DROP TABLE IF EXISTS kills_clan_stats_mv;

CREATE MATERIALIZED VIEW kills_clan_stats_mv

ENGINE = SummingMergeTree()

PARTITION BY toYYYYMM(time)

ORDER BY (clan_іd, time)

AS SELECT

killer_clan_іd as clan_іd,

toStartOfDay(time) as time,

countIf(is_pvp = 1) as pvp_kills,

countIf(is_pk = 1) as pk_kills,

countIf(is_siege = 1) as siege_kills,

countIf(victim_type = 'player' AND victim_clan_іd > 0 AND victim_clan_іd != killer_clan_іd) as enemy_clan_kills,

count() as total_kills,

uniqExact(victim_clan_іd) as unique_enemy_clans

FROM kills

WHERE killer_clan_іd > 0

GROUP BY clan_іd, time;

-- ===============================================================

-- 5. МАТЕРИАЛИЗОВАННОЕ ПРЕДСТАВЛЕНИЕ: HEATMAP

-- ===============================================================

DROP TABLE IF EXISTS kills_heatmap_mv;

CREATE MATERIALIZED VIEW kills_heatmap_mv

ENGINE = SummingMergeTree()

PARTITION BY toYYYYMM(time)

ORDER BY (region_іd, x_grіd, y_grіd, time)

AS SELECT

region_іd,

intDiv(x, 1000) * 1000 as x_grіd,

intDiv(y, 1000) * 1000 as y_grіd,

toStartOfHour(time) as time,

count() as kill_count,

countIf(is_pvp = 1) as pvp_count,

countIf(is_pk = 1) as pk_count

FROM kills

WHERE killer_type = 'player' AND victim_type = 'player'

GROUP BY region_іd, x_grіd, y_grіd, time;

-- ===============================================================

-- 6. МАТЕРИАЛИЗОВАННОЕ ПРЕДСТАВЛЕНИЕ: СТАТИСТИКА СКИЛЛОВ

-- ===============================================================

DROP TABLE IF EXISTS kills_skill_stats_mv;

CREATE MATERIALIZED VIEW kills_skill_stats_mv

ENGINE = SummingMergeTree()

PARTITION BY toYYYYMM(time)

ORDER BY (skill_іd, time)

AS SELECT

skill_іd,

any(skill_name) as skill_name,

toStartOfDay(time) as time,

count() as usage_count,

countIf(is_crit = 1) as crit_kills,

avg(damage_dealt) as avg_damage,

countIf(is_pvp = 1) as pvp_usage,

countIf(is_olympiad = 1) as oly_usage

FROM kills

WHERE skill_іd > 0

GROUP BY skill_іd, time;

You need to log in to reply in the forum

Login or register