1. 题目描述
Transactions 记录表
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| id | int |
| country | varchar |
| state | enum |
| amount | int |
| trans_date | date |
+----------------+---------+
id 是这个表的主键 该表包含有关传入事务的信息 状态列是枚举类型，值为 [approved、declined] 其中之一的列
Chargebacks 表
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| trans_id | int |
| trans_date | date |
+----------------+---------+
退单包含有关放置在事务表中的某些事务的传入退单的基本信息 trans_id 是 transactions 表的 id 列的外键（reference 列） 每项退单都对应于之前进行的交易，即使未经批准
编写一个解决方案，找出每个国家/地区的每月交易信息：已批准交易的数量及其总金额、退单的数量及其总金额
注意：在你的解决方案中，只需显示给定月份和国家，忽略所有为零的行
以 任意顺序 返回结果表
2. 测试用例
输入： Transactions 表：
+-----+---------+----------+--------+------------+
| id | country | state | amount | trans_date |
+-----+---------+----------+--------+------------+
| 101 | US | approved | 1000 | 2019-05-18 |
| 102 | US | declined | 2000 | 2019-05-19 |
| 103 | US | approved | 3000 | 2019-06-10 |
| 104 | US | declined | 4000 | 2019-06-13 |
| 105 | US | approved | 5000 | 2019-06-15 |
+-----+---------+----------+--------+------------+
Chargebacks 表：
+----------+------------+
| trans_id | trans_date |
+----------+------------+
| 102 | 2019-05-29 |
| 101 | 2019-06-30 |
| 105 | 2019-09-18 |
+----------+------------+
输出：
+---------+---------+----------------+-----------------+------------------+-------------------+
| month | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
+---------+---------+----------------+-----------------+------------------+-------------------+
| 2019-05 | US | 1 | 1000 | 1 | 2000 |
| 2019-06 | US | 2 | 8000 | 1 | 1000 |
| 2019-09 | US | 0 | 0 | 1 | 5000 |
+---------+---------+----------------+-----------------+------------------+-------------------+
3. 解题思路
- 在
Transactions表中, 对符合state = approved条件的数据进行统计, 按照country和month分组统计数量approved_count和金额approved_amount, 退单统计数据chargeback_count, chargeback_amount赋值为 0
select country,
date_format(trans_date, '%Y-%m') as month,
count(id) as approved_count,
sum(amount) as approved_amount,
0 as chargeback_count,
0 as chargeback_amount
from Transactions
where state = 'approved'
group by country, month
执行结果
+-------+-------+--------------+---------------+----------------+-----------------+
|country|month |approved_count|approved_amount|chargeback_count|chargeback_amount|
+-------+-------+--------------+---------------+----------------+-----------------+
|US |2019-05|1 |1000 |0 |0 |
|US |2019-06|2 |8000 |0 |0 |
+-------+-------+--------------+---------------+----------------+-----------------+
- 将
Chargebacks左外连接Transactions表, 按照country和month分组统计数量chargeback_count和金额chargeback_amount, 经批准的订单统计数据approved_count,approved_amount赋值为 0
select country,
date_format(c.trans_date, '%Y-%m') as month,
0 as approved_count,
0 as approved_amount,
count(c.trans_id) as chargeback_count,
sum(t.amount) as chargeback_amount
from Chargebacks as c
left join Transactions as t on t.id = c.trans_id
group by country, month
执行结果
+-------+-------+--------------+---------------+----------------+-----------------+
|country|month |approved_count|approved_amount|chargeback_count|chargeback_amount|
+-------+-------+--------------+---------------+----------------+-----------------+
|US |2019-05|0 |0 |1 |2000 |
|US |2019-06|0 |0 |1 |1000 |
|US |2019-09|0 |0 |1 |5000 |
+-------+-------+--------------+---------------+----------------+-----------------+
- 将步骤 1 和步骤 2 的结果进行全连接, 对合并的结果, 按照
country和month分组统计数据
select month,
country,
sum(approved_count) as approved_count,
sum(approved_amount) as approved_amount,
sum(chargeback_count) as chargeback_count,
sum(chargeback_amount) as chargeback_amount
from (select country,
date_format(trans_date, '%Y-%m') as month,
count(id) as approved_count,
sum(amount) as approved_amount,
0 as chargeback_count,
0 as chargeback_amount
from Transactions
where state = 'approved'
group by country, month
union
select country,
date_format(c.trans_date, '%Y-%m') as month,
0 as approved_count,
0 as approved_amount,
count(c.trans_id) as chargeback_count,
sum(t.amount) as chargeback_amount
from Chargebacks as c
left join Transactions as t on t.id = c.trans_id
group by country, month) as tmp
group by month, country
执行结果
+-------+-------+--------------+---------------+----------------+-----------------+
|month |country|approved_count|approved_amount|chargeback_count|chargeback_amount|
+-------+-------+--------------+---------------+----------------+-----------------+
|2019-05|US |1 |1000 |1 |2000 |
|2019-06|US |2 |8000 |1 |1000 |
|2019-09|US |0 |0 |1 |5000 |
+-------+-------+--------------+---------------+----------------+-----------------+
