目录
题目链接(无_力扣VIP_略过)
- 链接: 15分钟没思路建议直接看答案
1.读题(建议使用这种表结构_数据对比看)
__________________________________________
题目SQL Schema建表语句_数据
Create table If Not Exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)
Create table If Not Exists Chargebacks (trans_id int, trans_date date)
Truncate table Transactions
insert into Transactions (id, country, state, amount, trans_date) values ('101', 'US', 'approved', '1000', '2019-05-18')
insert into Transactions (id, country, state, amount, trans_date) values ('102', 'US', 'declined', '2000', '2019-05-19')
insert into Transactions (id, country, state, amount, trans_date) values ('103', 'US', 'approved', '3000', '2019-06-10')
insert into Transactions (id, country, state, amount, trans_date) values ('104', 'US', 'declined', '4000', '2019-06-13')
insert into Transactions (id, country, state, amount, trans_date) values ('105', 'US', 'approved', '5000', '2019-06-15')
Truncate table Chargebacks
insert into Chargebacks (trans_id, trans_date) values ('102', '2019-05-29')
insert into Chargebacks (trans_id, trans_date) values ('101', '2019-06-30')
insert into Chargebacks (trans_id, trans_date) values ('105', '2019-09-18')
2.答案_一图解
一图解
答案
-- 方法1
WITH e1 AS ( -- 创建一个公共表表达式 (CTE) 名为 e1
SELECT LEFT(trans_date, 7) AS month, id, -- 从 Transactions 表中提取交易日期的年月部分作为 month，并选取 id
country, state, amount -- 选择国家、状态和金额
FROM transactions
UNION ALL -- 合并下一个查询的结果
SELECT LEFT(c.trans_date, 7) AS month, trans_id AS id, -- 从 Chargebacks 表中提取冲正日期的年月部分，并将交易 ID 作为 id
country, NULL AS state, amount -- 选择国家，状态设为 NULL，选取金额
FROM Chargebacks c
JOIN transactions t ON c.trans_id = t.id -- 通过交易 ID 连接 Chargebacks 和 Transactions 表
)
SELECT month, country, -- 从 CTE e1 中选择月份和国家
COUNT(IF(state='approved', id, NULL)) AS approved_count, -- 计算每个月每个国家的批准数量（状态为 'approved'）
SUM(IF(state='approved', amount, 0)) AS approved_amount, -- 计算每个月每个国家的批准总金额
COUNT(IF(state IS NULL, id, NULL)) AS chargeback_count, -- 计算每个月每个国家的冲正数量（状态为 NULL）
SUM(IF(state IS NULL, amount, 0)) AS chargeback_amount -- 计算每个月每个国家的冲正总金额
FROM e1
GROUP BY month, country -- 按月份和国家分组
HAVING approved_amount > 0 OR chargeback_amount > 0; -- 只返回批准金额大于 0 或冲正金额大于 0 的结果
-- 可以缩写为 approved_amount or chargeback_amount
-- 最好不要怎么写, 真的真的一点都不好理解, 类似 avg(字段=条件), 我就不说了
-- 方法2
SELECT month, country,
COUNT(IF(tag=1, 1, NULL)) AS approved_count, -- 计算每个月每个国家的批准数量（tag=1表示批准）
SUM(IF(tag=1, amount, 0)) AS approved_amount, -- 计算每个月每个国家的批准总金额
COUNT(IF(tag=0, 1, NULL)) AS chargeback_count, -- 计算每个月每个国家的冲正数量（tag=0表示冲正）
SUM(IF(tag=0, amount, 0)) AS chargeback_amount -- 计算每个月每个国家的冲正总金额
FROM (
SELECT country, amount, 1 AS tag, -- 从 Transactions 表中选取国家、金额，标记为批准（tag=1）
date_format(trans_date, '%Y-%m') AS month -- 将交易日期格式化为 'YYYY-MM' 格式作为月份
FROM Transactions
WHERE state='approved' -- 只选择状态为 'approved' 的交易记录
UNION ALL -- 合并批准记录和冲正记录
SELECT country, amount, 0 AS tag, -- 从 Chargebacks 表中选取国家、金额，标记为冲正（tag=0）
date_format(c.trans_date, '%Y-%m') AS month -- 将冲正日期格式化为 'YYYY-MM' 格式作为月份
FROM Transactions AS t RIGHT OUTER JOIN Chargebacks AS c -- 右外连接 Transactions 和 Chargebacks 表
ON t.id = c.trans_id -- 通过交易 ID 进行连接
) AS temp -- 将上述合并结果作为临时表 temp
GROUP BY month, country; -- 按照月份和国家分组，计算批准和冲正的数量和金额
------------------------------------------------------------------------------
解题分析图览
方法1
方法2
难点分析
- 数据合并：需要将Transactions表和Chargebacks表的数据合并，以便于后续的计算和分析。
- 状态判断：需要根据交易的状态（approved或declined）以及是否存在冲正记录来计算批准和冲正的数量和金额。
- 日期格式化：需要将交易日期和冲正日期格式化为’YYYY-MM’格式，以便于按月份进行分组和计算。
- 分组计算：需要按月份和国家进行分组，并计算每个组内的批准和冲正数量和金额。
- 结果过滤：需要过滤出批准或冲正金额大于0的结果，以避免显示无意义的数据。
关键总结
- CTE（公共表表达式）的使用：通过创建CTE来合并和处理数据，使查询更加清晰和易于理解。
- 条件聚合函数的使用：使用COUNT和SUM函数结合IF条件来计算批准和冲正的数量和金额。
- 日期函数的使用：使用LEFT或DATE_FORMAT函数来提取或格式化日期，以便于按月份进行分组和计算。
- JOIN操作的使用：通过JOIN操作来连接不同的表，以便于获取所需的数据。
- GROUP BY和HAVING子句的使用：通过GROUP BY子句按月份和国家进行分组，并通过HAVING子句过滤出有意义的结果。