作者:whisper
链接:http://proprogrammar.com:443/article/641
声明:请尊重原作者的劳动,如需转载请注明出处
Table:
Transactions
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date | +---------------+---------+ id 是这个表的主键。 该表包含有关传入事务的信息。 state 列类型为 “[”批准“,”拒绝“] 之一。
编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
查询结果格式如下所示:
Transactions table: +------+---------+----------+--------+------------+ | id | country | state | amount | trans_date | +------+---------+----------+--------+------------+ | 121 | US | approved | 1000 | 2018-12-18 | | 122 | US | declined | 2000 | 2018-12-19 | | 123 | US | approved | 2000 | 2019-01-01 | | 124 | DE | approved | 2000 | 2019-01-07 | +------+---------+----------+--------+------------+ Result table: +----------+---------+-------------+----------------+--------------------+-----------------------+ | 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 | +----------+---------+-------------+----------------+--------------------+-----------------------+
select m.month, t.country, count(1) trans_count, sum(case when t.state = 'approved' then 1 else 0 end) approved_count,
sum(t.amount) trans_total_amount, sum(case when t.state = 'approved' then t.amount else 0 end) approved_total_amount
from Transactions t inner join (
select distinct date_format(trans_date, '%Y-%m') month
from Transactions
) m on date_format(t.trans_date, '%Y-%m') = m.month
group by m.month, t.country
要求每月每个国家/地区的信息,那么先求所有的年月,再按年月,国家/地区分组,利用sum+case, sum求统计信息,学一下多条件group by,date_format,distinct,sum+case
再看另一种解法
select date_format(trans_date,'%Y-%m') as month,
country,
count(state) as trans_count,
count(case when state='approved' then 1 else null end) as approved_count,
sum(amount) as trans_total_amount,
sum(case when state='approved' then amount else 0 end) as approved_total_amount
from Transactions
group by date_format(trans_date,'%Y-%m'),country
这里没有用连接来获取月份(自己是简单的思路一步步做,要什么就查什么,然后组合起来),直接在group by中获取年月
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 | | charge_date | date | +----------------+---------+ 退单包含有关放置在事务表中的某些事务的传入退单的基本信息。 trans_id 是 transactions 表的 id 列的外键。 每项退单都对应于之前进行的交易,即使未经批准。
编写一个 SQL 查询,以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。
注意:在您的查询中,给定月份和国家,忽略所有为零的行。
查询结果格式如下所示:
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 | approved | 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 | +------------+------------+ Result 表: +----------+---------+----------------+-----------------+-------------------+--------------------+ | month | country | approved_count | approved_amount | chargeback_count | chargeback_amount | +----------+---------+----------------+-----------------+-------------------+--------------------+ | 2019-05 | US | 1 | 1000 | 1 | 2000 | | 2019-06 | US | 3 | 12000 | 1 | 1000 | | 2019-09 | US | 0 | 0 | 1 | 5000 | +----------+---------+----------------+-----------------+-------------------+--------------------+
select r.month, r.country, sum(case r.status when 'approved' then r.count else 0 end) approved_count,
sum(case r.status when 'approved' then r.amount else 0 end) approved_amount,
sum(case r.status when 'back' then r.count else 0 end) chargeback_count,
sum(case r.status when 'back' then r.amount else 0 end) chargeback_amount
from(
select m.month, t.country, count(1) count, sum(t.amount) amount, 'back' status
from(
select distinct date_format(trans_date, '%Y-%m') month
from Transactions
union
select distinct date_format(trans_date, '%Y-%m')
from Chargebacks
) m inner join Chargebacks c on m.month = date_format(c.trans_date, '%Y-%m')
inner join Transactions t on t.id = c.trans_id
group by m.month, t.country
union
select m.month, t.country, count(1) count, sum(t.amount) amount, 'approved'
from(
select distinct date_format(trans_date, '%Y-%m') month
from Transactions
union
select distinct date_format(trans_date, '%Y-%m')
from Chargebacks
) m inner join Transactions t on m.month = date_format(t.trans_date, '%Y-%m') and t.state = 'approved'
group by m.month, t.country
) r
group by r.month, r.country
嵌套+连接,分别求出批准和退单信息,union在一起,最后统计一下相关信息,学一下多字段group by,多表连接,union作为from条件,distinct,sum+case求记录数和求数量和
下面看另一种解法
select date_format(a.trans_date,'%Y-%m') as month,
a.country as country,
count(case when a.type='approved' then 1 else null end) as approved_count,
#sum(case when a.type='approved' then 1 else 0 end) as approved_count,
sum(case when a.type='approved' then a.amount else 0 end) as approved_amount,
count(case when a.type='chargeback' then 1 else null end) as chargeback_count,
#sum(case when a.type='chargeback' then 1 else 0 end) as chargeback_count,
sum(case when a.type='chargeback' then a.amount else 0 end) as chargeback_amount
from
(select id,
country,
'approved' as type,
amount,
trans_date
from Transactions
where state='approved'
union all
select c.trans_id as id,
t.country as country,
'chargeback' as type,
t.amount as amount,
c.trans_date as trans_date
from Chargebacks c join Transactions t on c.trans_id=t.id
)a
group by date_format(a.trans_date,'%Y-%m'),a.country
上面的做法太复杂,这种简洁多了,作为中等题,不应该做成上面那么复杂,一定有简洁的解法,思路都是差不多的,分别求批准和退单的情况,连接在一起,最后统计一下相关信息
亲爱的读者:有时间可以点赞评论一下
全部评论