文章目录
- 1. 题目
- 2. 解题
1. 题目
表: Visits
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| visit_date | date |
+---------------+---------+
(user_id, visit_date) 是该表的主键
该表的每行表示 user_id 在 visit_date 访问了银行
表: Transactions
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| user_id | int |
| transaction_date | date |
| amount | int |
+------------------+---------+
该表没有主键，所以可能有重复行
该表的每一行表示 user_id 在 transaction_date 完成了一笔 amount 数额的交易
可以保证用户 (user) 在 transaction_date 访问了银行
(也就是说 Visits 表包含 (user_id, transaction_date) 行)
银行想要得到银行客户在一次访问时的交易次数和相应的在一次访问时该交易次数的客户数量的图表
写一条 SQL 查询多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等
结果包含两列：
- transactions_count： 客户在一次访问中的交易次数
- visits_count： 在 transactions_count 交易次数下相应的一次访问时的客户数量
transactions_count 的值从 0 到所有用户一次访问中的 max(transactions_count)
按 transactions_count 排序
下面是查询结果格式的例子：
Visits 表:
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1 | 2020-01-01 |
| 2 | 2020-01-02 |
| 12 | 2020-01-01 |
| 19 | 2020-01-03 |
| 1 | 2020-01-02 |
| 2 | 2020-01-03 |
| 1 | 2020-01-04 |
| 7 | 2020-01-11 |
| 9 | 2020-01-25 |
| 8 | 2020-01-28 |
+---------+------------+
Transactions 表:
+---------+------------------+--------+
| user_id | transaction_date | amount |
+---------+------------------+--------+
| 1 | 2020-01-02 | 120 |
| 2 | 2020-01-03 | 22 |
| 7 | 2020-01-11 | 232 |
| 1 | 2020-01-04 | 7 |
| 9 | 2020-01-25 | 33 |
| 9 | 2020-01-25 | 66 |
| 8 | 2020-01-28 | 1 |
| 9 | 2020-01-25 | 99 |
+---------+------------------+--------+
结果表:
+--------------------+--------------+
| transactions_count | visits_count |
+--------------------+--------------+
| 0 | 4 |
| 1 | 5 |
| 2 | 0 |
| 3 | 1 |
+--------------------+--------------+
* 对于 transactions_count = 0,
* visits 中 (1, "2020-01-01"), (2, "2020-01-02"),
* (12, "2020-01-01") 和 (19, "2020-01-03")
* 没有进行交易，所以 visits_count = 4 。
* 对于 transactions_count = 1,
* visits 中 (2, "2020-01-03"), (7, "2020-01-11"),
* (8, "2020-01-28"), (1, "2020-01-02")
* 和 (1, "2020-01-04") 进行了一次交易，
* 所以 visits_count = 5 。
* 对于 transactions_count = 2,
* 没有客户访问银行进行了两次交易，
* 所以 visits_count = 0 。
* 对于 transactions_count = 3,
* visits 中 (9, "2020-01-25") 进行了三次交易，
* 所以 visits_count = 1 。
* 对于 transactions_count >= 4,
* 没有客户访问银行进行了超过3次交易，
* 所以我们停止在 transactions_count = 3 。
如下是这个例子的图表：
来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/number-of-transactions-per-visit
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
2. 解题
- 先生成第一列，多了没关系，一会筛选，注意加个0
select 0 transactions_count
union all
select row_number() over(order by transaction_date) transactions_count
from Transactions
- with as 创建 临时表 t
select distinct v.user_id, visit_date, transaction_date,count(*) over(partition by user_id, visit_date, visit_date=transaction_date) times
from Visits v left join Transactions tr
on v.user_id=tr.user_id and v.visit_date=tr.transaction_date
{"headers": ["user_id", "visit_date", "transaction_date", "times"],
"values": [
[1, "2020-01-01", null, 1],
[1, "2020-01-02", "2020-01-02", 1],
[1, "2020-01-04", "2020-01-04", 1],
[2, "2020-01-02", null, 1],
[2, "2020-01-03", "2020-01-03", 1],
[7, "2020-01-11", "2020-01-11", 1],
[8, "2020-01-28", "2020-01-28", 1],
[9, "2020-01-25", "2020-01-25", 3],
[12, "2020-01-01", null, 1],
[19, "2020-01-03", null, 1]]}
- 统计数量
select 0 transactions_count, count(*) visits_count
from t
where transaction_date is null
union all
select times transactions_count, count(*) visits_count
from t
where transaction_date is not null
group by times
{"headers": ["transactions_count", "visits_count"],
"values": [[0, 4], [1, 5], [3, 1]]}
- 左连接，筛选数据
# Write your MySQL query statement below
with t as
(select distinct v.user_id, visit_date, transaction_date,count(*) over(partition by user_id, visit_date, visit_date=transaction_date) timesfrom Visits v left join Transactions tron v.user_id=tr.user_id and v.visit_date=tr.transaction_date
)select t1.transactions_count, ifnull(t2.visits_count,0) visits_count
from
(select 0 transactions_countunion allselect row_number() over(order by transaction_date) transactions_countfrom Transactions
) t1
left join
(select 0 transactions_count, count(*) visits_countfrom twhere transaction_date is nullunion allselect times transactions_count, count(*) visits_countfrom twhere transaction_date is not nullgroup by times
) t2
on t1.transactions_count = t2.transactions_count
where t1.transactions_count <= (select max(transactions_count) from (select 0 transactions_count, count(*) visits_countfrom twhere transaction_date is nullunion allselect times transactions_count, count(*) visits_countfrom twhere transaction_date is not nullgroup by times) t3)
我的CSDN博客地址 https://michael.blog.csdn.net/
长按或扫码关注我的公众号（Michael阿明），一起加油、一起学习进步！
