题目
数据
CREATE TABLE Visits(
user_id INT,
visit_date DATE);
CREATE TABLE Transactions1(
user_id INT,
transaction_date DATE,
amount INT);
INSERT INTO Visits VALUE(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');
INSERT INTO Transactions1 VALUE(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);
结果
cnt 为amount > 0 的数目
amt 为 amount
n 为transactions 表的行数编号
1.拼接两个数据表,因为字段数不同,故新增一列,且负值为0
select user_id,visit_date dt,0 amt from Visits # visits中没有amount,所以新建一列为amt,并全部赋值为0
union all
select user_id,transaction_date dt,amount amt from Transactions
2.筛选出来amount大于0的个数cnt
select *, sum(amt>0) cnt
from
( select user_id,visit_date dt,0 amt from Visits # visits中没有amount,所以新建一列为amt,并全部赋值为0
union all
select user_id,transaction_date dt,amount amt from Transactions
) all_data
group by user_id,dt
3.给交易表 transactions 编号从0开始
select 0 as n
union all
select (@x := @x+1) as n from Transactions,(select @x := 0) num # 给每一行编号,从0开始
4 n<= 某天最多交易次数
select max(cnt)
from
(select *,sum(amt>0) cnt
from
( select user_id,visit_date dt,0 amt from Visits
union all
select user_id,transaction_date dt,amount amt from Transactions
) all_data
group by user_id,dt
) tmp
5.最终链接两表、floor 向下取整,
select floor(n) transactions_count,
count(cnt) visits_count
from
( select *, sum(amt>0) cnt
from
( select user_id,visit_date dt,0 amt from Visits # visits中没有amount,所以新建一列为amt,并全部赋值为0
union all
select user_id,transaction_date dt,amount amt from Transactions
) all_data
group by user_id,dt
) tmp
right join
( select 0 as n
union all
select (@x := @x+1) as n from Transactions,(select @x := 0) num # 给每一行编号,从0开始
) nums
on nums.n = tmp.cnt
where
n <=
(select max(cnt) from
(select *,sum(amt>0) cnt
from
( select user_id,visit_date dt,0 amt from Visits
union all
select user_id,transaction_date dt,amount amt from Transactions
) all_data
group by user_id,dt
) tmp
)
group by n;