MySQL查询统计音乐排行榜歌曲同位置连续停留周数及排名变动

本方案基于MySQL 8.0+窗口函数实现，逻辑清晰且查询性能最优：

-- 定义目标查询日期，修改此处值即可查询任意日期结果
SET @target_date = '2019-12-29';

WITH song_rank_history AS (
    -- 拉取所有歌曲的历史排名，计算上周的排名和日期
    SELECT 
        song_name,
        date,
        chart_position,
        LAG(chart_position) OVER (PARTITION BY song_name ORDER BY date) AS last_week_pos,
        LAG(date) OVER (PARTITION BY song_name ORDER BY date) AS last_week_date
    FROM `table` -- 此处替换为你的实际表名
),
continuous_group AS (
    SELECT 
        *,
        -- 给连续同排名的周期打分组标记，排名变化/周数不连续则生成新分组
        SUM(
            CASE 
                WHEN last_week_pos = chart_position AND last_week_date = date - INTERVAL 7 DAY 
                THEN 0 
                ELSE 1 
            END
        ) OVER (PARTITION BY song_name ORDER BY date) AS group_id
    FROM song_rank_history
),
group_weeks_count AS (
    SELECT 
        *,
        -- 统计每个连续同排名分组的累计周数
        COUNT(*) OVER (PARTITION BY song_name, group_id ORDER BY date) AS weeks_in_position
    FROM continuous_group
)
-- 输出目标日期的最终结果
SELECT 
    song_name,
    date,
    chart_position,
    weeks_in_position,
    CASE
        WHEN last_week_date IS NULL THEN 'new'
        WHEN weeks_in_position > 1 THEN 'same'
        WHEN chart_position < last_week_pos THEN 'up'
        WHEN chart_position > last_week_pos THEN 'down'
        ELSE 'same'
    END AS movement
FROM group_weeks_count
WHERE date = @target_date
ORDER BY chart_position ASC;

逻辑说明 #

• 连续周数判断加入了7天间隔校验，避免中途空榜的歌曲被误算为连续停留
• movement字段完全匹配需求：无上周数据返回new（新入榜），连续同排名周数≥2返回same（不变），排名数字更小（排名更靠前）返回up（上升），排名数字更大（排名更靠后）返回down（下降）

如果使用的是不支持窗口函数的MySQL 5.x版本，可以用关联子查询实现兼容：

SET @target_date = '2019-12-29';

SELECT 
    t.song_name,
    t.date,
    t.chart_position,
    (
        SELECT COUNT(*) + 1
        FROM `table` t2
        WHERE t2.song_name = t.song_name
        AND t2.chart_position = t.chart_position
        AND t2.date < t.date
        AND t2.date = t.date - INTERVAL (7 * (COUNT(*)+1)) DAY
    ) AS weeks_in_position,
    CASE
        WHEN t_prev.date IS NULL THEN 'new'
        WHEN t_prev.chart_position = t.chart_position THEN 'same'
        WHEN t.chart_position < t_prev.chart_position THEN 'up'
        ELSE 'down'
    END AS movement
FROM `table` t
LEFT JOIN `table` t_prev 
    ON t_prev.song_name = t.song_name 
    AND t_prev.date = t.date - INTERVAL 7 DAY
WHERE t.date = @target_date
ORDER BY t.chart_position ASC;

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