SQL.LeetCode(1193):每月交易Ⅰ

表：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`;

1. DATE_FORMAT(trans_date, '%Y-%m')：在 SELECT 中计算每条记录的月份，并为其定义别名 month。
2. GROUP BY country, month：在这里，SQL 引擎会识别到 month 实际上指的是 DATE_FORMAT(trans_date, '%Y-%m')，因此按月份和国家进行分组。

总结：尽管 SQL 查询执行顺序中，GROUP BY 先于 SELECT 执行，但 SQL 解析器会在执行之前处理好 SELECT 中的表达式，允许 GROUP BY 使用别名，这就是为什么可以在 GROUP BY 中使用 SELECT 定义的别名 month。