MySQL query to create a rank chart over time

Question

I have a table with statistic data (roughly 100,000 rows) that has the following format:

WeekNum   Name     Value   Category
201751    Joe      15      X
201751    Max      23      X 
201751    Jim      7       X
201752    Joe      18      X
201752    John     17      X
201752    Max      31      X
201752    Jim      13      X
etc.

Now I would like to create a line chart with Google Charts to show the Top 10 over time. I want to create a JSON that then feeds the Google Chart API. The chart should look something like this:

How do I do the query to get the top 10 for each week? I came across some Rank questions here and adapted my query like this:

SELECT S.WeekNum, S.Name, S.Value, @curRank := @curRank + 1 AS Rank
FROM table S, (SELECT @curRank := 0) R
WHERE S.Category = 'X'
ORDER BY S.WeekNum, S.Value DESC

But this ranks all the records and I get back hundreds of records. Is it possible to get only the Top 10 for each week, and then do the ranking?

Thanks in advance for your help.

Do you want the top 10 per user per week, or just the top 10 per week among all users? — Giorgos Betsos, Feb 12 '18 at 14:02
I want the top 10 for each week over the whole period of time. Each user has only one row per week. — Matth, Feb 12 '18 at 14:04

score 1 · Answer 1 · answered Feb 12 '18 at 14:09

You can use the following query:

SELECT WeekNum, Name, Value, 
       @rn := IF( @week = WeekNum, @rn + 1, IF(@week := WeekNum, 1, 1)) AS rn
FROM mytable
CROSS JOIN (SELECT @week := 0, @rn :=0) As v
ORDER BY WeekNum, Value DESC

in order to simulate ROW_NUMBER() OVER (PARTITION BY ... ORDER BY) window function not available in MySQL.

Output from above query:

WeekNum Name    Value   rn
---------------------------
201751  Max     23      1
201751  Joe     15      2
201751  Jim     7       3
201752  Max     31      1
201752  Joe     18      2
201752  John    17      3
201752  Jim     13      4

Field rn returns the ranking value within each week slice. It is being reset for every consecutive week.

Demo here

You can now consume the results of the above query in order to extract the top 10 records for each week:

SELECT @rnk := @rnk + 1
FROM (
   ... above query here ...
) AS t1
CROSS JOIN (SELECT @rnk := 0) AS t2
WHERE @rn <= 10

The above query uses @rnk variable in order to calculate the rank over all records returned by the subquery.

Awesome, that's it. I used your first query and put the following wrapper around it. And that is exactly what I wanted: [SELECT WeekNum, Name, Rank FROM ( SELECT WeekNum, Name, Value,... ORDER BY WeekNum, Value DESC) T WHERE Rank <= 10] — Matth, Feb 12 '18 at 14:20

MySQL query to create a rank chart over time

1 Answers1