61 lines
2.8 KiB
Markdown
61 lines
2.8 KiB
Markdown
# smogon stats
|
|
turn <https://smogon.com/stats/> chaos json files into [SQLite](https://sqlite.org) databases
|
|
|
|
you can find pre-generated dbs at <https://pyrope.net/mon/stats>, and you can [fiddle](https://sqlite.org/fiddle) with them in your browser or on your own computer.
|
|
|
|
## examples (2025-02)
|
|
```sh
|
|
smogon-stats gen9ou-1500.json -o gen9ou-1500.sqlite
|
|
sqlite3 gen9ou-1500.sqlite -markdown "SELECT name, format('%.2f%%', usage * 100) as usage FROM mon WHERE mon.usage > 0.04 ORDER BY mon.usage DESC LIMIT 10"
|
|
```
|
|
output:
|
|
|
|
| name | usage |
|
|
|--------------------|--------|
|
|
| Great Tusk | 33.05% |
|
|
| Kingambit | 23.75% |
|
|
| Gholdengo | 21.98% |
|
|
| Iron Valiant | 18.95% |
|
|
| Dragapult | 16.96% |
|
|
| Dragonite | 15.53% |
|
|
| Raging Bolt | 14.82% |
|
|
| Ogerpon-Wellspring | 14.77% |
|
|
| Iron Moth | 14.46% |
|
|
| Slowking-Galar | 14.26% |
|
|
|
|
you can also use SQLite's [`.expert`](https://sqlite.org/cli.html#index_recommendations_sqlite_expert_) to find indexes that can dramatically speed up queries, but the numbers are small enough that it likely won't matter except for exploratory stuff—even `100^3` is only `1_000_000`.
|
|
|
|
you can use SQLite's [`.excel`](https://sqlite.org/cli.html#_export_to_excel_) to open the result of the next query in a spreadsheet application, so here are some fun graphs with the commands that generated the data.
|
|
the smogon data is from `wget https://smogon.com/stats/2025-02/chaos/gen9ou-{0,1500,1695}.json`:
|
|
|
|
(apologies for the surely terrible SQL—the point of this is that you can do your own queries :P)
|
|
|
|
### usage by elo
|
|
```sql
|
|
attach 'gen9ou-0.sqlite' as ou0; attach 'gen9ou-1500.sqlite' as ou1500; attach 'gen9ou-1695.sqlite' as ou1695;
|
|
|
|
SELECT ou0.mon.name, ou0.mon.usage as usage0, ou1500.mon.usage as usage1500, ou1695.mon.usage as usage1695
|
|
FROM ou0.mon
|
|
JOIN ou1500.mon on ou0.mon.name = ou1500.mon.name
|
|
JOIN ou1695.mon on ou0.mon.name = ou1695.mon.name
|
|
WHERE ou0.mon.usage > 0.03
|
|
ORDER BY ou0.mon.usage DESC;
|
|
```
|
|
|
|
|
|
### top 50 moves (1695)
|
|
```sql
|
|
SELECT m.name, mon.usage * m.usage AS usage_adj
|
|
FROM move m
|
|
JOIN mon ON mon.name = m.mon
|
|
GROUP BY usage_adj
|
|
ORDER BY usage_adj
|
|
DESC limit 50;
|
|
```
|
|
|
|
|
|
|
|
### "win" and "fail" heuristics (1695)
|
|
see `examples/win-fail.sql` for source and explanation (i could see this heuristic being improved in the future!).
|
|
|