如何在聚合查询中计算与上周数据的百分比差异？

嘿，这个分组后计算环比的坑我之前踩过！直接关联原表确实会因为大表扫描导致性能爆炸，给你几个既高效又优雅的解决方案：

方案一：用窗口函数LAG()（推荐，MySQL 8.0+适用） #

因为你的结果是按日期倒序排列的周度数据，刚好可以用LAG()窗口函数直接拿到上一周的总入账金额，完全不需要额外的JOIN操作，性能和你原来的查询几乎没差别。

修改后的SQL如下：

SELECT 
    DATE_FORMAT(s.created, "%Y-%m-%d") as "Date", 
    count(s.id) AS "Accounts credited", 
    sum(s.withdrawal) AS "Total Credited",
    -- 计算环比百分比差异，处理第一行无上周数据的情况
    CASE 
        WHEN LAG(sum(s.withdrawal)) OVER (ORDER BY s.created DESC) IS NOT NULL
        THEN ROUND(100 * (sum(s.withdrawal) - LAG(sum(s.withdrawal)) OVER (ORDER BY s.created DESC)) 
             / LAG(sum(s.withdrawal)) OVER (ORDER BY s.created DESC), 2)
        ELSE NULL
    END AS "Difference in %"
FROM statements s 
WHERE (s.status_id = 'OPEN' OR s.status_id = 'PENDING') 
GROUP BY YEAR(s.created), MONTH(s.created), DAY(s.created) 
ORDER BY s.created DESC 
LIMIT 8;

我加了ROUND()让百分比更美观，你可以根据需求调整小数位数。这个方案的核心是窗口函数在聚合完成后再处理，避免了对原大表的重复扫描，性能拉满。

方案二：预聚合后自关联（兼容MySQL 5.x） #

如果你的MySQL版本还不支持窗口函数，那可以先把周度统计结果预聚合到一个临时结果集（CTE或子查询），再和这个小结果集自关联找上周数据——因为预聚合后的结果集只有每周一条数据，关联起来速度极快。

示例SQL：

-- 用子查询替代CTE可以兼容MySQL 5.x
SELECT 
    ws.stat_date AS "Date",
    ws.accounts_credited AS "Accounts credited",
    ws.total_credited AS "Total Credited",
    CASE 
        WHEN prev_ws.total_credited IS NOT NULL
        THEN ROUND(100 * (ws.total_credited - prev_ws.total_credited) / prev_ws.total_credited, 2)
        ELSE NULL
    END AS "Difference in %"
FROM (
    SELECT 
        DATE_FORMAT(s.created, "%Y-%m-%d") as stat_date,
        DATE(s.created) as created_date,
        count(s.id) AS accounts_credited, 
        sum(s.withdrawal) AS total_credited
    FROM statements s 
    WHERE (s.status_id = 'OPEN' OR s.status_id = 'PENDING') 
    GROUP BY YEAR(s.created), MONTH(s.created), DAY(s.created)
) ws
LEFT JOIN (
    SELECT 
        DATE(s.created) as created_date,
        sum(s.withdrawal) AS total_credited
    FROM statements s 
    WHERE (s.status_id = 'OPEN' OR s.status_id = 'PENDING') 
    GROUP BY YEAR(s.created), MONTH(s.created), DAY(s.created)
) prev_ws 
    ON prev_ws.created_date = ws.created_date - INTERVAL 7 DAY
ORDER BY ws.created_date DESC
LIMIT 8;

这里的两个子查询都是做同样的聚合，你也可以把结果存入临时表再关联，性能会更优一点，但子查询写法已经足够简洁。

关于存储过程或非SQL方案 #

其实上面的SQL方案已经完全能满足需求，存储过程没必要——除非你要把这个逻辑封装成可重复调用的接口，但单纯计算环比的话，SQL写法更直接。

如果想在应用层处理也可以：先按原查询把数据查出来，然后在代码里遍历计算每一行和上一行的百分比差异。这种方式适合需要对结果做更多自定义处理的场景，但性能和SQL方案差不多，毕竟返回的数据只有几条。

最后提个小优化：确保你的statements表有(status_id, created)的联合索引，这能让原查询的WHERE过滤和GROUP BY操作更快，你的原查询已经高效，应该已经有这个索引了，但还是确认下更稳妥。

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