MySQL如何查询各钱包当日首/末笔余额并计算期初、期末总余额

方案1：MySQL 8.0及以上版本（支持窗口函数） #

通过ROW_NUMBER()窗口函数标记每个钱包当日的首条、末条记录，过滤后求和即可：

SELECT
    SUM(CASE WHEN rn_asc = 1 THEN balance ELSE 0 END) AS 期初总余额,
    SUM(CASE WHEN rn_desc = 1 THEN balance ELSE 0 END) AS 期末总余额
FROM (
    SELECT
        wallet,
        balance,
        -- 按钱包分组，时间正序排名，rn_asc=1就是该钱包当日首条记录
        ROW_NUMBER() OVER (PARTITION BY wallet ORDER BY `timestamp` ASC) AS rn_asc,
        -- 按钱包分组，时间倒序排名，rn_desc=1就是该钱包当日末条记录
        ROW_NUMBER() OVER (PARTITION BY wallet ORDER BY `timestamp` DESC) AS rn_desc
    FROM 你的表名
    -- 筛选当日数据，此处示例为2021-04-19，可替换为CURDATE()统计当天
    WHERE DATE(`timestamp`) = '2021-04-19'
) t
WHERE rn_asc = 1 OR rn_desc = 1;

方案2：MySQL 5.x版本（无窗口函数支持） #

通过关联子查询匹配每个钱包当日的最早、最晚时间对应的余额，再求和：

SELECT
    (SELECT SUM(balance)
     FROM 你的表名 t1
     WHERE DATE(t1.`timestamp`) = '2021-04-19'
     AND t1.`timestamp` = (SELECT MIN(t2.`timestamp`) FROM 你的表名 t2 WHERE t2.wallet = t1.wallet AND DATE(t2.`timestamp`) = '2021-04-19')) AS 期初总余额,
    (SELECT SUM(balance)
     FROM 你的表名 t1
     WHERE DATE(t1.`timestamp`) = '2021-04-19'
     AND t1.`timestamp` = (SELECT MAX(t2.`timestamp`) FROM 你的表名 t2 WHERE t2.wallet = t1.wallet AND DATE(t2.`timestamp`) = '2021-04-19')) AS 期末总余额;

结果验证 #

上述两种方案在示例数据上执行后，返回结果与预期完全一致：

• 期初总余额：5500
• 期末总余额：5590

注意：timestamp是MySQL关键字，查询时需要用反引号包裹避免语法错误。

内容的提问来源于stack exchange，提问作者Luis Alberto Murcia Solivella