commit 7fbc673eb6a5de60d52cb5c828e1b999526e704a
parent 15ff5b93570fa99173a21ce93ef33ecb3163dbc6
Author: alex wennerberg <alex@alexwennerberg.com>
Date: Tue, 13 Feb 2024 21:03:37 -0500
cleanup
Diffstat:
3 files changed, 4 insertions(+), 277 deletions(-)
diff --git a/getdata.py b/getdata.py
@@ -22,7 +22,7 @@ def main():
for n, file in enumerate(get_round_fileids()):
if file in cache:
continue
- if file not in cache:
+ if file not in cache and file != "1LGHvTrQz2zhBjz1PhlW61ZYmwRWJyWkEHj-png7ZKdw": # misc file
print(f"analyzing round {n+1}")
save_round(n+1, file)
diff --git a/sql/delete-last.sql b/sql/delete-last.sql
@@ -0,0 +1,3 @@
+delete from round where id > 50;
+delete from deck where round > 50;
+delete from match where round > 50;
diff --git a/sql/workbook.md b/sql/workbook.md
@@ -1,276 +0,0 @@
-### Most popular cards (top 50)
-```sql
-select name,count(1) from card group by 1 order by 2 desc limit 50;
-```
-```
-+-------------------------+----------+
-| name | count(1) |
-+-------------------------+----------+
-| Black Lotus | 104 |
-| Chancellor of the Forge | 74 |
-| City of Traitors | 73 |
-| Chancellor of the Annex | 70 |
-| Mox Pearl | 64 |
-| Memnite | 64 |
-| Thassa's Oracle | 50 |
-| Wasteland | 44 |
-| Swamp | 43 |
-| Icatian Store | 42 |
-| Mox Sapphire | 41 |
-| Lion's Eye Diamond | 39 |
-| Karakas | 39 |
-| Mental Misstep | 34 |
-| Hickory Woodlot | 34 |
-| Leyline of Anticipation | 33 |
-| Force of Will | 30 |
-| Crystal Vein | 30 |
-| Mana Crypt | 29 |
-| Cavern of Souls | 29 |
-| Emrakul, the Aeons Torn | 28 |
-| Dryad Arbor | 28 |
-| Channel | 28 |
-| Dwarven Hold | 27 |
-| Crashing Footfalls | 27 |
-| Chronomaton | 27 |
-| Mountain | 26 |
-| Lotus Bloom | 26 |
-| Mishra's Workshop | 24 |
-| Mishra's Factory | 24 |
-| Forest | 24 |
-| Strip Mine | 23 |
-| Death's Shadow | 23 |
-| Student of Warfare | 22 |
-| Saprazzan Cove | 22 |
-| Force of Negation | 22 |
-| Plains | 21 |
-| Hexdrinker | 21 |
-| Thoughtseize | 19 |
-| Leyline of Singularity | 19 |
-| Elite Spellbinder | 19 |
-| Chalice of the Void | 19 |
-| Blackmail | 19 |
-| Undiscovered Paradise | 18 |
-| Steel Overseer | 18 |
-| Shadowgrange Archfiend | 18 |
-| Sand Silos | 18 |
-| Cenn's Tactician | 18 |
-| Burning Inquiry | 18 |
-| The Rack | 17 |
-+-------------------------+----------+
-```
-### Best decks, played in at least 30 games
-```sql
-select deck, cast(avg(score) * 100 as int) / 100.0 as avgscore, count(1) from
-(select round, player, group_concat(name, ';') as deck
-from (select * from card order by round,player,name)
-group by 1,2) as deck
-join match on deck.round = match.round and deck.player = match.player
-group by 1
-having count(1) > 30
-order by 2 desc;
-```
-```
-+------------------------------------------------------------+----------+----------+
-| deck | avgscore | count(1) |
-+------------------------------------------------------------+----------+----------+
-| Cenn's Tactician;Karakas;Leyline of Singularity | 4.77 | 36 |
-| Crystal Vein;Mox Emerald;Words of Wilding | 4.31 | 35 |
-| Isochron Scepter;Mana Crypt;Verdant Command | 3.93 | 32 |
-| Death's Shadow;Tarnished Citadel;Thoughtseize | 3.86 | 83 |
-| Mana Crypt;Phyrexian Dreadnought;The Mycosynth Gardens | 3.74 | 35 |
-| Mox Pearl;Rishadan Port;Skrelv's Hive | 3.7 | 54 |
-| Chancellor of the Annex;Chancellor of the Forge;Strip Mine | 3.66 | 62 |
-| Chancellor of the Annex;Icatian Store;Strip Mine | 3.44 | 79 |
-| Cenn's Tactician;Curse of Silence;Plains | 3.44 | 54 |
-| City of Traitors;Magus of the Moon;Mox Ruby | 3.4 | 47 |
-| Black Lotus;Black Lotus;Minsc & Boo, Timeless Heroes | 3.4 | 52 |
-| Crashing Footfalls;Dismember;Forest | 3.37 | 45 |
-| Disrupting Shoal;Evermind;Memnite | 3.3 | 36 |
-| Autonomous Assembler;Mishra's Factory;Mox Pearl | 3.25 | 36 |
-| Chalice of the Void;City of Traitors;Hangarback Walker | 3.2 | 64 |
-| Emrakul, the Aeons Torn;Lion's Eye Diamond;Shelldock Isle | 3.17 | 93 |
-| Chancellor of the Tangle;Crashing Footfalls;Force of Vigor | 3.17 | 67 |
-| Mana Crypt;Trinisphere;Urza's Saga | 3.16 | 36 |
-| Force of Despair;Memnite;Unmask | 3.14 | 49 |
-| Barren Glory;Lotus Bloom;Lotus Bloom | 3.14 | 85 |
-| Black Lotus;Black Lotus;Steel Seraph | 3.13 | 46 |
-| Retrofitter Foundry;Sol Ring;Wasteland | 3.11 | 69 |
-| Cenn's Tactician;Concealed Courtyard;Thoughtseize | 3.11 | 36 |
-| Island;Laboratory Maniac;Mana Crypt | 3.08 | 35 |
-| Dark Depths;Urborg, Tomb of Yawgmoth;Vampire Hexmage | 3.05 | 73 |
-+------------------------------------------------------------+----------+----------+
-```
-### Best decks, played in at least 30 games, filter bans
-
-### Avg card scores, played in at least 100 games
-
-```sql
-select name, cast(avg(score) * 100 as int) / 100.0 as score, count(1) from card
-join match on card.round = match.round and card.player = match.player
-group by 1
-having count(1) > 100
-order by 2 desc
-```
-
-```
-+-------------------------------------+-------+----------+
-| name | score | count(1) |
-+-------------------------------------+-------+----------+
-| Wild Magic Surge | 3.63 | 102 |
-| Mayor of Avabruck // Howlpack Alpha | 3.58 | 116 |
-| Ratchet Bomb | 3.54 | 135 |
-| Rishadan Port | 3.41 | 217 |
-| Barren Glory | 3.4 | 198 |
-| Curse of Silence | 3.39 | 190 |
-| Minsc & Boo, Timeless Heroes | 3.37 | 131 |
-| Thoughtseize | 3.35 | 515 |
-| Cenn's Tactician | 3.34 | 378 |
-| Strip Mine | 3.28 | 398 |
-| Retrofitter Foundry | 3.28 | 285 |
-| The Rack | 3.26 | 387 |
-| Tarnished Citadel | 3.26 | 336 |
-| Stromkirk Noble | 3.24 | 254 |
-| Steel Overseer | 3.21 | 464 |
-| Luminarch Aspirant | 3.19 | 121 |
-| Gisa's Bidding | 3.19 | 168 |
-| Angel of Sanctions | 3.19 | 151 |
-| Phyrexian Dreadnought | 3.16 | 139 |
-| Skrelv's Hive | 3.15 | 164 |
-| Unmask | 3.14 | 122 |
-| Force of Despair | 3.11 | 185 |
-| Chalice of the Void | 3.11 | 335 |
-| Dark Depths | 3.1 | 187 |
-| Sandstone Needle | 3.09 | 166 |
-| Sol Ring | 3.08 | 382 |
-| Disrupting Shoal | 3.08 | 149 |
-| Death's Shadow | 3.08 | 517 |
-| Mox Emerald | 3.07 | 248 |
-| Boseiju, Who Endures | 3.07 | 198 |
-| Vampire Hexmage | 3.06 | 123 |
-| Solitude | 3.05 | 130 |
-| Noxious Revival | 3.05 | 187 |
-| Chancellor of the Annex | 3.04 | 1005 |
-| Bayou | 3.04 | 120 |
-| Isochron Scepter | 3.03 | 111 |
-| Mana Crypt | 3.02 | 634 |
-| Lotus Bloom | 3.02 | 601 |
-| Hangarback Walker | 3.02 | 433 |
-| Show and Tell | 3.01 | 186 |
-| Overgrown Tomb | 3.0 | 177 |
-| Magus of the Moon | 3.0 | 244 |
-| Daze | 3.0 | 120 |
-| Crashing Footfalls | 2.98 | 662 |
-| Shrieking Affliction | 2.97 | 113 |
-| Anointed Peacekeeper | 2.97 | 174 |
-| Sheltered Valley | 2.95 | 133 |
-| Grief | 2.95 | 172 |
-| Crystal Vein | 2.95 | 568 |
-| Thassa's Oracle | 2.94 | 742 |
-| Blackmail | 2.94 | 386 |
-| Inkmoth Nexus | 2.93 | 298 |
-| Tropical Island | 2.92 | 194 |
-| Funeral Charm | 2.92 | 118 |
-| Mishra's Factory | 2.9 | 508 |
-| Leyline of Singularity | 2.9 | 340 |
-| City of Traitors | 2.9 | 1413 |
-| Cavern of Souls | 2.9 | 460 |
-| Alpine Moon | 2.9 | 275 |
-| Mind Swords | 2.89 | 283 |
-| Force of Vigor | 2.89 | 144 |
-| Dismember | 2.89 | 157 |
-| Trinisphere | 2.88 | 194 |
-| Autonomous Assembler | 2.88 | 233 |
-| Snapback | 2.87 | 213 |
-| Piracy Charm | 2.87 | 110 |
-| Phyrexian Revoker | 2.87 | 107 |
-| Urza's Saga | 2.86 | 304 |
-| Ulamog, the Infinite Gyre | 2.86 | 301 |
-| Swarm Shambler | 2.86 | 303 |
-| Shelldock Isle | 2.86 | 156 |
-| Oboro, Palace in the Clouds | 2.86 | 264 |
-| Dwarven Hold | 2.85 | 405 |
-| Mox Pearl | 2.83 | 1512 |
-| Undiscovered Paradise | 2.82 | 462 |
-| Mana Vault | 2.82 | 168 |
-| Swamp | 2.81 | 795 |
-| Oko, Thief of Crowns | 2.81 | 315 |
-| Interplanar Beacon | 2.8 | 237 |
-| Icatian Store | 2.8 | 757 |
-| Urborg, Tomb of Yawgmoth | 2.79 | 143 |
-| Sand Silos | 2.79 | 340 |
-| Blooming Marsh | 2.79 | 260 |
-| Mutavault | 2.78 | 121 |
-| Meddling Mage | 2.78 | 144 |
-| Cabal Therapy | 2.78 | 296 |
-| Flash | 2.77 | 134 |
-| Cathedral of War | 2.77 | 203 |
-| The Tabernacle at Pendrell Vale | 2.76 | 123 |
-| Dark Ritual | 2.75 | 182 |
-| Mox Jet | 2.74 | 322 |
-| Hexdrinker | 2.74 | 513 |
-| Saprazzan Skerry | 2.73 | 215 |
-| Laboratory Maniac | 2.73 | 167 |
-| Emrakul, the Aeons Torn | 2.73 | 492 |
-| Island Sanctuary | 2.72 | 110 |
-| Student of Warfare | 2.71 | 510 |
-| Mox Sapphire | 2.7 | 775 |
-| Black Lotus | 2.7 | 1419 |
-| Balance | 2.7 | 271 |
-| Force of Will | 2.69 | 354 |
-| Burning Inquiry | 2.69 | 314 |
-| Memnite | 2.68 | 1139 |
-| Lion's Eye Diamond | 2.68 | 757 |
-| Chancellor of the Forge | 2.68 | 1172 |
-| Sphere of Resistance | 2.67 | 115 |
-| Island | 2.67 | 213 |
-| Chancellor of the Tangle | 2.67 | 256 |
-| Fury | 2.66 | 271 |
-| Chronomaton | 2.66 | 552 |
-| Misdirection | 2.65 | 127 |
-| Mind Rake | 2.65 | 138 |
-| Mishra's Workshop | 2.63 | 384 |
-| Maze of Ith | 2.63 | 258 |
-| Fountain of Cho | 2.63 | 206 |
-| Simian Spirit Guide | 2.62 | 283 |
-| Young Wolf | 2.6 | 170 |
-| Mountain | 2.6 | 421 |
-| Karakas | 2.6 | 580 |
-| Nether Spirit | 2.59 | 321 |
-| Hickory Woodlot | 2.59 | 612 |
-| Ghost Quarter | 2.59 | 257 |
-| Force of Negation | 2.59 | 361 |
-| Elite Spellbinder | 2.59 | 386 |
-| Wasteland | 2.58 | 847 |
-| Dryad Arbor | 2.56 | 364 |
-| Dunes of the Dead | 2.55 | 173 |
-| Underground River | 2.53 | 105 |
-| Plains | 2.52 | 376 |
-| Energy Field | 2.52 | 238 |
-| Saprazzan Cove | 2.51 | 266 |
-| Jace, Wielder of Mysteries | 2.5 | 124 |
-| Bottomless Vault | 2.49 | 153 |
-| Nullhide Ferox | 2.45 | 232 |
-| Remote Farm | 2.44 | 157 |
-| Forest | 2.44 | 418 |
-| Darksteel Citadel | 2.44 | 181 |
-| Karn Liberated | 2.42 | 106 |
-| Shadowgrange Archfiend | 2.4 | 367 |
-| Mox Ruby | 2.38 | 110 |
-| Channel | 2.38 | 435 |
-| Sea's Claim | 2.37 | 182 |
-| Old-Growth Dryads | 2.34 | 330 |
-| Smallpox | 2.33 | 104 |
-| Hollow Trees | 2.32 | 246 |
-| Thalia, Guardian of Thraben | 2.3 | 156 |
-| Lavinia, Azorius Renegade | 2.29 | 127 |
-| Blazing Rootwalla | 2.29 | 163 |
-| Treetop Village | 2.27 | 120 |
-| Swords to Plowshares | 2.27 | 159 |
-| Pyrokinesis | 2.22 | 116 |
-| Mental Misstep | 2.13 | 616 |
-| Leyline of Anticipation | 2.09 | 413 |
-| Bitterblossom | 2.06 | 149 |
-| Peat Bog | 1.8 | 139 |
-| Leyline of Sanctity | 1.47 | 183 |
-+-------------------------------------+-------+----------+
-```
