力扣数据库1193. 每月交易 I

一、数据

表：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                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

二、分析

1、由示例数据可以看出需要找出每个月和每个国家/地区的相关数据，因此需要对`trans_date`列和`country`进行GROUP BY分组计算，同时给的原始数据为`date`类型，因此也需要对`trans_date`列进行截取，提取年份和月份。

2、`state`列代表的就是该事务是否被批准，因为`state`列就有两个数据，因此可以使用IF函数删选被批准和未被批准的事务。

三、代码

select 
    substring(trans_date,1,7) as month, -- 对trans_date列进行截取，从1(头)开始，截取7位就能得到年份和月份
    country,
    count(id) as trans_count, -- 事务的总数
    count(if(state = 'approved',1,null)) as approved_count, -- 计算被批准的事务总数，IF(条件,值1,值2) --> 条件为真返回值1，为假返回值2，且count函数不计算为空值的情况
    sum(amount) as trans_total_amount, -- 总金额
    sum(if(state = 'approved',amount,0)) as approved_total_amount -- 计算被批准的事务的总金额，IF(条件,值1,值2) --> 条件为真返回值1，为假返回值2
from Transactions
group by month,country; -- 对月份和国家进行分组

四、总结

1、考察了IF函数的使用，IF(条件,值1,值2)，条件为真，返回值1，反之返回值2。

2、考察了count函数的使用技巧，count函数计算行数，且自动跳过为空的行。