Using SQL on text files to discover which ship to fly in EVE online

EVE online is a MMORPG where the players fly spaceships to blow each other to pieces and smack talk in forums and chats online.

The developers recently released a CSV data dump containing all the PvP kills (spaceships blown apart by other players) in the last month.

I thought it was an excellent excuse to test out one of the projects I starred on github and never got to try out: q, a nifty tool that allows you to use SQL queries on CSV or otherwise tabular text files.

As a first step, since q was having some problems with the text encoding in CCP release, I got rid of most of the colums I didn’t care about:

$ mkdir eve-killdump
$ cd eve-killdump/
$ unzip ~/Downloads/WWB_killdump.zip
$ cut -d, -f10-12,14,16-18 killdump.csv > onlymatters.csv

At this point my “onlymatters.csv” file looked like this:

destroyedShipType,destroyedShipGroup,killTime,solarSystemName,regionName,iskLost,iskDestroyed
Coercer,Destroyer,2016-03-10 21:29:37,Lamaa,The Bleak Lands,12585330.52,6692045.33

Then I ran my first query, I wanted to see which ships were destroyed the most:

$ q -H -d, -w none "SELECT destroyedShipType, destroyedShipGroup, COUNT(*) as nDestroyed FROM ./onlymatters.csv GROUP BY destroyedShipType ORDER BY nDestroyed" | tail -n 10
Atron,Frigate,6067
Catalyst,Destroyer,7702
Caracal,Cruiser,7903
Svipul,Tactical Destroyer,8165
Velator,Rookie ship,8481
Tristan,Frigate,8700
Mobile Tractor Unit,Mobile Tractor Unit,8712
Thrasher,Destroyer,8914
Ibis,Rookie ship,10503
Capsule,Capsule,155253

Unsurprisingly, a lot of Frigates (the cheapest/easiest to fly spaceship type) are destroyed the most. And most of them of Gallente manufacturing! Interesting.

Then I wanted to see the destroyed ships grouped by “ship group” (shall we call it “class”?) and ran:

$ q -H -d, -w none "SELECT destroyedShipType, destroyedShipGroup, COUNT(*) as nDestroyed FROM ./onlymatters.csv GROUP BY destroyedShipGroup ORDER BY nDestroyed" | tail -n 10
Manticore,Stealth Bomber,10924
Megathron,Battleship,11947
Svipul,Tactical Destroyer,15961
Drake,Combat Battlecruiser,16478
Malediction,Interceptor,22912
Ibis,Rookie ship,28716
Coercer,Destroyer,31393
Maller,Cruiser,44608
Tristan,Frigate,100919
Capsule,Capsule,158907

Again, Frigates are second only to Capsules (every pilot has a capsule to fly when his ship gets blown apart, BUT the capsule can be blown apart too, so I guess the Capsule is the most flown “ship” in EVE!) in number of ship lost.

That’s it for now, was a nice excercise to test out q a bit :)

Fly safe! o/