MySQL高阶2066-账户余额

目录

题目

准备数据

分析数据

总结

---

题目

请写出能够返回用户每次交易完成后的账户余额. 我们约定所有用户在进行交易前的账户余额都为0， 并且保证所有交易行为后的余额不为负数。

返回的结果请依次按照 账户（account_id), 日期( day ) 进行升序排序 .

准备数据

Create table If Not Exists Transactions (account_id int, day date, type ENUM('Deposit', 'Withdraw'), amount int)
    Truncate table Transactions
    insert into Transactions (account_id, day, type, amount) values ('1', '2021-11-07', 'Deposit', '2000')
    insert into Transactions (account_id, day, type, amount) values ('1', '2021-11-09', 'Withdraw', '1000')
    insert into Transactions (account_id, day, type, amount) values ('1', '2021-11-11', 'Deposit', '3000')
    insert into Transactions (account_id, day, type, amount) values ('2', '2021-12-07', 'Deposit', '7000')
    insert into Transactions (account_id, day, type, amount) values ('2', '2021-12-12', 'Withdraw', '7000')

分析数据

select
    account_id, day,
    sum(case when type = 'Deposit' then amount else -amount end) over (partition by account_id order by day) balance
from
    transactions
order by
    account_id,day;

总结

使用开窗函数sum()计算不同的交易类型