• LeetCode 【困难】数据库-第1336(超困难):每次访问的交易次数


    题目

    数据

    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;
    
  • 相关阅读:
    Rain 学习自用贴
    連休計画
    091207 晴
    记下London奥运我喜欢的几首歌
    20世纪最好的10个算法(转)
    一日一美女:告诉你什么是斐波那契螺旋线
    矩阵鞍点的寻找
    日本の物語
    a new start~ s!
    Photoshop CS5运用色彩原理去除半透明水印(转)
  • 原文地址:https://www.cnblogs.com/Tdazheng/p/14982791.html
Copyright © 2020-2023  润新知