• Welcome to the Speedsolving.com, home of the web's largest puzzle community!
    You are currently viewing our forum as a guest which gives you limited access to join discussions and access our other features.

    Registration is fast, simple and absolutely free so please, join our community of 40,000+ people from around the world today!

    If you are already a member, simply login to hide this message and begin participating in the community!

WCA Statistics Tools

Stefan

Member
Joined
May 7, 2006
Messages
7,280
WCA
2003POCH01
YouTube
Visit Channel
I improved my tool that I've been using to prepare WCA statistics for forum posts such as [post=1011290]Female Podiums[/post] or [post=1009075]FMC statistics per year[/post] and published it now:
https://github.com/pochmann/wca-statistics-tools

It automatically keeps a local database up to date (checking/downloading/importing the newest WCA export) and turns the queries you write in *.in textfiles into *.out textfiles that contain the results nicely formatted and documented, ready to copy&paste into the forum.

Would make me happy if it's useful for others as well and I'd appreciate feedback and suggestions for improvement (both for the tool as well as for its documentation).

Edit: I renamed it to "WCA Statistics Tools".
 
Last edited:

kinch2002

Premium Member
Joined
Dec 22, 2008
Messages
2,504
Location
Guildford! UK!
WCA
2009SHEP01
YouTube
Visit Channel
Nice idea!
I'll try to set it up for myself later...I'm not too good at this sort of thing, I'm used to being put in front of a computer with ms sql server and the databases already set up for me :)
But assuming I can set it up I'll be keen to use this.
 

Stefan

Member
Joined
May 7, 2006
Messages
7,280
WCA
2003POCH01
YouTube
Visit Channel
Rename it to something less confusing? I thought this was about/for the WCA forum at first.

Yeah I'm not happy with the name, either, and already considered renaming it. How about "WCA statistics forum tool" or "WCA-statistics forum-tool"? I'm also open for suggestions.

(I hope to eventually get around to make it a module useful for other programs and websites and then drop the "forum" from the name. And maybe add general cubing stuff and then change "WCA" into "Cubing".)
 

MaeLSTRoM

Member
Joined
Jan 7, 2011
Messages
1,862
Location
UK
WCA
2011WALL02
YouTube
Visit Channel
Yeah I'm not happy with the name, either, and already considered renaming it. How about "WCA statistics forum tool" or "WCA-statistics forum-tool"? I'm also open for suggestions.

WCA-Statistics forum post tool?

Anyway seems pretty cool, maybe I'll actually start doing some stats of my own now heh. (instead of just bugging Kinch2002 until he does it for me :p )
 

cubizh

Member
Joined
Oct 2, 2011
Messages
602
Location
Portugal
WCA
2014GOME07
YouTube
Visit Channel
Thanks for sharing!

A while ago I did something similar to automatically download and apply the new export only if a new version is available, checking first, to reduce bandwidth, but your code seems a lot better.
I ended up not really using it as I don't really use the sql data.

A slight improvement to your program would be to directly use the defined variables on top, something like:
Code:
 commandline='mysql --default-character-set=utf8 --host='+ host + ' --user=' + user + " --password=' + password + ' ' + database + '< WCA_export.sql'
 subprocess.call(commandline, shell=True)
 

Stefan

Member
Joined
May 7, 2006
Messages
7,280
WCA
2003POCH01
YouTube
Visit Channel
automatically download and apply the new export only if a new version is available, checking first, to reduce bandwidth

Actually I don't worry about bandwidth so much, I just don't want to spend 40 seconds on a useless download and import.

I ended up not really using it as I don't really use the sql data.

Do you use it for tsv?

For the longest time, my auto-updater was for tsv (part of another project). For sql and this tool I went a cumbersome way until I got fed up with that last night. I intend to post my tsv version as well, along with more helper functions.

A slight improvement to your program would be to directly use the defined variables on top, something like:

Ouch, yeah. Being able to reuse those values there was the point of putting them into variables, and then I forgot. Thanks.
 
Last edited:

Stefan

