目录
题目
表:Transactions
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date | +---------------+---------+ id 是这个表的主键。 该表包含有关传入事务的信息。 state 列类型为 ["approved", "declined"] 之一。
编写 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: 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 | +------+---------+----------+--------+------------+ 输出: +----------+---------+-------------+----------------+--------------------+-----------------------+ | 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 | +----------+---------+-------------+----------------+--------------------+-----------------------+
分析
数据表中的 trans_date 精确到日,查找每个月的
通过date_format将日期格式转为年月,date_format(trans_date, '%Y-%m')
按照每个月和每个国家聚合
聚合函数,group by month, country
查找总的事务数
count(*) trans_count
查找已批准的事务数
count(if(state = 'approved',1,null)) approved_count
查找总金额
sum(amount) trans_total_amount
查找已批准的事物的总金额
sum(case when state = 'approved' then amount else 0 end) approved_total_amount
简化 sum(if(state = 'approved', amount, 0))
代码
select
date_format(trans_date, '%Y-%m') month,
country,
count(*) trans_count,
count(if(state = 'approved',1,null)) approved_count,
sum(amount) trans_total_amount,
sum(case when state = 'approved' then amount else 0 end) approved_total_amount
from Transactions
group by month, country