MySQL按时间戳匹配去年同星期几数据并补全缺失记录

针对你需要包含所有时间戳（本年+去年）、补全缺失数据并对比去年对应星期几营收/交易笔数的需求，以下是具体解决方案：

核心思路 #

先生成所有日期（小时级）与门店的完整组合作为基础数据集，再通过左连接关联本年、去年的聚合销售数据，确保不会遗漏任何记录，同时补全缺失值。

方案一：按「去年同期日期」对比（日期减一年） #

适用于需要对比本年某日期与去年同一日期的场景，比如2024-01-08对应2023-01-08：

WITH all_dates AS (
    -- 提取所有有销售记录的小时级日期（覆盖本年+去年）
    SELECT DISTINCT DATE_FORMAT(transaction_time, '%Y-%m-%d %H:00') AS dt
    FROM sales_data
    WHERE YEAR(transaction_time) IN (YEAR(CURDATE()) - 1, YEAR(CURDATE()))
),
all_stores AS (
    -- 提取所有门店
    SELECT DISTINCT folderCounter_id
    FROM sales_data
),
base_dataset AS (
    -- 生成所有日期+门店的完整组合（补全缺失记录的核心）
    SELECT ad.dt, as_.folderCounter_id
    FROM all_dates ad
    CROSS JOIN all_stores as_
),
current_year_agg AS (
    -- 本年销售数据聚合（按日期、门店分组）
    SELECT
        DATE_FORMAT(transaction_time, '%Y-%m-%d %H:00') AS dt,
        folderCounter_id,
        SUM(incasso) AS incasso_current,
        COUNT(*) AS scontrini_current
    FROM sales_data
    WHERE YEAR(transaction_time) = YEAR(CURDATE())
    GROUP BY dt, folderCounter_id
),
last_year_agg AS (
    -- 去年销售数据聚合，并转换为对应本年的日期（用于关联）
    SELECT
        DATE_FORMAT(DATE_ADD(transaction_time, INTERVAL 1 YEAR), '%Y-%m-%d %H:00') AS dt_match,
        folderCounter_id,
        SUM(incasso) AS incasso_last,
        COUNT(*) AS scontrini_last
    FROM sales_data
    WHERE YEAR(transaction_time) = YEAR(CURDATE()) - 1
    GROUP BY dt_match, folderCounter_id
)
SELECT
    b.dt,
    b.folderCounter_id,
    COALESCE(c.incasso_current, 0) AS incasso_current, -- 缺失值补0，也可改为NULL
    COALESCE(c.scontrini_current, 0) AS scontrini_current,
    COALESCE(l.incasso_last, 0) AS incasso_last,
    COALESCE(l.scontrini_last, 0) AS scontrini_last
FROM base_dataset b
LEFT JOIN current_year_agg c
    ON b.dt = c.dt AND b.folderCounter_id = c.folderCounter_id
LEFT JOIN last_year_agg l
    ON b.dt = l.dt_match AND b.folderCounter_id = l.folderCounter_id
ORDER BY b.dt DESC, b.folderCounter_id;

方案二：按「去年对应星期几」对比 #

适用于需要对比本年某星期几与去年同一星期几的场景，比如本年周一对应去年周一，需匹配星期几、小时、周数：

WITH all_dates AS (
    SELECT DISTINCT DATE_FORMAT(transaction_time, '%Y-%m-%d %H:00') AS dt
    FROM sales_data
    WHERE YEAR(transaction_time) IN (YEAR(CURDATE()) - 1, YEAR(CURDATE()))
),
all_stores AS (
    SELECT DISTINCT folderCounter_id
    FROM sales_data
),
base_dataset AS (
    SELECT ad.dt, as_.folderCounter_id
    FROM all_dates ad
    CROSS JOIN all_stores as_
),
current_year_agg AS (
    SELECT
        DATE_FORMAT(transaction_time, '%Y-%m-%d %H:00') AS dt,
        folderCounter_id,
        SUM(incasso) AS incasso_current,
        COUNT(*) AS scontrini_current
    FROM sales_data
    WHERE YEAR(transaction_time) = YEAR(CURDATE())
    GROUP BY dt, folderCounter_id
),
last_year_agg AS (
    SELECT
        DATE_FORMAT(transaction_time, '%Y-%m-%d %H:00') AS dt,
        WEEKDAY(transaction_time) AS weekday_num, -- 0=周一，6=周日
        HOUR(transaction_time) AS hour_num,
        WEEKOFYEAR(transaction_time) AS week_num,
        folderCounter_id,
        SUM(incasso) AS incasso_last,
        COUNT(*) AS scontrini_last
    FROM sales_data
    WHERE YEAR(transaction_time) = YEAR(CURDATE()) - 1
    GROUP BY dt, weekday_num, hour_num, week_num, folderCounter_id
)
SELECT
    b.dt,
    b.folderCounter_id,
    COALESCE(c.incasso_current, 0) AS incasso_current,
    COALESCE(c.scontrini_current, 0) AS scontrini_current,
    COALESCE(l.incasso_last, 0) AS incasso_last,
    COALESCE(l.scontrini_last, 0) AS scontrini_last
FROM base_dataset b
LEFT JOIN current_year_agg c
    ON b.dt = c.dt AND b.folderCounter_id = c.folderCounter_id
LEFT JOIN last_year_agg l
    ON b.folderCounter_id = l.folderCounter_id
    AND WEEKDAY(b.dt) = l.weekday_num
    AND HOUR(b.dt) = l.hour_num
    AND WEEKOFYEAR(b.dt) = l.week_num -- 匹配同一周的星期几
ORDER BY b.dt DESC, b.folderCounter_id;

关键说明 #

• 基础数据集生成：通过CROSS JOIN将所有日期与门店组合，确保不会遗漏任何时间戳或门店的记录。
• 缺失值补全：用COALESCE将NULL值替换为0（或你需要的默认值），避免结果中出现空值。
• 关联逻辑灵活：可根据实际业务需求选择「日期同期」或「星期几同期」的关联方式。

内容的提问来源于stack exchange，提问作者peppe71-19