LeetCode-1336. 每次访问的交易次数（困难）

表: 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
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

审题：第一张表是访问表，记录每次访问，但是不一定交易，第二张表记录交易信息。查询交易了0次，一次等的人数。

第一张表记录一天访问，但有些人在同一天交易多次。

思考：

解题：

首先，对于每个用户的每次访问，一共有几个transaction（交易）可以用以下SQL获得：

SELECT IFNULL(cnt, 0) cnt, COUNT(*) num FROM Visits t1 LEFT JOIN
(SELECT user_id, transaction_date, COUNT(*) cnt FROM Transactions GROUP BY user_id, transaction_date)t2
ON t1.user_id=t2.user_id AND visit_date=transaction_date GROUP BY IFNULL(cnt, 0)
-- 练习
select ifnull(cnt,0) cnt, count(*) num from visits t1 left join 
-- 查询这个id在这个日期下有几次交易。
(select user_id, transaction_date,count(*) cnt from Transactions 
group by user_id,transaction_date)t2
--
on t1.user_id = t2.user_id and visit_date = transaction_date group by ifnull(cnt,0)

然后，需要生成一张从0到上面这张表的最大值(设为N)的表和它进行JOIN，来获得最终结果。考虑到最大出现的数字，肯定不会超过Transaction表的行数，使用Transaction开表：

SELECT (@t := @t+1) AS id FROM Transactions t0, (SELECT @t := -1) b

然后让数字小于等于最大值，最大值可以通过这个SQL获取：

SELECT MAX(cnt) maxi FROM Visits t11 LEFT JOIN
(SELECT user_id, transaction_date, COUNT(*) cnt FROM Transactions GROUP BY user_id, transaction_date

 生成数字表的SQL变为：

SELECT (@t := @t+1) AS id FROM Transactions t0, (SELECT @t := -1) b,
(SELECT MAX(cnt) maxi FROM Visits t11 LEFT JOIN
(SELECT user_id, transaction_date, COUNT(*) cnt FROM Transactions GROUP BY user_id, transaction_date)t21
ON t11.user_id=t21.user_id AND visit_date=transaction_date) t31 WHERE @t < maxi

但是这样存在一个问题，如果Transaction为空，则开不出0这个数据，所以处理一下，让@t从1开始，然后手动UNION一个为0的行，结果：

SELECT (@t := @t+1) AS id FROM Transactions t0, (SELECT @t := 0) b,
(SELECT MAX(cnt) maxi FROM Visits t11 LEFT JOIN
(SELECT user_id, transaction_date, COUNT(*) cnt FROM Transactions GROUP BY user_id, transaction_date)t21
ON t11.user_id=t21.user_id AND visit_date=transaction_date) t31 WHERE @t < maxi 
 UNION SELECT 0

然后将数字表和最开始的表1左连接即可：

SELECT CEIL(id) transactions_count, IFNULL(num, 0) visits_count FROM
(SELECT (@t := @t+1) AS id FROM Transactions t0, (SELECT @t := 0) b,
(SELECT MAX(cnt) maxi FROM Visits t11 LEFT JOIN
(SELECT user_id, transaction_date, COUNT(*) cnt FROM Transactions GROUP BY user_id, transaction_date)t21
ON t11.user_id=t21.user_id AND visit_date=transaction_date) t31 WHERE @t < maxi 
 UNION SELECT 0) t33
LEFT JOIN
(SELECT IFNULL(cnt, 0) cnt, COUNT(*) num FROM Visits t1 LEFT JOIN
(SELECT user_id, transaction_date, COUNT(*) cnt FROM Transactions GROUP BY user_id, transaction_date)t2
ON t1.user_id=t2.user_id AND visit_date=transaction_date GROUP BY IFNULL(cnt, 0)) t3
ON t33.id=t3.cnt ORDER BY id

 知识点：本题比较难，需要继续深入理解