README.md

comments	difficulty	edit_url	tags
true	中等	https://github.com/doocs/leetcode/edit/main/solution/2000-2099/2066.Account%20Balance/README.md	数据库

2066. 账户余额 🔒

English Version

题目描述

表名: Transactions

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| day         | date |
| type        | ENUM |
| amount      | int  |
+-------------+------+
(account_id, day) 是该Transactions表的主键.
表中的每行数据表示一次交易的信息, 包括此次交易的账号(account_id), 交易类型(type), 交易发生时间(day), 交易发生金额(amount).
其中交易类型(type)字段包括了两种行为：存入 ('Deposit'), 取出('Withdraw').

 

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

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

查询结果的格式请参照以下测试样例.

 

测试样例1:

输入: 
Transactions 表:
+------------+------------+----------+--------+
| account_id | day        | type     | amount |
+------------+------------+----------+--------+
| 1          | 2021-11-07 | Deposit  | 2000   |
| 1          | 2021-11-09 | Withdraw | 1000   |
| 1          | 2021-11-11 | Deposit  | 3000   |
| 2          | 2021-12-07 | Deposit  | 7000   |
| 2          | 2021-12-12 | Withdraw | 7000   |
+------------+------------+----------+--------+
输出: 
+------------+------------+---------+
| account_id | day        | balance |
+------------+------------+---------+
| 1          | 2021-11-07 | 2000    |
| 1          | 2021-11-09 | 1000    |
| 1          | 2021-11-11 | 4000    |
| 2          | 2021-12-07 | 7000    |
| 2          | 2021-12-12 | 0       |
+------------+------------+---------+

解释: 
账户1:
- 初始金额为 0.
- 2021-11-07 --> 存入2000. 余额变为 0 + 2000 = 2000.
- 2021-11-09 --> 取出1000. 余额变为 2000 - 1000 = 1000.
- 2021-11-11 --> 存入3000. 余额变为 1000 + 3000 = 4000.
账户2:
- 初始金额为 0.
- 2021-12-07 --> 存入7000. 余额变为 0 + 7000 = 7000.
- 2021-12-12 --> 取出 7000. 余额变为 7000 - 7000 = 0.

解法

方法一

MySQL

# Write your MySQL query statement below
SELECT
    account_id,
    day,
    SUM(IF(type = 'Deposit', amount, -amount)) OVER (
        PARTITION BY account_id
        ORDER BY day
    ) AS balance
FROM Transactions
ORDER BY 1, 2;