大数据SQL题

柔情只为你懂 2023-10-01 10:34 7阅读 0赞

**1：每个app下访问次数最多的5个用户**

用户登陆日志  
app, user\_id, datetime  
a1,user1,2020-01-01 00:00:00  
a2,user2,2020-01-01 00:00:01  
a3,user3,2020-01-01 00:00:02  
a4,user4,2020-01-01 00:00:03  
……  
输出每个app下访问次数最多的5个用户

考察**窗口函数**：

select app,userid,rank from (
    select app, userid, row_number() over (partition by app,userid order by c desc) as rank
    from (
        select app, userid, count(*) as c 
            from table group by app,userid
    )
    )
    where rank <5

**2：一定认识的用户的组合数**

现有城市网吧访问数据，字段：网吧id，访客id（身份证号），上线时间，下线时间  
规则1、如果有两个用户在一家网吧的前后上下线时间在10分钟以内，则两人可能认识  
规则2、如果这两个用户在三家以上网吧出现【规则1】的情况，则两人一定认识  
需求：  
该城市上网用户中两人一定认识的组合数。

**理解题目意思，梳理 join 条件，过滤符合条件的组合。**

**组合？t1.userid != t2.userid 这样在自己join自己的时候会出现两对。因此用的是t1.userid > t2.userid**

select u1, u2,  from (
    select t1.userid as u1, t2.userid as u2, t1.store_id as store_id from 
        select * from table as t1
    join
        select * from table as t2
    on t1.store_id = t2.store_id and t1.userid > t2.userid and 
    abs(t1.online_timestamp - t2.online_timestamp) < 600 and abs(t1.offline_timestamp - t2.offline_timestamp) < 600 )
    group by u1,u2
    having count (distinct store_id) > 3

**3：每年收入前一万**

现在有中国人从1980年到2020年每年每人的收入记录表A如下：  
id，year，income  
求每年收入前一万的人 **窗口函数+ desc**

select id,year,rank from (
    select id,year,row_number() over (partition by year order by income desc) as rank
    from table)
    where rank < 10000

深度思考：**每年13亿人，每个reducer处理的数据量很大，那么我们可以将id的前四位当作partition by 的参数，分组取topn，再合并。**

select id, year, row_number() over (partition by year order by income desc) as rank_year from (
    select id,year,rank from (
    select id, year, 
     row_number() over (partition by year,substr(id,1,4) order by inc