如何在MySQL中获取分组后每组最后一条记录的balance值？

我有一张名为wallet的表，结构及数据如下：

期望得到按日期分组的结果（每日一行）：

当前的查询语句为：

SELECT MIN(balance) min_balance,
       DATE(timestamp) date
FROM wallet
GROUP BY date

由于MySQL中没有类似LAST(balance)的函数，想知道如何添加last_balance字段（这里的“最后”指timestamp最大的记录对应的balance值）？

方法一：使用窗口函数（MySQL 8.0及以上版本） #

利用ROW_NUMBER()窗口函数按日期分组、时间戳倒序标记每组最新记录，再聚合筛选：

WITH ranked_wallet AS (
    SELECT 
        balance,
        DATE(timestamp) AS date,
        ROW_NUMBER() OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp DESC) AS rn
    FROM wallet
)
SELECT
    MIN(w.balance) AS min_balance,
    r.balance AS last_balance,
    w.date
FROM wallet w
JOIN ranked_wallet r ON w.date = r.date AND r.rn = 1
GROUP BY w.date, r.balance;

也可以用FIRST_VALUE()直接提取每组最新余额，写法更简洁：

SELECT DISTINCT
    DATE(timestamp) AS date,
    MIN(balance) OVER (PARTITION BY DATE(timestamp)) AS min_balance,
    FIRST_VALUE(balance) OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp DESC) AS last_balance
FROM wallet;

方法二：使用关联子查询（兼容低版本MySQL） #

先通过子查询找到每个日期的最大时间戳，再关联原表获取对应余额：

SELECT
    MIN(w1.balance) AS min_balance,
    w2.balance AS last_balance,
    DATE(w1.timestamp) AS date
FROM wallet w1
JOIN (
    SELECT 
        DATE(timestamp) AS date,
        MAX(timestamp) AS max_ts
    FROM wallet
    GROUP BY date
) t ON DATE(w1.timestamp) = t.date
JOIN wallet w2 ON t.max_ts = w2.timestamp
GROUP BY DATE(w1.timestamp), w2.balance;

或者直接在SELECT子句中嵌套子查询获取最新余额：

SELECT
    MIN(balance) AS min_balance,
    (SELECT balance FROM wallet 
     WHERE DATE(timestamp) = DATE(w.timestamp) 
     ORDER BY timestamp DESC LIMIT 1) AS last_balance,
    DATE(timestamp) AS date
FROM wallet w
GROUP BY date;

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