sql-每次访问的交易次数

背景：1⃣️客户只有访问银行才可以交易，客户访问一次交易次可能是0，1，2，3，。。。。。。
2⃣️需求是求出访问一次银行交易次数为0，1，2，3，。。。。。的人数分别是多少

• -1 建表插入语句
  Create table If Not Exists 76_Visits (user_id int, visit_date DATETIME );
  Create table If Not Exists 76_Transactions (user_id int, transaction_date DATETIME, amount int);
  Truncate table 76_Visits;
  insert into 76_Visits (user_id, visit_date) values (‘1’, ‘2020-01-01 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘2’, ‘2020-01-02 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘12’, ‘2020-01-01 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘19’, ‘2020-01-03 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘1’, ‘2020-01-02 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘2’, ‘2020-01-03 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘1’, ‘2020-01-04 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘7’, ‘2020-01-11 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘9’, ‘2020-01-25 00:00:00’);
  insert into 76_Visits (user_id, visit_date) values (‘8’, ‘2020-01-28 00:00:00’);
  Truncate table 76_Transactions;
  insert into 76_Transactions (user_id, transaction_date, amount) values (‘1’, ‘2020-01-02 00:00:00’, ‘120’);
  insert into 76_Transactions (user_id, transaction_date, amount) values (‘2’, ‘2020-01-03 00:00:00’, ‘22’);
  insert into 76_Transactions (user_id, transaction_date, amount) values (‘7’, ‘2020-01-11 00:00:00’, ‘232’);
  insert into 76_Transactions (user_id, transaction_date, amount) values (‘1’, ‘2020-01-04 00:00:00’, ‘7’);
  insert into 76_Transactions (user_id, transaction_date, amount) values (‘9’, ‘2020-01-25 00:00:00’, ‘33’);
  insert into 76_Transactions (user_id, transaction_date, amount) values (‘9’, ‘2020-01-25 00:00:00’, ‘66’);
  insert into 76_Transactions (user_id, transaction_date, amount) values (‘8’, ‘2020-01-28 00:00:00’, ‘1’);
  insert into 76_Transactions (user_id, transaction_date, amount) values (‘9’, ‘2020-01-25 00:00:00’, ‘99’);
  SELECT * from 76_Visits order by user_id LIMIT 100; SELECT * from 76_Transactions order by user_id LIMIT 100;
  –2 需求
  – 查询多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等

– 结果包含两列：

– - transactions_count： 客户在一次访问中的交易次数
– - visits_count： 在 transactions_count 交易次数下相应的一次访问时的客户数量
– 不访问银行无法完成交易 ，访问一次银行可能完成多次交易
– 展示效果：

– ```
– ±-------------------±-------------+
– | 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 。
解答
–left join 和full join 效果一样
CREATE table user_date_transactions_table
AS
SELECT v1.user_id
,v1.visit_date
,sum(IF(amount IS NULL, 0, 1)) user_visits_num
FROM 76_Visits v1
LEFT JOIN 76_Transactions t1
ON v1.user_id = t1.user_id
AND v1.visit_date = t1.transaction_date
GROUP BY v1.user_id
,v1.visit_date
;
create TABLE final_table AS
SELECT
user_visits_num as transactions_count,
COUNT(1) visits_count
from user_date_transactions_table
group by user_visits_num;
SELECT v.rk AS transactions_count
,COALESCE( ft.visits_count,0)
FROM (
SELECT 0 rk
UNION
SELECT ROW_NUMBER() OVER(ORDER BY user_id ) rk
FROM 76_Visits
) v
LEFT JOIN final_table ft
ON v.rk = transactions_count
;
– 方法二 借助外力 rank排序关联表
SELECT
t5.rnb AS transactions_count,
coalesce(visits_count, 0) AS visits_count
FROM
(SELECT
0 AS rnb
UNION
SELECT
ROW_NUMBER() OVER () AS rnb
FROM
76_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
76_Visits t1
LEFT JOIN
76_Transactions t2
ON
t1.user_id = t2.user_id
AND
t1.visit_date = t2.transaction_date
GROUP BY
t1.user_id,
t1.visit_date ) t3
GROUP BY cnt ) t4
ON t5.rnb = t4.transactions_count;
– 方法三
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
76_Visits v
left join
76_Transactions t
on
v.user_id= t.user_id
and
v.visit_date=t.transaction_date
)t1
group by pcn;