1193. 每月交易 I

表：Transactions

1
2
3
4
5
6
7
8
9
10
11
12
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 ["approved", "declined"] 之一。

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
输入：
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 转换为 YYYY-MM 格式（不同数据库函数略有差异，但核心是截取年月部分）；
  2. 分组维度：按 “月份” 和 “国家” 双维度分组（GROUP BY month, country），确保统计粒度正确；
  3. 条件聚合统计：
    • 总事务数：直接计数（COUNT(*)，每个分组的行数即总事务数）；
    • 已批准事务数：仅统计 state='approved' 的行数（用 CASE WHEN 或 SUM(条件) 实现）；
    • 总事务金额：求和所有 amount（SUM(amount)）；
    • 已批准事务金额：仅求和 state='approved' 的 amount（条件求和）。

代码实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,  -- 提取年月，格式为YYYY-MM
    country,
    COUNT(*) AS trans_count,  -- 总事务数（分组内总行数）
    -- 已批准事务数：统计state='approved'的行数
    SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved_count,
    SUM(amount) AS trans_total_amount,  -- 总事务金额
    -- 已批准事务总金额：仅求和approved的amount
    SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total_amount
FROM
    Transactions
-- 按月份和国家分组，确保统计维度正确
GROUP BY
    month, country;  -- 此处month是SELECT中定义的别名，MySQL支持；其他数据库需用原始函数（如DATE_FORMAT(trans_date, '%Y-%m')）