使用MySQL计算不同日期交易积分余额及差值

需求说明 #

按交易日期汇总当日积分总余额，并计算当日总余额与前一日总余额的差值。

样本数据 #

假设数据表名为transaction_points，字段包括：

• id：交易编号（INT）
• transaction_date：交易日期（VARCHAR，格式为DD-MM-YYYY）
• points：交易积分（INT，正为增加，负为扣除）

样本数据如下：

解决方案SQL #

SELECT
    transaction_date,
    daily_total AS 总积分,
    daily_total - LAG(daily_total) OVER (ORDER BY STR_TO_DATE(transaction_date, '%d-%m-%Y')) AS 差值
FROM (
    -- 先按日期汇总每日总积分
    SELECT
        transaction_date,
        SUM(points) AS daily_total
    FROM transaction_points
    GROUP BY transaction_date
) AS daily_summary
ORDER BY STR_TO_DATE(transaction_date, '%d-%m-%Y');

代码解释 #

1. 内层子查询daily_summary：通过GROUP BY transaction_date分组，用SUM(points)计算当日积分总余额，得到每日的总积分数据。
2. 外层查询：
   • 使用LAG(daily_total) OVER (ORDER BY STR_TO_DATE(transaction_date, '%d-%m-%Y'))获取前一日的总积分值，ORDER BY子句确保日期按时间顺序排序（原始日期为字符串格式，需用STR_TO_DATE转换为日期类型保证排序逻辑正确）。
   • 用当日总积分减去前一日总积分得到差值；第一日无前置数据，差值为NULL。

执行结果 #

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