每次到访的交易次数分布：没人的档位也要显示 0
LeetCode 1336 · 频次分布、生成数字序列、LEFT JOIN 补零
面试怎么回答
先算每次到访的交易数：到访 LEFT JOIN 交易，按用户和日期 COUNT(amount)，零交易的到访得到 0；再按交易数分组得到分布；最后用递归 CTE 生成 0 到最大交易数的整数序列去左关联，补齐没有到访的档位。「先生成完整维度、再左关联补零」是做分布图、日历报表的通用办法。
先把这段讲顺；面试官追问时，再展开下面的原理和细节。
详细讲解
题意
到访表记录用户每次到银行的日期，交易表记录用户在到访当天的每笔交易。统计「一次到访做了 k 笔交易」的到访次数分布：k 从 0 一直到单次到访的最大交易数，中间没有到访的档位也要显示 0。
表结构
- Visits：user_id、visit_date；(user_id, visit_date) 为主键
- Transactions：user_id、transaction_date、amount；没有主键，可能有重复行
思路
分三步：一是每次到访 LEFT JOIN 当天的交易，按（用户, 日期）计数，得到每次到访的交易数（没交易为 0）；二是按交易数分组，得到每个档位的到访次数；三是生成 0 到最大交易数的连续整数序列，用它 LEFT JOIN 第二步的结果，没有的档位补 0。难点在第三步：MySQL 没有现成的序列函数，原解法借用交易表的行号来造序列，也可以用递归 CTE。
参考解法
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)另一种写法：递归 CTE 生成 0..最大值 的序列
WITH RECURSIVE per_visit AS (
SELECT v.user_id, v.visit_date, COUNT(t.amount) AS cnt
FROM Visits v
LEFT JOIN Transactions t ON v.user_id = t.user_id AND v.visit_date = t.transaction_date
GROUP BY v.user_id, v.visit_date
), seq AS (
SELECT 0 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < (SELECT MAX(cnt) FROM per_visit)
)
SELECT s.n AS transactions_count, COUNT(p.user_id) AS visits_count
FROM seq s
LEFT JOIN per_visit p ON p.cnt = s.n
GROUP BY s.n
ORDER BY s.n易错点
- 只做前两步，中间缺失的档位（比如没有人一次做 2 笔交易）就不会出现。
- 计数时用 COUNT(t.amount) 而不是 COUNT(*)，否则没交易的到访会被算成 1 笔。
- 用行号造序列的前提是表的行数不少于最大交易数，这里交易表行数一定够。