Member
Joined
May 7, 2006
Messages
7,280
WCA
2003POCH01
YouTube
Visit Channel
I renamed and improved it a bit. And here's a better demonstration of what it does:

You write a query file like this (current single record and record holders for each event):

Code:
SELECT eventId Event,
       best 'Record[R]',
       group_concat(personId separator ', ') 'Record Holders'
FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
     natural join Results
     join Events on Events.id = eventId
GROUP BY Event
ORDER BY rank;

My tool first makes sure your database is up to date (automatically updating if necessary) and then produces an output file like this:

Code:
[noparse][SPOILER="World Records"]Using data from [url=https://www.worldcubeassociation.org/results/misc/export.html]WCA_export473_20140916[/url] and Stefan's [url=https://github.com/pochmann/wca-statistics-tools/]WCA Statistics Tools[/url].

[TABLE="class:grid,align:left"]
[TR][TD][B]Event[/B][/TD][TD][B]Record[/B][/TD][TD][B]Record Holders[/B][/TD][/TR]
[TR][TD]Rubik's Cube[/TD][TD="align:right"]5.55[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2007VALK01]Mats Valk[/url][/TD][/TR]
[TR][TD]4x4 Cube[/TD][TD="align:right"]21.97[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2010WEYE02]Sebastian Weyer[/url][/TD][/TR]
[TR][TD]5x5 Cube[/TD][TD="align:right"]48.42[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009ZEMD01]Feliks Zemdegs[/url][/TD][/TR]
[TR][TD]2x2 Cube[/TD][TD="align:right"]0.69[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009KASE02]Christian Kaserer[/url][/TD][/TR]
[TR][TD]3x3 blindfolded[/TD][TD="align:right"]23.19[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2011KOWA01]Marcin Kowalczyk[/url][/TD][/TR]
[TR][TD]3x3 one-handed[/TD][TD="align:right"]9.03[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009ZEMD01]Feliks Zemdegs[/url][/TD][/TR]
[TR][TD]3x3 fewest moves[/TD][TD="align:right"]20[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009OKAY01]Tomoaki Okayama (岡山友昭)[/url][/TD][/TR]
[TR][TD]3x3 with feet[/TD][TD="align:right"]25.14[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2012CAMP03]Gabriel Pereira Campanha[/url][/TD][/TR]
[TR][TD]Megaminx[/TD][TD="align:right"]39.57[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2010CORM02]Louis Cormier[/url][/TD][/TR]
[TR][TD]Pyraminx[/TD][TD="align:right"]1.36[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2008ANDE02]Oscar Roth Andersen[/url][/TD][/TR]
[TR][TD]Square-1[/TD][TD="align:right"]6.96[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2008LIBI01]Bingliang Li (李炳良)[/url][/TD][/TR]
[TR][TD]Rubik's Clock[/TD][TD="align:right"]5.27[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009WANG19]Sam Zhixiao Wang (王志骁)[/url][/TD][/TR]
[TR][TD]Skewb[/TD][TD="align:right"]2.19[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009HARN01]Brandon Harnish[/url], [url=https://www.worldcubeassociation.org/results/p.php?i=2013KOSK01]Jonatan Kłosko[/url][/TD][/TR]
[TR][TD]6x6 Cube[/TD][TD="align:right"]1:40.86[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009HAYS01]Kevin Hays[/url][/TD][/TR]
[TR][TD]7x7 Cube[/TD][TD="align:right"]2:39.41[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2010CHEN20]Lin Chen (陈霖)[/url][/TD][/TR]
[TR][TD]4x4 blindfolded[/TD][TD="align:right"]2:25.27[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2012FROS01]Oliver Frost[/url][/TD][/TR]
[TR][TD]5x5 blindfolded[/TD][TD="align:right"]5:39.10[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2012JALO01]Grzegorz Jałocha[/url][/TD][/TR]
[TR][TD]3x3 multi blind[/TD][TD="align:right"]41/41 ([SIZE=1]54:14[/SIZE])[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2011KOWA01]Marcin Kowalczyk[/url][/TD][/TR]
[TR][TD]Rubik's Magic[/TD][TD="align:right"]0.69[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009WANG13]Yuxuan Wang (王宇轩)[/url][/TD][/TR]
[TR][TD]Master Magic[/TD][TD="align:right"]1.66[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2009WANG13]Yuxuan Wang (王宇轩)[/url][/TD][/TR]
[TR][TD]3x3 multi blind old[/TD][TD="align:right"]24/24 ([SIZE=1]2:15:57[/SIZE])[/TD][TD][url=https://www.worldcubeassociation.org/results/p.php?i=2007HABE01]Tim Habermaas[/url][/TD][/TR]
[/TABLE]

[SPOILER="SQL"][CODE]SELECT eventId Event,
       best 'Record[R]',
       group_concat(personId separator ', ') 'Record Holders'
FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
     natural join Results
     join Events on Events.id = eventId
GROUP BY Event
ORDER BY rank;
[/SPOILER][/noparse][/code][/spoiler]

You can then copy&paste that output into your forum post, and it will look like this:

Using data from WCA_export473_20140916 and Stefan's WCA Statistics Tools.

EventRecordRecord Holders
Rubik's Cube5.55Mats Valk
4x4 Cube21.97Sebastian Weyer
5x5 Cube48.42Feliks Zemdegs
2x2 Cube0.69Christian Kaserer
3x3 blindfolded23.19Marcin Kowalczyk
3x3 one-handed9.03Feliks Zemdegs
3x3 fewest moves20Tomoaki Okayama (岡山友昭)
3x3 with feet25.14Gabriel Pereira Campanha
Megaminx39.57Louis Cormier
Pyraminx1.36Oscar Roth Andersen
Square-16.96Bingliang Li (李炳良)
Rubik's Clock5.27Sam Zhixiao Wang (王志骁)
Skewb2.19Brandon Harnish, Jonatan Kłosko
6x6 Cube1:40.86Kevin Hays
7x7 Cube2:39.41Lin Chen (陈霖)
4x4 blindfolded2:25.27Oliver Frost
5x5 blindfolded5:39.10Grzegorz Jałocha
3x3 multi blind41/41 (54:14)Marcin Kowalczyk
Rubik's Magic0.69Yuxuan Wang (王宇轩)
Master Magic1.66Yuxuan Wang (王宇轩)
3x3 multi blind old24/24 (2:15:57)Tim Habermaas

Code:
[NOPARSE]SELECT eventId Event,
       best 'Record[R]',
       group_concat(personId separator ', ') 'Record Holders'
FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
     natural join Results
     join Events on Events.id = eventId
GROUP BY Event
ORDER BY rank;[/NOPARSE]

For comparison: You could run the query for example in the mysql command line and post it here in a [noparse]
Code:
...
[/noparse] tag, which would look like this:
Code:
mysql> SELECT eventId Event,
    ->        best 'Record[R]',
    ->        group_concat(personId separator ', ') 'Record Holders'
    -> FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
    ->      natural join Results
    ->      join Events on Events.id = eventId
    -> GROUP BY Event
    -> ORDER BY rank;
+--------+-----------+------------------------+
| Event  | Record[R] | Record Holders         |
+--------+-----------+------------------------+
| 333    |       555 | 2007VALK01             |
| 444    |      2197 | 2010WEYE02             |
| 555    |      4842 | 2009ZEMD01             |
| 222    |        69 | 2009KASE02             |
| 333bf  |      2319 | 2011KOWA01             |
| 333oh  |       903 | 2009ZEMD01             |
| 333fm  |        20 | 2009OKAY01             |
| 333ft  |      2514 | 2012CAMP03             |
| minx   |      3957 | 2010CORM02             |
| pyram  |       136 | 2008ANDE02             |
| sq1    |       696 | 2008LIBI01             |
| clock  |       527 | 2009WANG19             |
| skewb  |       219 | 2009HARN01, 2013KOSK01 |
| 666    |     10086 | 2009HAYS01             |
| 777    |     15941 | 2010CHEN20             |
| 444bf  |     14527 | 2012FROS01             |
| 555bf  |     33910 | 2012JALO01             |
| 333mbf | 580325400 | 2011KOWA01             |
| magic  |        69 | 2009WANG13             |
| mmagic |       166 | 2009WANG13             |
| 333mbo | 750815700 | 2007HABE01             |
+--------+-----------+------------------------+

Benefits of my tool:
  • Automatically keeps your database up to date.
  • Adds some database indexes to speed up queries.
  • Formats the query results for nice copy&paste-ing here.
    • Builds a [noparse]
      ...
      [/noparse] from the data.
    • Turns IDs into names or even links.
    • Formats results (e.g., 750815700 becomes "24/24 (2:15:57)").
    • Right-aligns numbers and formatted numbers.
    • Creates a titled spoiler (nice because most of our statistics are long).
    • Mentions the WCA export that was used.
    • Includes the query under the results (so others can check it, learn from it, use it).
  • Recompute an old statistic with current data simply by deleting the .out file and running the tool.
The mysql command line tool also does tables and right-align, though not as good. I don't know what other tools (e.g., Excel using the .tsv export) can do for you, as I prefer to work with SQL (and Python, more on that in future versions).
 
Last edited:

Stefan

Member
Joined
May 7, 2006
Messages
7,280
WCA
2003POCH01
YouTube
Visit Channel
Using these two files (I did it with files because the mysql command line window turned non-Latin characters into question marks):

mysql.bat:
Code:
mysql.exe -t --default-character-set=utf8 -hlocalhost -uwca_export -pXXX wca_export < mysql.in > mysql.out

mysql.in:
Code:
SELECT [COLOR="#FF0000"]cellName[/COLOR] Event,
       best 'Record[R]',
       group_concat([COLOR="#FF0000"]Persons.name[/COLOR] separator ', ') 'Record Holders'
FROM (SELECT eventId, min(best) best FROM Results WHERE best>0 GROUP by eventId) tmp
     natural join Results
     join Events on Events.id = eventId
     [COLOR="#FF0000"]join Persons on Persons.id = personId and subId = 1[/COLOR]
GROUP BY Event
ORDER BY rank;

Now it shows the event and person names, although at the cost of a longer and in this case significantly slower query. Also, the non-Latin characters mess up the table lines.

mysql.out:
Code:
+---------------------+-----------+----------------------------------+
| Event               | Record[R] | Record Holders                   |
+---------------------+-----------+----------------------------------+
| Rubik's Cube        |       555 | Mats Valk                        |
| 4x4 Cube            |      2197 | Sebastian Weyer                  |
| 5x5 Cube            |      4842 | Feliks Zemdegs                   |
| 2x2 Cube            |        69 | Christian Kaserer                |
| 3x3 blindfolded     |      2319 | Marcin Kowalczyk                 |
| 3x3 one-handed      |       903 | Feliks Zemdegs                   |
| 3x3 fewest moves    |        20 | Tomoaki Okayama (岡山友昭)       |
| 3x3 with feet       |      2514 | Gabriel Pereira Campanha         |
| Megaminx            |      3957 | Louis Cormier                    |
| Pyraminx            |       136 | Oscar Roth Andersen              |
| Square-1            |       696 | Bingliang Li (李炳良)            |
| Rubik's Clock       |       527 | Sam Zhixiao Wang (王志骁)        |
| Skewb               |       219 | Brandon Harnish, Jonatan Kłosko  |
| 6x6 Cube            |     10086 | Kevin Hays                       |
| 7x7 Cube            |     15941 | Lin Chen (陈霖)                  |
| 4x4 blindfolded     |     14527 | Oliver Frost                     |
| 5x5 blindfolded     |     33910 | Grzegorz Jałocha                 |
| 3x3 multi blind     | 580325400 | Marcin Kowalczyk                 |
| Rubik's Magic       |        69 | Yuxuan Wang (王宇轩)             |
| Master Magic        |       166 | Yuxuan Wang (王宇轩)             |
| 3x3 multi blind old | 750815700 | Tim Habermaas                    |
+---------------------+-----------+----------------------------------+
 
Last edited:
Top