每月交易
需求一:查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
展示效果:
month | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
---|---|---|---|---|---|
2018-12 | US | 2 | 1 | 3000 | 1000 |
2019-01 | US | 1 | 1 | 2000 | 2000 |
2019-01 | DE | 1 | 1 | 2000 | 2000 |
2019-05 | US | 2 | 1 | 3000 | 1000 |
2019-06 | US | 3 | 2 | 12000 | 8000 |
create table if not exists 58_Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date);
create table if not exists Chargebacks (trans_id int, trans_date date);
truncate table 58_Transactions;
insert into 58_Transactions (id, country, state, amount, trans_date) values (101, 'US', 'approved', 1000, '2018-12-18');
insert into 58_Transactions (id, country, state, amount, trans_date) values (102, 'US', 'declined', 2000, '2018-12-19');
insert into 58_Transactions (id, country, state, amount, trans_date) values (103, 'US', 'approved', 2000, '2019-01-01');
insert into 58_Transactions (id, country, state, amount, trans_date) values (104, 'DE', 'approved', 2000, '2019-01-07');
insert into 58_Transactions (id, country, state, amount, trans_date) values (105, 'US', 'approved', 1000, '2019-05-18');
insert into 58_Transactions (id, country, state, amount, trans_date) values (106, 'US', 'declined', 2000, '2019-05-19');
insert into 58_Transactions (id, country, state, amount, trans_date) values (107, 'US', 'approved', 3000, '2019-06-10');
insert into 58_Transactions (id, country, state, amount, trans_date) values (108, 'US', 'declined', 4000, '2019-06-13');
insert into 58_Transactions (id, country, state, amount, trans_date) values (109, 'US', 'approved', 5000, '2019-06-15');
truncate table 58_Chargebacks;
insert into 58_Chargebacks (trans_id, trans_date) values (102, '2019-05-29');
insert into 58_Chargebacks (trans_id, trans_date) values (101, '2019-06-30');
insert into 58_Chargebacks (trans_id, trans_date) values (105, '2019-09-18');
最终SQL:
select
date_format(trans_date,'%Y-%m') as month,
country,
count(*) as trans_count,
sum(if(state='approved',1,0)) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state='approved',amount,0)) as approved_total_amount
from
58_Transactions t
group by
date_format(trans_date,'%Y-%m'),
country;