Sql total plays daily for top played songs in the last 30 days-CodePudding

I am using mysql 10.4.13-MariaDB.

I am using this to get top 10 played songs today.

SELECT media_id, artist, title, SUM(c_play) AS total_count 
FROM table 
WHERE c_date > NOW() - INTERVAL 30 DAY 
GROUP BY media_id media_id, artist, title 
HAVING SUM(c_play) > 0 
ORDER BY total_count DESC
LIMIT 0,10

this would return 10 results

song a (artist, title) - 22 plays
song b (artist, title) - 17 plays
song c (artist, title) - 11 plays
....

this works well.

I would like to get total plays daily for top 10 played songs in the last 30 days. So for example total plays today (for top 10 songs) was 41. Yesterday 38, the day before 56 etc...

I tried:

SELECT date(c_date) AS stat_day, media_id, artist, title, SUM(c_play) AS total_count 
FROM table 
WHERE c_date > NOW() - INTERVAL 30 DAY 
GROUP BY date(c_date)
HAVING SUM(c_play) > 0 
ORDER BY stat_day DESC
LIMIT 0,10

but I am not getting expected results. The problem is I want to return 30 results (for each day for last 30 days) and I want to get group count of top 10 songs played.

CodePudding user response：

You want a Windowing Function for this, which is a fairly recent addition to the MySql ecosystem (other databases have had them since 2012).

Try this:

SELECT *
FROM (
    SELECT date(c_date) AS stat_day, media_id, artist, title, SUM(c_play) AS total_count 
       , row_number() over (partition by date(c_date) order by sum(c_play) desc) as 
    rn
    FROM table 
    WHERE c_date > NOW() - INTERVAL 30 DAY 
    GROUP BY date(c_date), media_id, artist, title
    HAVING SUM(c_play) > 0 
) t
WHERE t.rn <= 10

If you only care about totals for the whole day, you can also do this:

SELECT stat_day, sum(total_count) as total_count
FROM (
    SELECT date(c_date) AS stat_day, media_id, artist, title, SUM(c_play) AS total_count 
       , row_number() over (partition by date(c_date) order by sum(c_play) desc) as 
    rn
    FROM table
    WHERE c_date > NOW() - INTERVAL 30 DAY 
    GROUP BY date(c_date), media_id, artist, title
    HAVING SUM(c_play) > 0 
) t
WHERE t.rn <= 10
GROUP BY stat_day
ORDER BY stat_day

Update: My first read of the question made me believe we wanted the top 10 songs from each day (a different set of 10 each day). A second look makes me understand we want the top 10 songs from the last 30 days combined (one set of 10 songs total), and to know the daily play count for each of those songs.

That will look more like this:

SELECT date(t2.c_date), t2.media_id, t2.artist, t2.title, SUM(t2.c_play) as Total_count
FROM (
    /* Media from last 30 days, numbered by play count */
    SELECT media_id, row_number() over (order by sum(c_play) desc) as rn
    FROM table 
    WHERE c_date > NOW() - INTERVAL 30 DAY 
    GROUP BY media_id
    HAVING SUM(c_play) > 0 
) t
 /* Now look at the play data again from this time period for these same songs */
INNER JOIN table t2 ON t2.media_id = t.media_id
    AND t2.c_date > NOW() - INTERVAL 30 DAY 
WHERE t.rn <= 10 /* Restrict to top 10 songs */
/* and group by day this time */
GROUP BY date(t2.c_date), t2.media_id, t2.artist, t2.title

Depending on your version of MariaDB/MySql and their configuration, you might be able to put he ORDER BY LIMIT 10 back into the inner query, instead of row_number() AND t.rn <= 10 as shown, and it might run faster. But this should work regardless, as long as Windowing Functions are supported.