「SQL面试题库」 No_95 每次访问的交易次数
今日真题
题目介绍： 每次访问的交易次数 number-of-transactions-per-visit
难度困难
SQL架构
表:
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 。
如下是这个例子的图表： ```
sql
SELECT *
FROM
(
SELECT t5.rnb AS transactions_count, IFNULL(visits_count, 0) AS visits_count
FROM
(
SELECT 0 AS rnb
UNION
SELECT ROW_NUMBER() OVER () AS rnb
FROM Transactions
) t5
LEFT JOIN
(
SELECT
cnt AS transactions_count
,COUNT(user_id) AS visits_count
FROM
(
SELECT t1.user_id, COUNT(t2.amount) AS cnt
FROM Visits t1
LEFT JOIN Transactions t2
ON t1.user_id = t2.user_id AND t1.visit_date = t2.transaction_date
GROUP BY user_id, visit_date
) t3
GROUP BY cnt
) t4
ON t5.rnb = t4.transactions_count
) t6
WHERE transactions_count <= (
SELECT COUNT(t2.amount) AS cnt
FROM Visits t1
LEFT JOIN Transactions t2
ON t1.user_id = t2.user_id AND t1.visit_date = t2.transaction_date
GROUP BY t1.user_id, visit_date
ORDER BY cnt DESC
LIMIT 1)难点 从0自增序列，2交易的人数为0
sql
select pcnt transactions_count,count(*) visits_count
from (
select visit_date,
sum(if(amount is null,0,1)) over(partition by transaction_date ) pcnt,
count(*) over(partition by visit_date ) tcnt
from Visits v left join Transactions t
on v.user_id= t.user_id and v.visit_date=t.transaction_date
)t1
group by pcnt这个得出结果是[0, 4], [1, 5], [3, 3] 少了[2,0] 还没想到什么好办法能把[2，0]加进去。。。
- 已经有灵感了？在评论区写下你的思路吧！