表: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 |
+----------+---------+-------------+----------------+--------------------+-----------------------+
这道题难度并不大,但可以练习我们对SQL函数的使用.
我们先写出我们需要查询哪些数据.
month: 每年的每月
country: 所属国家
trans_count: 事务的总数
approvced_count: 已经批准的事务
trans_total_amount: 事务总共的金额
approved_total_amount: 已经通过的事务的总金额
我们需要查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
所以需要统计每个月和每个国家/地区的数据.这里我们可以确定分组即为每年每月的国家.
group by country,"年-月"
其次就是事务数,一个是统计所有的事务,这个还是比较简单
count(id) trans_count,
而另一个统计批准的事务的总数,所以得需要添加一个判定条件
count(if(state = 'approved',1,null)) approved_count
另一个就是事务的总金额
sum(amount) trans_total_amount
批准的事务总金额,同样需要加入判定条件
sum(if(state = 'approved',amount,0)) approved_total_amount
基础架构已经搭好,我们写出总的SQL语句
select "年-月" `month`,
country,
count(id) trans_count,
count(if(state = 'approved',1,null)) approved_count,
sum(amount) trans_total_amount,
sum(if(state = 'approved',amount,0)) approved_total_amount
from Transactions
group by country,"年-月";
剩下就是年月的格式输出.我们可以了解一下DATE_FORMATE函数,可以讲时间的字符串转化成我们想要的模式,只不过SQL中可能与Java中的日期表达式不太一样.
我们可以写成
DATE_FORMAT(trans_date, '%Y-%m') `month`
所以总的SQL为
select DATE_FORMAT(trans_date, '%Y-%m') `month`,
country,
count(id) trans_count,
count(if(state = 'approved',1,null)) approved_count,
sum(amount) trans_total_amount,
sum(if(state = 'approved',amount,0)) approved_total_amount
from Transactions
group by country,DATE_FORMAT(trans_date, '%Y-%m');
但是也可以在group by处将日期表达式改写成为month
select DATE_FORMAT(trans_date, '%Y-%m') `month`,
country,
count(id) trans_count,
count(if(state = 'approved',1,null)) approved_count,
sum(amount) trans_total_amount,
sum(if(state = 'approved',amount,0)) approved_total_amount
from Transactions
group by country,`month`;
但其实,这里应该是会有一部分人有疑问的,了解SQL执行流程的都知道.group by 是会在select前执行.但是此时这里group by 处引用了select中的变量,这是怎么回事呢?
为什么 GROUP BY
能识别 SELECT
定义的别名?
这主要是因为 SQL 的执行和解析方式。大多数数据库系统在解析 SQL 时,会优化查询。在这个过程中:
- SQL 引擎解析
GROUP BY
时,会识别SELECT
中定义的表达式。 - 在
SELECT
中定义的month
只是对DATE_FORMAT(trans_date, '%Y-%m')
的别名,数据库在执行GROUP BY
时,实际上已经识别到它是DATE_FORMAT(trans_date, '%Y-%m')
,然后按这个表达式来进行分组。
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS `month`,
country,
count(id) AS trans_count,
count(IF(state = 'approved',1,NULL)) AS approved_count,
sum(amount) AS trans_total_amount,
sum(IF(state = 'approved',amount,0)) AS approved_total_amount
FROM Transactions
GROUP BY country, `month`;
DATE_FORMAT(trans_date, '%Y-%m')
:在SELECT
中计算每条记录的月份,并为其定义别名month
。GROUP BY country, month
:在这里,SQL 引擎会识别到month
实际上指的是DATE_FORMAT(trans_date, '%Y-%m')
,因此按月份和国家进行分组。
总结:尽管 SQL 查询执行顺序中,GROUP BY
先于 SELECT
执行,但 SQL 解析器会在执行之前处理好 SELECT
中的表达式,允许 GROUP BY
使用别名,这就是为什么可以在 GROUP BY
中使用 SELECT
定义的别名 month
。