网站开发工程师岗位说明书最近国际新闻大事20条
力扣题
1、题目地址
1843. 可疑银行账户
2、模拟表
表：Accounts
| Column Name | Type |
|---|---|
| account_id | int |
| max_income | int |
- account_id 是这张表具有唯一值的列。
- 每行包含一个银行账户每月最大收入的信息。
表：Transactions
| Column Name | Type |
|---|---|
| transaction_id | int |
| account_id | int |
| type | ENUM |
| amount | int |
| day | datetime |
- transaction_id 是这张表具有唯一值的列。
- 每行包含一条转账信息。
- type 是枚举类型（包含’Creditor’,‘Debtor’），其中 ‘Creditor’ 表示用户向其账户存入资金，‘Debtor’ 表示用户从其账户取出资金。
- amount 是交易过程中的存入/取出的金额。
3、要求
如果一个账户在 连续两个及以上 月份的 总收入 超过最大收入（max_income），那么认为这个账户 可疑。 账户当月 总收入 是当月存入资金总数（即 transactions 表中 type 字段的 ‘Creditor’）。
编写一个解决方案，报告所有的 可疑 账户。
以 任意顺序 返回结果表
返回结果格式如下示例所示。
示例 1：
输入：
Accounts 表：
| account_id | max_income |
|---|---|
| 3 | 21000 |
| 4 | 10400 |
Transactions 表：
| transaction_id | account_id | type | amount | day |
|---|---|---|---|---|
| 2 | 3 | Creditor | 107100 | 2021-06-02 11:38:14 |
| 4 | 4 | Creditor | 10400 | 2021-06-20 12:39:18 |
| 11 | 4 | Debtor | 58800 | 2021-07-23 12:41:55 |
| 1 | 4 | Creditor | 49300 | 2021-05-03 16:11:04 |
| 15 | 3 | Debtor | 75500 | 2021-05-23 14:40:20 |
| 10 | 3 | Creditor | 102100 | 2021-06-15 10:37:16 |
| 14 | 4 | Creditor | 56300 | 2021-07-21 12:12:25 |
| 19 | 4 | Debtor | 101100 | 2021-05-09 15:21:49 |
| 8 | 3 | Creditor | 64900 | 2021-07-26 15:09:56 |
| 7 | 3 | Creditor | 90900 | 2021-06-14 11:23:07 |
输出：
| account_id |
|---|
| 3 |
解释：
对于账户 3：
- 在 2021年6月，用户收入为 107100 + 102100 + 90900 = 300100。
- 在 2021年7月，用户收入为 64900。
可见收入连续两月超过21000的最大收入，因此账户3列入结果表中。
对于账户 4：
- 在 2021年5月，用户收入为 49300。
- 在 2021年6月，用户收入为 10400。
- 在 2021年7月，用户收入为 56300。
可见收入在5月与7月超过了最大收入，但6月没有。因为账户没有没有连续两月超过最大收入，账户4不列入结果表中。
4、代码编写
思路
1、Transactions 中根据 account_id 和 day里面的年月进行分组，加上查询条件 type=‘Creditor’（存入的钱），就可以查询到不同账户在不同月份存钱的总量
SELECT account_id, DATE_FORMAT(day, '%Y-%m') AS `month`, SUM(amount) AS sAmount
FROM Transactions
WHERE `type` = 'Creditor'
GROUP BY account_id, `month`
| account_id | month | sAmount |
| ---------- | ------- | ------- |
| 3 | 2021-06 | 300100 |
| 4 | 2021-06 | 10400 |
| 4 | 2021-05 | 49300 |
| 4 | 2021-07 | 56300 |
| 3 | 2021-07 | 64900 |
2、要求里面是要连续两个月或以上总收入超过最大收入，那我们按最少两个月就行，将上面查询出来的连接自己，account_id 相同，月份（month）差距是一个月，满足总收入（sAmount）超过最大收入（max_income）就行
第一版写法（日期 YYYY-MM 格式特殊处理）
WITH tmp AS(SELECT account_id, DATE_FORMAT(day, '%Y-%m') AS `month`, SUM(amount) AS sAmountFROM TransactionsWHERE `type` = 'Creditor'GROUP BY account_id, `month`
)
SELECT DISTINCT one.account_id
FROM tmp AS one, tmp AS two
WHERE one.account_id = two.account_id
AND CONCAT(one.month, '01') = DATE_SUB(CONCAT(two.month, '-01'), INTERVAL 1 MONTH)
AND one.sAmount > (SELECT max_income FROM Accounts WHERE account_id = one.account_id)
AND two.sAmount > (SELECT max_income FROM Accounts WHERE account_id = two.account_id)
说一下我为什么这样用：
首先我测试 select date_sub('2023-02', interval 1 month)，期望结果是 2023-01，但是结果是 Null，
其次我再测试 select date_sub('2023-02-01', interval 1 month)，这次正常输出，结果是 2023-01-01
所以就有了个想法，拼接后面的 -01，使用 concat 函数进行拼接即可，即 select date_sub(concat('2023-02','-01'), interval 1 month)
第二版写法（日期格式用 YYYYMM 使用 PERIOD_DIFF 函数）
PERIOD_DIFF
1、PERIOD_DIFF() 函数返回两日期之间的差异，结果以月份计算。
2、period1 和 period2 应采用相同的格式（格式为YYMM或YYYYMM）
3、语法：PERIOD_DIFF(period1, period2)
测试：
select period_diff('202302', '202301')，结果 1
select period_diff('2302', '2301')，结果 1
select period_diff('202301', '202302')，结果 -1
select period_diff('2301', '2302')，结果 -1
参考：MySQL PERIOD_DIFF() 函数
WITH tmp AS(SELECT account_id, DATE_FORMAT(day, '%Y%m') AS `month`, SUM(amount) AS sAmountFROM TransactionsWHERE `type` = 'Creditor'GROUP BY account_id, `month`
)
SELECT DISTINCT one.account_id
FROM tmp AS one, tmp AS two
WHERE one.account_id = two.account_id
AND PERIOD_DIFF(two.month, one.month) = 1
AND one.sAmount > (SELECT max_income FROM Accounts WHERE account_id = one.account_id)
AND two.sAmount > (SELECT max_income FROM Accounts WHERE account_id = two.account_id)
注意：日期转换 %Y 和 %m 对应 YYYY 和 MM（mm是分钟，MM才是月份，注意不要弄错）
date_format( ) 转换格式：
| 格式 | 描述 |
|---|---|
| %M | 月名 |
| %m | 月，数值（00-12） |
| %Y | 年，4 位 |
| %y | 年，2 位 |