MySQL 5.7 按Date、AccountID分组查询当月与上月营收脚本需求

核心思路 #

由于无法使用窗口函数LAG和CTE语法，采用同表自关联的方式实现跨月数据匹配：利用Date字段为月末日期的特性，通过日期计算得到上月月末日期，将同一客户的上月营收数据关联到当月行上。

前提假设 #

• 业务表名为 account_revenue
• Date 字段为DATE类型，存储值均为对应月份的最后一天（如2024-01-31、2024-02-29）
• 若原表需要先按Date、AccountID聚合得到单客户单月营收，可使用下方的预聚合版本脚本

直接关联版本（原表已按Date+AccountID分组） #

SELECT
    curr.Date AS 当月月末日期,
    curr.AccountID AS 客户ID,
    curr.AccountName AS 客户名称,
    curr.Revenue AS 当月营收,
    IFNULL(prev.Revenue, 0) AS 上月营收
FROM 
    account_revenue curr
LEFT JOIN 
    account_revenue prev 
    ON curr.AccountID = prev.AccountID
    -- 当月月末日期减去当月天数，直接得到上月月末日期
    AND prev.Date = DATE_SUB(curr.Date, INTERVAL DAY(curr.Date) DAY)
ORDER BY 
    curr.Date DESC,
    curr.AccountID;

预聚合版本（原表存在更细粒度数据） #

如果原表不是按Date+AccountID分组的汇总数据，先做分组聚合再关联：

SELECT
    curr.Date AS 当月月末日期,
    curr.AccountID AS 客户ID,
    curr.AccountName AS 客户名称,
    curr.month_revenue AS 当月营收,
    IFNULL(prev.month_revenue, 0) AS 上月营收
FROM 
    (
        SELECT 
            Date,
            AccountID,
            AccountName,
            SUM(Revenue) AS month_revenue
        FROM account_revenue
        GROUP BY Date, AccountID, AccountName
    ) curr
LEFT JOIN 
    (
        SELECT 
            Date,
            AccountID,
            SUM(Revenue) AS month_revenue
        FROM account_revenue
        GROUP BY Date, AccountID
    ) prev 
    ON curr.AccountID = prev.AccountID
    AND prev.Date = DATE_SUB(curr.Date, INTERVAL DAY(curr.Date) DAY)
ORDER BY 
    curr.Date DESC,
    curr.AccountID;

说明 #

• 采用LEFT JOIN避免客户上月无数据时当月记录被过滤，无上月营收时默认返回0，可删除IFNULL函数返回NULL
• 可额外添加WHERE条件对curr.Date做范围筛选，不影响关联逻辑
• 日期计算逻辑完全适配月末日期的存储规则，无需额外做日期对齐处理

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