MariaDB如何查询近30天Top10播放歌曲的每日总播放量

核心逻辑 #

• 先通过子查询锁定近30天总播放量Top10的歌曲ID范围
• 仅针对这部分歌曲按天分组，统计每日播放量总和

基础版SQL（仅返回有播放记录的日期） #

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

完整版SQL（补全无播放的日期，固定返回30条结果） #

如果需要确保即使某天Top10歌曲总播放量为0也返回对应日期，可使用递归CTE生成完整的近30天日期序列关联查询：

WITH RECURSIVE date_series AS (
    -- 生成近30天的完整日期序列
    SELECT DATE(NOW() - INTERVAL 29 DAY) AS stat_day
    UNION ALL
    SELECT stat_day + INTERVAL 1 DAY
    FROM date_series
    WHERE stat_day < DATE(NOW())
),
top10_songs AS (
    -- 提取近30天Top10歌曲ID
    SELECT media_id
    FROM `table`
    WHERE c_date > NOW() - INTERVAL 30 DAY
    GROUP BY media_id, artist, title
    HAVING SUM(c_play) > 0
    ORDER BY SUM(c_play) DESC
    LIMIT 10
)
SELECT 
    d.stat_day,
    COALESCE(SUM(t.c_play), 0) AS daily_top10_total
FROM date_series d
LEFT JOIN `table` t 
    ON DATE(t.c_date) = d.stat_day
    AND t.media_id IN (SELECT media_id FROM top10_songs)
GROUP BY d.stat_day
ORDER BY d.stat_day DESC

原尝试SQL的错误点 #

1. 没有过滤Top10歌曲范围，统计的是全量歌曲的日播放总和
2. SELECT 包含media_id/artist/title等非聚合字段，但GROUP BY仅按日期分组，不符合SQL分组规范，返回的非聚合字段值为随机值
3. 末尾添加LIMIT 0,10仅返回10条结果，无法拿到30天的统计数据

内容的提问来源于stack exchange，提问作者Toniq