Top 10 collection completion - a monster in-query formula in MySQL?
Posted
by Andrew Heath
on Stack Overflow
See other posts from Stack Overflow
or by Andrew Heath
Published on 2010-04-12T03:08:57Z
Indexed on
2010/04/12
3:13 UTC
Read the original article
Hit count: 384
I've got the following tables:
User Basic Data (unique)
[userid] [name] [etc]
User Collection (one to one)
[userid] [game]
User Recorded Plays (many to many)
[userid] [game] [scenario] [etc]
Game Basic Data (unique)
[game] [total_scenarios]
I would like to output a table that shows the collection play completion percentage for the Top 10 users in descending order of %:
Output Table
[userid] [collection_completion]
3 95%
1 81%
24 68%
etc etc
In my mind, the calculation sequence for ONE USER is:
- grab user's total owned scenarios from
User Collection
joined withGame Basic Data
andCOUNT(gbd.total_scenarios)
- grab all recorded plays by
COUNT(DISTINCT scenario)
for that user - Divide all recorded plays by total owned scenarios
So that's 2 queries and a little PHP massage at the end. For a list of users sorted by completion percentage things get a little more complicated.
I figure I could grab all users' collection totals in one query, and all users recorded plays in another, and then do the calcs and sort the final array in PHP, but it seems like overkill to potentially be doing all that for 1000+ users when I only ever want the Top 10.
Is there a wicked monster query in MySQL that could do all that and LIMIT 10
? Or is sticking with PHP handling the bulk of the work the way to go in this case?
© Stack Overflow or respective owner