MySQL获取去年同期60天的页面浏览量数据

现有SQL可正常获取过去60天的每日浏览量总和，需获取该60天对应的去年同期每日浏览量数据（例如2024-10-07的对应数据为2023-10-07的浏览量）。

当前可用的过去60天浏览量查询SQL：

SELECT COALESCE(SUM(`adv_views`), 0) AS `total_views`
, STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') AS `date`
FROM `art_daily_views`
GROUP BY `adv_year`, `adv_month`, `adv_day`
ORDER BY `date` DESC
LIMIT 0, 60;

自行修改后的SQL返回空结果：

SELECT COALESCE(SUM(`adv_views`), 0) AS `total_views`
, STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') AS `date`
FROM `art_daily_views`
WHERE STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') <= STR_TO_DATE(DATE_SUB(NOW(), INTERVAL 12 MONTH), '%Y-%m-%d')
GROUP BY `adv_year`, `adv_month`, `adv_day`
ORDER BY `date` DESC
LIMIT 0, 60;

表结构示例：

adv_day, adv_month, adv_year, adv_views
'07', '10', '2024', '1'
'07', '10', '2024', '3'
'07', '10', '2024', '2'
'06', '10', '2024', '1'
'06', '10', '2024', '1'

问题原因 #

原修改后的SQL逻辑错误：<= DATE_SUB(NOW(), INTERVAL 12 MONTH)会筛选出所有早于等于一年前的日期，再取最新60条，这得到的是一年前及更早的最近60天数据，而非当前过去60天对应的去年同期数据。

正确SQL写法 #

方法1：直接匹配日期范围

通过计算当前过去60天对应的去年同期日期区间，精准筛选数据：

SELECT 
    COALESCE(SUM(`adv_views`), 0) AS `total_views`,
    STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') AS `date`
FROM `art_daily_views`
WHERE 
    STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') 
    BETWEEN DATE_SUB(DATE_SUB(NOW(), INTERVAL 60 DAY), INTERVAL 1 YEAR)
    AND DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY `adv_year`, `adv_month`, `adv_day`
ORDER BY `date` DESC;

方法2：先获取近期日期再映射同期

先提取当前过去60天的日期，再关联查询这些日期对应的去年同期数据，避免日期范围计算误差：

WITH recent_dates AS (
    SELECT 
        STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') AS curr_date
    FROM `art_daily_views`
    GROUP BY `adv_year`, `adv_month`, `adv_day`
    ORDER BY curr_date DESC
    LIMIT 60
)
SELECT 
    COALESCE(SUM(`adv_views`), 0) AS `total_views`,
    STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') AS `date`
FROM `art_daily_views`
JOIN recent_dates 
    ON STR_TO_DATE(CONCAT(`adv_year`, '-', `adv_month`, '-', `adv_day`), '%Y-%m-%d') = DATE_SUB(recent_dates.curr_date, INTERVAL 1 YEAR)
GROUP BY `adv_year`, `adv_month`, `adv_day`
ORDER BY `date` DESC;

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