【LeetCode题库】1193. 每月交易 I —— DATE_FORMAT() 函数、IF() 函数的使用。

我是一名立志把细节说清楚的博主，欢迎【关注】🎉 ~

原创不易， 如果有帮助 ，记得【点赞】【收藏】 哦~ ❥(^_-)~

如有错误、疑惑，欢迎【评论】指正探讨，我会尽可能第一时间回复的，谢谢支持

原题

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

题解

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

解题笔记

1. 查找每个月和每个国家/地区。
   • DATE_FORMAT() 函数，转换日期格式。将原先精确到日的日期，转换为精确到月的日期。

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

   • GROUP BY 关键字，按月份和国家进行分组。
     • 关键字详解请看文章：【MySQL】数据分组（关键字：GROUP BY）过滤分组（关键字：HAVING）

     GROUP BY month, country
     

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

   COUNT(*) AS trans_count
   

3. 使用 SUM 函数计算总金额。

   SUM(amount) AS trans_total_amount
   

4. 查找已批准的事物数。
   已批准的事物的 state 标记为 approved。首先使用 IF 函数将 state = 'approved' 的记录标记为 1，否则为 NULL。再使用 COUNT 计算总量。
   • 数值为1的就会被 COUNT() 函数记录。数值为 NULL 的不会被记录。

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

5. 查找已批准的事物的总金额。
   和第四步一样，先使用 IF 函数，再使用 SUM 函数。
   • 和上面不同的是，最外层不是 COUNT 函数，所以为了符合 SUM 函数加法的运算规律，将不符合条件的数值设置为 0，而不是NULL。

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

我是一名立志把细节说清楚的博主，欢迎【关注】🎉 ~

原创不易， 如果有帮助 ，记得【点赞】【收藏】 哦~ ❥(^_-)~

如有错误、疑惑 ，欢迎【评论】指正探讨，我会尽可能第一时间回复的，谢谢支持