• 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 Website

Mr.Toad

Member
Joined
Jul 26, 2009
Messages
82
Also, my stats are divided by Brooks and Searle. I don't really care all that much, but it would be nice if this was fixed. I also seem to be showing up under the last name Searle instead of Brooks in most of the statistics.
I'll try to figure this out.
I just saw this, to solve it you just need to remove the bit in red from the query:

Code:
group by personId[U][COLOR="red"], personName[/COLOR][/U]
 

macky

Premium Member
Joined
Apr 4, 2006
Messages
402
Location
Stanford, CA
WCA
2003MAKI01
I just saw this, to solve it you just need to remove the bit in red from the query:

Code:
group by personId[U][COLOR="red"], personName[/COLOR][/U]

Yes, I should remove that either way, but that shows him as "Anthony Searle" because that's the first name encountered with his WCA id. Either I find a way to have MySQL go through the data from the latest row, or I need to use subid=1 somewhere (maybe subquery, maybe in the php).
 
Last edited:

Carson

Premium Member
Joined
Feb 1, 2008
Messages
1,285
Location
Somerset, Kentucky, United States
WCA
2008PENT01
An interesting thought:

It would be neat to have a list of the average time required to win each event each year that a competition has taken place. I'm not sure if I am explaining this well... the average winning time of each occurrence of a specific event during the course of a year.
 

Tim Reynolds

Premium Member
Joined
Jun 28, 2006
Messages
995
Location
Boston, MA
WCA
2005REYN01
YouTube
Visit Channel
An interesting thought:

It would be neat to have a list of the average time required to win each event each year that a competition has taken place. I'm not sure if I am explaining this well... the average winning time of each occurrence of a specific event during the course of a year.

Code:
	2011		2010		2009		2008		2007		2006		2005		2004
333	11.299		11.900 		13.118		13.224		14.178		15.659		17.934		19.396
444	52.164		54.928		58.553		1:04.550	1:11.277	1:16.532	1:30.212	1:31.624
555	1:36.436	1:36.298	1:41.308	1:54.779	2:09.807	2:28.572	3:02.212	2:54.018
222	3.818		4.147		5.114		5.163		6.288		7.404		8.881		10.870
333bf	1:52.778	1:50.932	1:54.976	1:58.020	2:27.421	3:19.773	5:42.239	10:59.078
333oh	20.304		22.107		24.140		25.470		27.079		32.113		44.459		43.515
333fm	32.7		33.1		34.0		34.1		35.5		31.3		35.0		31.0
333ft	1:30.620	1:24.716	1:35.776	1:37.772	1:18.426	2:56.020
minx	1:26.599	1:29.640	1:40.590	1:51.690	1:50.584	2:06.223
pyram	6.709		7.318		8.422		8.906		10.923		11.911		14.193
sq1	24.774		25.596		25.850		30.769		33.249		43.849		48.855		33.210
clock	12.458		12.540		12.051		14.443		12.748		14.089		14.100		11.767
666	3:07.895	3:15.692	3:30.542
777	4:51.563	4:51.234	5:18.497
magic	1.346		1.371		1.434		1.486		1.554		1.814		2.399		2.000
mmagic	3.476		3.460		4.009		3.600		3.470		3.873		4.003
444bf	9:31.138	12:50.516	14:09.820	13:55.358	25:36.573	10:08.450	n/a		19:14.000
555bf	20:18.784	21:48.800	24:49.000	28:29.500	40:48.000	38:02.667	n/a		2:34:36.000

There might be some mistakes, I converted the times by hand.
 

Carson

Premium Member
Joined
Feb 1, 2008
Messages
1,285
Location
Somerset, Kentucky, United States
WCA
2008PENT01
What about a ranking of rolling means of 100 during competitions for 3x3x3, not including DNF's and not throwing out best or worst times? This would be based on the persons "current" rolling mean instead of their best rolling mean.


Edit: Also, what about a ranking of those people with the most lines of text on their WCA profile. I'm this could be done using the database without actually polling the site, but I would guess it would be a little more complex.
 
Last edited:

macky

Premium Member
Joined
Apr 4, 2006
Messages
402
Location
Stanford, CA
WCA
2003MAKI01
We'll get stats.cubing.net working again, but it'll take some time to do it well. The hosting service shut down the database because some queries were taking too long, for reasons I don't understand. We have to improve the queries (probably using many pre-computed tables) and test them before we can put them back online without risking getting Lucas in trouble.

It's too big of a project for me alone; I need to do math. If you have knowledge about MySQL/php and would like to lead the project or help maintain it, let me know at Gmail account smakisumi.

As a note, all the code is on github: https://github.com/cubing/stats
 

Stefan

Member
Joined
May 7, 2006
Messages
7,280
WCA
2003POCH01
YouTube
Visit Channel
I don't have my new system set up yet and I'm not familiar with git(hub), but here's my code suggestion for most_solve_hours.php, maybe someone else wants to integrate it into the repository (if not, I'll do it, but I don't know when). Or maybe it can just serve as an example for how to precompute and use stuff later. I tested this code when I wrote it recently, and it did speed up the very slow original query a lot.

Do this once after every update/import of the database:
Code:
DROP TABLE HelperMostSolveHours;

CREATE TABLE HelperMostSolveHours
SELECT   personId, personCountryId, continentId, year,
       (sum( if(value1>0,value1,0) ) +
        sum( if(value2>0,value2,0) ) +
        sum( if(value3>0,value3,0) ) +
        sum( if(value4>0,value4,0) ) +
        sum( if(value5>0,value5,0) ))/100/60/60 hours,
        personName
FROM     Results
       JOIN Events      ON Events.id = eventId
       JOIN Competitions ON Results.competitionId = Competitions.id
       JOIN Countries    ON Results.countryId = Countries.id
WHERE    format = 'time'
GROUP BY personId, Results.countryId, year;

Use this (well, with the dynamic conditions instead of hardcoded year<=2008 etc, of course) in the viewer script:
Code:
SELECT   sum(hours) hours, personId, personName, personCountryId
FROM     HelperMostSolveHours
WHERE    continentId = '_Europe'
 AND    year <= 2008
GROUP BY personId
ORDER BY hours DESC
LIMIT    100;

Some timings on my PC:

Original query: 15-17 seconds

Original query simply without the "LIMIT 100": 1.0-1.7 seconds
(Very interesting, somehow telling it to produce less made it a lot slower... apparently the overhead of trying to do less work actually made it a lot *more* work.)

Precomputation: 3 seconds
Query using the precomputed table: 0.1-0.2 seconds
 
Top