Leetcode 1193. 每月交易Ⅰ

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

本题使用到的 MySQL 函数的说明：

DATE_FORMAT(date, format)：用于以不同的格式显示日期/时间数据。date 参数是合法的日期，format 规定日期/时间的输出格式。

DATE_FORMAT() 函数、GROUP BY

思路

本题要求 查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额，我们可以将这句话拆分成几个子任务：

查找每个月和每个国家/地区。数据表中的 trans_date 是精确到日，我们可以使用 DATE_FORMAT() 函数将日期按照年月 %Y-%m 输出。比如将 2019-01-02 转换成 2019-01 。

DATE_FORMAT(trans_date, '%Y-%m')

获取到所有的月份后，使用 GROUP BY 聚合每个月和每个国家的记录就完成了第一步。

查找总的事务数。第一步已经将数据按月和国家聚合，只需要使用 COUNT 函数就能获取到总的事务数。

COUNT(*) AS trans_count

查找总金额。使用 SUM 函数计算总金额。

SUM(amount) AS trans_total_amount

查找已批准的事物数。已批准的事物的 state 标记为 approved。首先使用 IF 函数将 state = 'approved' 的记录标记为 1，否则为 NULL。再使用 COUNT 计算总量。

COUNT(IF(state = 'approved', 1, NULL)) AS approved_count

查找已批准的事物的总金额。和第四步一样，先使用 IF 函数，再使用 SUM 函数。

SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount

sql

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) 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 month, country