465 lines
13 KiB
Markdown
465 lines
13 KiB
Markdown
# Query Implementation Plan for Killmail Analytics
|
|
|
|
## Objective
|
|
Build an interactive analytics interface where every aggregation table can be filtered by clicking rows, progressively narrowing down the dataset.
|
|
|
|
## Core Concept: Progressive Filtering
|
|
- Start with ALL killmails displayed
|
|
- Each table shows a different dimension breakdown
|
|
- Clicking ANY row in ANY table adds it as a filter
|
|
- All tables re-aggregate with the new filter set
|
|
- Filters are cumulative and removable
|
|
|
|
---
|
|
|
|
## Tables to Display
|
|
|
|
### 1. **Time Aggregations**
|
|
|
|
#### Table A: By Hour of Day
|
|
```sql
|
|
SELECT
|
|
kill_hour,
|
|
count() as kill_count,
|
|
count(DISTINCT victim_character_id) as unique_victims,
|
|
count(DISTINCT solar_system_id) as systems_hit
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY kill_hour
|
|
ORDER BY kill_hour
|
|
```
|
|
**Clickable column:** `kill_hour` → adds filter `kill_hour = X`
|
|
|
|
#### Table B: By Day of Week
|
|
```sql
|
|
SELECT
|
|
kill_day_of_week,
|
|
CASE kill_day_of_week
|
|
WHEN 1 THEN 'Monday'
|
|
WHEN 2 THEN 'Tuesday'
|
|
WHEN 3 THEN 'Wednesday'
|
|
WHEN 4 THEN 'Thursday'
|
|
WHEN 5 THEN 'Friday'
|
|
WHEN 6 THEN 'Saturday'
|
|
WHEN 7 THEN 'Sunday'
|
|
END as day_name,
|
|
count() as kill_count,
|
|
count(DISTINCT victim_character_id) as unique_victims
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY kill_day_of_week
|
|
ORDER BY kill_day_of_week
|
|
```
|
|
**Clickable column:** `kill_day_of_week` → adds filter `kill_day_of_week = X`
|
|
|
|
#### Table C: By Date
|
|
```sql
|
|
SELECT
|
|
kill_date,
|
|
count() as kill_count,
|
|
count(DISTINCT victim_character_id) as unique_victims,
|
|
count(DISTINCT solar_system_id) as systems_hit
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY kill_date
|
|
ORDER BY kill_date DESC
|
|
```
|
|
**Clickable column:** `kill_date` → adds filter `kill_date = 'YYYY-MM-DD'`
|
|
|
|
#### Table D: By Month
|
|
```sql
|
|
SELECT
|
|
toYYYYMM(killmail_time) as month,
|
|
count() as kill_count,
|
|
count(DISTINCT victim_character_id) as unique_victims
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY month
|
|
ORDER BY month DESC
|
|
```
|
|
**Clickable column:** `month` → adds filter `toYYYYMM(killmail_time) = YYYYMM`
|
|
|
|
---
|
|
|
|
### 2. **Location Aggregations**
|
|
|
|
#### Table E: By Solar System
|
|
```sql
|
|
SELECT
|
|
solar_system_name,
|
|
region_name,
|
|
security,
|
|
count() as kill_count,
|
|
count(DISTINCT victim_character_id) as unique_victims,
|
|
count(DISTINCT victim_ship_type_name) as ship_variety
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY solar_system_name, region_name, security
|
|
ORDER BY kill_count DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable columns:**
|
|
- `solar_system_name` → adds filter `solar_system_name = 'X'`
|
|
- `region_name` → adds filter `region_name = 'X'`
|
|
|
|
#### Table F: By Region
|
|
```sql
|
|
SELECT
|
|
region_name,
|
|
count() as kill_count,
|
|
count(DISTINCT solar_system_name) as systems_with_kills,
|
|
count(DISTINCT victim_character_id) as unique_victims
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY region_name
|
|
ORDER BY kill_count DESC
|
|
```
|
|
**Clickable column:** `region_name` → adds filter `region_name = 'X'`
|
|
|
|
#### Table G: By Constellation
|
|
```sql
|
|
SELECT
|
|
constellation_name,
|
|
region_name,
|
|
count() as kill_count,
|
|
count(DISTINCT solar_system_name) as systems_in_constellation
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY constellation_name, region_name
|
|
ORDER BY kill_count DESC
|
|
```
|
|
**Clickable column:** `constellation_name` → adds filter `constellation_name = 'X'`
|
|
|
|
#### Table H: By Security Status
|
|
```sql
|
|
SELECT
|
|
CASE
|
|
WHEN security >= 0.5 THEN 'High Sec'
|
|
WHEN security > 0.0 THEN 'Low Sec'
|
|
ELSE 'Null Sec'
|
|
END as sec_status,
|
|
count() as kill_count,
|
|
count(DISTINCT solar_system_name) as systems
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY sec_status
|
|
ORDER BY kill_count DESC
|
|
```
|
|
**Clickable column:** `sec_status` → adds filter based on security range
|
|
|
|
---
|
|
|
|
### 3. **Ship Aggregations**
|
|
|
|
#### Table I: By Victim Ship
|
|
```sql
|
|
SELECT
|
|
victim_ship_type_name,
|
|
victim_ship_group_name,
|
|
victim_ship_category_name,
|
|
count() as kill_count,
|
|
count(DISTINCT victim_character_id) as unique_pilots_killed
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY victim_ship_type_name, victim_ship_group_name, victim_ship_category_name
|
|
ORDER BY kill_count DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable columns:**
|
|
- `victim_ship_type_name` → adds filter `victim_ship_type_name = 'X'`
|
|
- `victim_ship_group_name` → adds filter `victim_ship_group_name = 'X'`
|
|
- `victim_ship_category_name` → adds filter `victim_ship_category_name = 'X'`
|
|
|
|
#### Table J: By Attacker Ship
|
|
```sql
|
|
SELECT
|
|
ship_type_name,
|
|
ship_group_name,
|
|
count() as times_used,
|
|
count(DISTINCT killmail_id) as kills_participated,
|
|
sum(damage_done) as total_damage,
|
|
countIf(final_blow) as final_blows
|
|
FROM killmail_attackers
|
|
WHERE killmail_id IN (
|
|
SELECT killmail_id FROM killmails WHERE <active_filters>
|
|
)
|
|
GROUP BY ship_type_name, ship_group_name
|
|
ORDER BY kills_participated DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable columns:**
|
|
- `ship_type_name` → adds filter on attacker ship
|
|
- `ship_group_name` → adds filter on attacker ship group
|
|
|
|
---
|
|
|
|
### 4. **Player/Entity Aggregations**
|
|
|
|
#### Table K: By Victim Character
|
|
```sql
|
|
SELECT
|
|
victim_character_name,
|
|
victim_corporation_name,
|
|
victim_alliance_name,
|
|
count() as deaths,
|
|
count(DISTINCT victim_ship_type_name) as ships_lost
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY victim_character_name, victim_corporation_name, victim_alliance_name
|
|
ORDER BY deaths DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable columns:**
|
|
- `victim_character_name` → filter by victim
|
|
- `victim_corporation_name` → filter by victim corp
|
|
- `victim_alliance_name` → filter by victim alliance
|
|
|
|
#### Table L: By Victim Corporation
|
|
```sql
|
|
SELECT
|
|
victim_corporation_name,
|
|
victim_alliance_name,
|
|
count() as losses,
|
|
count(DISTINCT victim_character_name) as members_killed
|
|
FROM killmails
|
|
WHERE <active_filters>
|
|
GROUP BY victim_corporation_name, victim_alliance_name
|
|
ORDER BY losses DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable columns:**
|
|
- `victim_corporation_name` → filter by victim corp
|
|
- `victim_alliance_name` → filter by victim alliance
|
|
|
|
#### Table M: By Victim Alliance
|
|
```sql
|
|
SELECT
|
|
victim_alliance_name,
|
|
count() as losses,
|
|
count(DISTINCT victim_corporation_name) as corps_hit,
|
|
count(DISTINCT victim_character_name) as members_killed
|
|
FROM killmails
|
|
WHERE <active_filters> AND victim_alliance_name != ''
|
|
GROUP BY victim_alliance_name
|
|
ORDER BY losses DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable column:** `victim_alliance_name` → filter by victim alliance
|
|
|
|
#### Table N: By Attacker Character
|
|
```sql
|
|
SELECT
|
|
character_name,
|
|
corporation_name,
|
|
alliance_name,
|
|
count(DISTINCT killmail_id) as kills_participated,
|
|
countIf(final_blow) as final_blows,
|
|
sum(damage_done) as total_damage
|
|
FROM killmail_attackers
|
|
WHERE killmail_id IN (
|
|
SELECT killmail_id FROM killmails WHERE <active_filters>
|
|
)
|
|
GROUP BY character_name, corporation_name, alliance_name
|
|
ORDER BY kills_participated DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable columns:**
|
|
- `character_name` → filter by attacker
|
|
- `corporation_name` → filter by attacker corp
|
|
- `alliance_name` → filter by attacker alliance
|
|
|
|
#### Table O: By Attacker Corporation
|
|
```sql
|
|
SELECT
|
|
corporation_name,
|
|
alliance_name,
|
|
count(DISTINCT killmail_id) as kills_participated,
|
|
count(DISTINCT character_name) as members_involved
|
|
FROM killmail_attackers
|
|
WHERE killmail_id IN (
|
|
SELECT killmail_id FROM killmails WHERE <active_filters>
|
|
)
|
|
GROUP BY corporation_name, alliance_name
|
|
ORDER BY kills_participated DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable columns:**
|
|
- `corporation_name` → filter by attacker corp
|
|
- `alliance_name` → filter by attacker alliance
|
|
|
|
#### Table P: By Attacker Alliance
|
|
```sql
|
|
SELECT
|
|
alliance_name,
|
|
count(DISTINCT killmail_id) as kills_participated,
|
|
count(DISTINCT corporation_name) as corps_involved,
|
|
count(DISTINCT character_name) as members_involved
|
|
FROM killmail_attackers
|
|
WHERE killmail_id IN (
|
|
SELECT killmail_id FROM killmails WHERE <active_filters>
|
|
) AND alliance_name != ''
|
|
GROUP BY alliance_name
|
|
ORDER BY kills_participated DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable column:** `alliance_name` → filter by attacker alliance
|
|
|
|
---
|
|
|
|
### 5. **Module/Item Aggregations** (THE COMPLEX ONES)
|
|
|
|
#### Table Q: By Module Slot Type
|
|
```sql
|
|
SELECT
|
|
slot_type,
|
|
count() as items_fitted,
|
|
count(DISTINCT killmail_id) as ships_with_slot,
|
|
count(DISTINCT item_type_name) as module_variety
|
|
FROM killmail_items
|
|
WHERE killmail_id IN (
|
|
SELECT killmail_id FROM killmails WHERE <active_filters>
|
|
)
|
|
GROUP BY slot_type
|
|
ORDER BY items_fitted DESC
|
|
```
|
|
**Clickable column:** `slot_type` → filter to show only items in this slot type
|
|
|
|
#### Table R: By Module (within selected slot type)
|
|
**This table only appears after slot_type is filtered**
|
|
```sql
|
|
SELECT
|
|
item_type_name,
|
|
item_group_name,
|
|
item_category_name,
|
|
count() as times_fitted,
|
|
count(DISTINCT killmail_id) as ships_with_module
|
|
FROM killmail_items
|
|
WHERE killmail_id IN (
|
|
SELECT killmail_id FROM killmails WHERE <active_filters>
|
|
)
|
|
AND slot_type = <selected_slot_type>
|
|
GROUP BY item_type_name, item_group_name, item_category_name
|
|
ORDER BY times_fitted DESC
|
|
LIMIT 100
|
|
```
|
|
**Clickable columns:**
|
|
- `item_type_name` → adds filter `has this module in this slot`
|
|
- `item_group_name` → filter by module group
|
|
- `item_category_name` → filter by module category
|
|
|
|
#### Table S: Module Co-occurrence (Advanced)
|
|
**Shows what modules appear together with a selected module**
|
|
```sql
|
|
WITH selected_ships AS (
|
|
SELECT DISTINCT killmail_id
|
|
FROM killmail_items
|
|
WHERE item_type_name = <selected_module>
|
|
AND slot_type = <selected_slot_type>
|
|
AND killmail_id IN (
|
|
SELECT killmail_id FROM killmails WHERE <active_filters>
|
|
)
|
|
)
|
|
SELECT
|
|
item_type_name,
|
|
slot_type,
|
|
count() as times_fitted_together,
|
|
count() * 100.0 / (SELECT count() FROM selected_ships) as percentage
|
|
FROM killmail_items
|
|
WHERE killmail_id IN selected_ships
|
|
AND NOT (item_type_name = <selected_module> AND slot_type = <selected_slot_type>)
|
|
GROUP BY item_type_name, slot_type
|
|
ORDER BY times_fitted_together DESC
|
|
LIMIT 50
|
|
```
|
|
|
|
---
|
|
|
|
## Filter Implementation Logic
|
|
|
|
### Filter State Structure
|
|
```json
|
|
{
|
|
"kill_hour": [13, 14],
|
|
"solar_system_name": ["Amamake", "Dal"],
|
|
"victim_ship_type_name": ["Algos"],
|
|
"region_name": ["Heimatar"],
|
|
"slot_type": ["mid"],
|
|
"has_module": {
|
|
"slot": "mid",
|
|
"module_name": "Stasis Webifier II"
|
|
}
|
|
}
|
|
```
|
|
|
|
### Building WHERE Clause
|
|
```sql
|
|
WHERE 1=1
|
|
AND kill_hour IN (13, 14)
|
|
AND solar_system_name IN ('Amamake', 'Dal')
|
|
AND victim_ship_type_name = 'Algos'
|
|
AND region_name = 'Heimatar'
|
|
AND killmail_id IN (
|
|
SELECT DISTINCT killmail_id
|
|
FROM killmail_items
|
|
WHERE slot_type = 'mid'
|
|
AND item_type_name = 'Stasis Webifier II'
|
|
)
|
|
```
|
|
|
|
### Special Case: Module Filters
|
|
When filtering by "has module X in slot Y":
|
|
```sql
|
|
AND killmail_id IN (
|
|
SELECT killmail_id
|
|
FROM killmail_items
|
|
WHERE slot_type = 'Y'
|
|
AND item_type_name = 'X'
|
|
)
|
|
```
|
|
|
|
For multiple modules in same slot (AND condition):
|
|
```sql
|
|
AND killmail_id IN (
|
|
SELECT killmail_id
|
|
FROM killmail_items
|
|
WHERE slot_type = 'mid'
|
|
AND item_type_name IN ('Module A', 'Module B')
|
|
GROUP BY killmail_id
|
|
HAVING count(DISTINCT item_type_name) = 2
|
|
)
|
|
```
|
|
|
|
---
|
|
|
|
## Additional Aggregatable Columns
|
|
|
|
### We CAN aggregate on:
|
|
- ✅ `kill_hour`, `kill_day_of_week`, `kill_date`, `month`
|
|
- ✅ `solar_system_name`, `constellation_name`, `region_name`, `security`
|
|
- ✅ `victim_ship_type_name`, `victim_ship_group_name`, `victim_ship_category_name`
|
|
- ✅ `victim_character_name`, `victim_corporation_name`, `victim_alliance_name`
|
|
- ✅ Attacker ships (from `killmail_attackers`)
|
|
- ✅ Attacker characters/corps/alliances (from `killmail_attackers`)
|
|
- ✅ `slot_type`, `item_type_name`, `item_group_name`, `item_category_name` (from `killmail_items`)
|
|
- ✅ Weapon types (from `killmail_attackers.weapon_type_name`)
|
|
- ✅ `final_blow` (bool - can group by attacker ships that got final blow)
|
|
|
|
### Performance Notes (ignore for now):
|
|
- These queries will be FAST on 10GB
|
|
- ClickHouse will handle millions of rows in milliseconds
|
|
- The subquery filters (`killmail_id IN (...)`) are fine - ClickHouse optimizes these well
|
|
- LIMIT 100 keeps UI snappy even with huge result sets
|
|
|
|
---
|
|
|
|
## Implementation Checklist for Clanker
|
|
|
|
1. **Query Template System**: Build WHERE clause dynamically from filter state
|
|
2. **Execute ALL aggregation queries** on every filter change
|
|
3. **Display tables** with clickable rows/columns
|
|
4. **Update filter state** on click
|
|
5. **Re-run all queries** with new filters
|
|
6. **Show active filters** with remove buttons
|
|
7. **Handle module filtering** specially (slot + type combo)
|
|
|
|
That's it. Every table. Every clickable dimension. Maximum analytics power. |