MySQL 8.0 如何查询各日期账户余额最高的国家

你写的SQL无法满足「查询每个日期下账户余额最高的国家」的需求，存在以下几个核心错误：

• 最终语句加了limit 1，只会返回1条结果，无法覆盖所有交易日期的结果
• 余额计算逻辑错误：没有区分pay_in（存入，金额加）和pay_out（支出，金额减）两种交易类型，且没有按日期逐笔累计客户的历史余额，只是简单按客户分组汇总金额，不符合账户余额随交易逐笔变动的逻辑
• 分组维度错误：用row_number() over (partition by customer_id)取每个客户最后一笔交易，没有按日期、国家维度汇总余额，和需求的统计维度完全不匹配

要实现需求需要分4步计算：

1. 先对每笔交易做金额正负转换：存入交易金额记为正，支出交易金额记为负
2. 按客户维度、按交易日期升序排序，用窗口函数累计计算每个客户在每笔交易发生后的实时账户余额
3. 关联客户表拿到国家信息，按「交易日期+国家」维度分组，汇总得到每个日期下每个国家的所有客户总余额
4. 对每个日期下的国家按总余额降序排名，取排名第1的即为当日账户余额最高的国家

注意：原表date字段是TEXT类型存储的字符串，需要先转成标准日期格式再排序、分组，避免字符串排序导致的日期顺序错误。

WITH customer_each_trade_balance AS (
    -- 计算每个客户每笔交易完成后的累计余额
    SELECT
        STR_TO_DATE(`date`, '%m/%d/%Y') AS trade_date,
        customer_id,
        SUM(
            CASE
                WHEN transaction_type = 'pay_in' THEN amount
                WHEN transaction_type = 'pay_out' THEN -amount
                ELSE 0
            END
        ) OVER (
            PARTITION BY customer_id
            ORDER BY STR_TO_DATE(`date`, '%m/%d/%Y'), deposit_id
        ) AS customer_current_balance
    FROM deposit_transactions
),
country_daily_balance AS (
    -- 按日期+国家汇总总余额
    SELECT
        ctb.trade_date,
        c.country,
        SUM(ctb.customer_current_balance) AS country_total_balance
    FROM customer_each_trade_balance ctb
    INNER JOIN customers c
        ON ctb.customer_id = c.customer_id
    GROUP BY ctb.trade_date, c.country
),
daily_rank AS (
    -- 给每个日期下的国家按总余额降序排名
    SELECT
        trade_date,
        country,
        country_total_balance,
        ROW_NUMBER() OVER (
            PARTITION BY trade_date
            ORDER BY country_total_balance DESC
        ) AS balance_rank
    FROM country_daily_balance
)
-- 筛选每个日期余额最高的国家
SELECT trade_date, country, country_total_balance
FROM daily_rank
WHERE balance_rank = 1
ORDER BY trade_date;

• 如果遇到同一日期下多个国家总余额完全相等的场景，ROW_NUMBER()会随机返回其中一个国家，如果需要把并列第一的国家全部返回，可以把ROW_NUMBER()替换成RANK()
• 代码里累计余额排序时加了deposit_id作为次级排序条件，是为了避免同一客户同一天有多笔交易时，累计顺序错乱导致余额计算错误

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