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()计算不同的交易类型