如何从一对多关联表中查询关键词的上周、上月排名数据

现有实现问题说明 #

你当前编写的SQL存在两个明显问题：

• 不必要使用GROUP_CONCAT，由于你每日仅为每个关键词插入1条排名数据，该函数会导致返回结果为字符串格式，若当天有多条数据还会自动拼接，不符合数值类型的排名使用需求
• 固定取N天前当天的排名，若对应日期未执行排名采集任务无数据，会返回空值，实际业务中通常取对应时间段内的最新排名更合理

方案1：保留原需求（固定取今日、7天前、30天前当日排名） #

适合确认每日都有稳定排名数据入库的场景：

SELECT 
    k.keyword, 
    k.link,
    COALESCE(kr.rank, 0) AS today_rank,
    COALESCE(kr7.rank, 0) AS last_week_rank,
    COALESCE(kr30.rank, 0) AS last_month_rank
FROM keywords k
LEFT JOIN keyword_rank kr 
    ON kr.keyword_hash = k.hash 
    AND DATE(kr.date) = CURDATE()
LEFT JOIN keyword_rank kr7 
    ON kr7.keyword_hash = k.hash 
    AND DATE(kr7.date) = DATE_SUB(CURDATE(), INTERVAL 7 DAY)
LEFT JOIN keyword_rank kr30 
    ON kr30.keyword_hash = k.hash 
    AND DATE(kr30.date) = DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE k.project_hash = :project_hash
GROUP BY k.id, k.keyword, k.link

这里用COALESCE把空值转为0，避免返回NULL，取消了不必要的GROUP_CONCAT调用。

方案2：取各时间段最新排名（推荐，兼容漏采场景） #

若你的MySQL版本为8.0及以上，用窗口函数实现更高效，逻辑为取每个关键词分别在今日、近7天、近30天范围内的最新一条排名，即使某几天漏采数据也能返回最近的有效排名：

WITH rank_list AS (
    SELECT 
        keyword_hash,
        rank,
        date,
        ROW_NUMBER() OVER (PARTITION BY keyword_hash ORDER BY date DESC) AS rn_today,
        ROW_NUMBER() OVER (PARTITION BY keyword_hash ORDER BY IF(date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY), date, '1970-01-01') DESC) AS rn_7d,
        ROW_NUMBER() OVER (PARTITION BY keyword_hash ORDER BY IF(date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY), date, '1970-01-01') DESC) AS rn_30d
    FROM keyword_rank
)
SELECT 
    k.keyword,
    k.link,
    COALESCE(rt.rank, 0) AS today_rank,
    COALESCE(r7.rank, 0) AS last_7d_rank,
    COALESCE(r30.rank, 0) AS last_30d_rank
FROM keywords k
LEFT JOIN rank_list rt ON rt.keyword_hash = k.hash AND rt.rn_today = 1 AND DATE(rt.date) = CURDATE()
LEFT JOIN rank_list r7 ON r7.keyword_hash = k.hash AND r7.rn_7d = 1 AND r7.date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
LEFT JOIN rank_list r30 ON r30.keyword_hash = k.hash AND r30.rn_30d = 1 AND r30.date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE k.project_hash = :project_hash
GROUP BY k.id, k.keyword, k.link

性能优化建议 #

给keyword_rank表添加联合索引，可大幅提升多表关联查询速度：

ALTER TABLE `keyword_rank` ADD INDEX `idx_hash_date_rank` (`keyword_hash`, `date`, `rank`);

内容的提问来源于stack exchange，提问作者m.qayyum