92 lines
3.4 KiB
SQL
92 lines
3.4 KiB
SQL
-- Aggregating projections so full-table GROUP BY over items/attackers uses
|
|
-- pre-aggregated data instead of scanning all rows. Apply to existing DBs;
|
|
-- then run MATERIALIZE PROJECTION so existing data is projected (see end).
|
|
-- Queries must use uniq() (not count(DISTINCT)) so the optimizer picks these.
|
|
--
|
|
-- Refs: https://clickhouse.com/docs/data-modeling/projections
|
|
|
|
-- ReplacingMergeTree in 25.x requires this table setting to allow ADD PROJECTION.
|
|
ALTER TABLE zkill.killmail_items MODIFY SETTING deduplicate_merge_projection_mode = 'rebuild';
|
|
ALTER TABLE zkill.killmail_attackers MODIFY SETTING deduplicate_merge_projection_mode = 'rebuild';
|
|
|
|
-- Matches: queryModuleBySlotType (slot_type, items_fitted, ships_with_slot, module_variety)
|
|
ALTER TABLE zkill.killmail_items
|
|
ADD PROJECTION IF NOT EXISTS proj_by_slot_type (
|
|
SELECT
|
|
slot_type,
|
|
count() AS items_fitted,
|
|
uniq(killmail_id) AS ships_with_slot,
|
|
uniq(item_type_name) AS module_variety
|
|
GROUP BY slot_type
|
|
);
|
|
|
|
-- Matches: queryModuleByModuleAllSlots / queryModuleByModule (per-slot module counts)
|
|
ALTER TABLE zkill.killmail_items
|
|
ADD PROJECTION IF NOT EXISTS proj_by_slot_module (
|
|
SELECT
|
|
slot_type,
|
|
item_type_id,
|
|
item_type_name,
|
|
item_group_name,
|
|
item_category_name,
|
|
count() AS times_fitted,
|
|
uniq(killmail_id) AS ships_with_module
|
|
GROUP BY slot_type, item_type_id, item_type_name, item_group_name, item_category_name
|
|
);
|
|
|
|
-- Matches: queryPlayerByAttackerCharacter. Use uniq() so projection matches; query can use uniq for speed.
|
|
ALTER TABLE zkill.killmail_attackers
|
|
ADD PROJECTION IF NOT EXISTS proj_by_character (
|
|
SELECT
|
|
character_name,
|
|
corporation_name,
|
|
alliance_name,
|
|
uniq(killmail_id) AS kills_participated,
|
|
countIf(final_blow) AS final_blows,
|
|
sum(damage_done) AS total_damage
|
|
GROUP BY character_name, corporation_name, alliance_name
|
|
);
|
|
|
|
-- Matches: queryShipByAttacker
|
|
ALTER TABLE zkill.killmail_attackers
|
|
ADD PROJECTION IF NOT EXISTS proj_by_ship (
|
|
SELECT
|
|
ship_type_name,
|
|
ship_group_name,
|
|
count() AS times_used,
|
|
uniq(killmail_id) AS kills_participated,
|
|
sum(damage_done) AS total_damage,
|
|
countIf(final_blow) AS final_blows
|
|
GROUP BY ship_type_name, ship_group_name
|
|
);
|
|
|
|
-- Matches: queryPlayerByAttackerCorporation
|
|
ALTER TABLE zkill.killmail_attackers
|
|
ADD PROJECTION IF NOT EXISTS proj_by_corporation (
|
|
SELECT
|
|
corporation_name,
|
|
alliance_name,
|
|
uniq(killmail_id) AS kills_participated,
|
|
uniq(character_name) AS members_involved
|
|
GROUP BY corporation_name, alliance_name
|
|
);
|
|
|
|
-- Matches: queryPlayerByAttackerAlliance
|
|
ALTER TABLE zkill.killmail_attackers
|
|
ADD PROJECTION IF NOT EXISTS proj_by_alliance (
|
|
SELECT
|
|
alliance_name,
|
|
uniq(killmail_id) AS kills_participated,
|
|
uniq(corporation_name) AS corps_involved,
|
|
uniq(character_name) AS members_involved
|
|
GROUP BY alliance_name
|
|
);
|
|
|
|
-- Build projections for existing data (can be slow; run during low traffic).
|
|
ALTER TABLE zkill.killmail_items MATERIALIZE PROJECTION proj_by_slot_type;
|
|
ALTER TABLE zkill.killmail_items MATERIALIZE PROJECTION proj_by_slot_module;
|
|
ALTER TABLE zkill.killmail_attackers MATERIALIZE PROJECTION proj_by_character;
|
|
ALTER TABLE zkill.killmail_attackers MATERIALIZE PROJECTION proj_by_ship;
|
|
ALTER TABLE zkill.killmail_attackers MATERIALIZE PROJECTION proj_by_corporation;
|
|
ALTER TABLE zkill.killmail_attackers MATERIALIZE PROJECTION proj_by_alliance;
|