走进大数据丨 分享两道大厂SQL笔试题

第一道SQL

我们有如下的用户访问数据

userId	visitDate	visitCount
u01	2019/1/21	5
u02	2019/1/23	6
u03	2019/1/22	8
u04	2019/1/20	3
u01	2019/1/23	6
u01	2019/2/21	8
u02	2019/1/23	6
u01	2019/2/22	4

要求使用SQL统计出每个用户每月的累积访问次数，如下表所示：

用户id	月份	小计	累积
u01	2019-01	11	11
u01	2019-02	12	23
u02	2019-01	12	12
u03	2019-01	8	8
u04	2019-01	3	3

数据：

u01     2019/1/21       5

u02     2019/1/23       6

u03     2019/1/22       8

u04     2019/1/20       3

u01     2019/1/23       6

u01     2019/2/21       8

u02     2019/1/23       6

u01     2019/2/22       4

1）创建表

create table action

(userId string,

visitDate string,

visitCount int)

row format delimited fields terminated by "\t";

1）修改数据格式

select

     userId,

     date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,

     visitCount

from

     action;t1

2）计算每人单月访问量

select

    userId,

    mn,

    sum(visitCount) mn_count

from

    t1

group by userId,mn;t2

3）按月累计访问量

select

    userId,

    mn,

    mn_count,

    sum(mn_count) over(partition by userId order by mn)

from t2;

最终SQL

SELECT

userId,

mn,

mn_count,

sum( mn_count ) over ( PARTITION BY userId ORDER BY mn )

FROM

(

SELECT

userId,

mn,

sum( visitCount ) mn_count

FROM

( SELECT userId, date_format( regexp_replace ( visitDate, '/', '-' ), 'yyyy-MM' ) mn, visitCount FROM action ) t1

GROUP BY

userId,

mn

) t2;

第二道SQL

有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：

u1 a

u2 b

u1 b

u1 a

u3 c

u4 b

u1 a

u2 c

u5 b

u4 b

u6 c

u2 c

u1 b

u2 a

u2 a

u3 a

u5 a

u5 a

u5 a

建表：

create table visit(user_id string,shop string) row format delimited fields terminated by '\t';

load data local inpath '/opt/module/datas/visit.txt' into table visit;

1）每个店铺的UV（访客数）

select shop,count(distinct user_id) from visit group by shop;

2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

（1）查询每个店铺被每个用户访问次数

select shop,user_id,count(*) ct

from visit

group by shop,user_id;t1

（2）计算每个店铺被用户访问次数排名

select shop,user_id,ct,rank() over(partition by shop order by ct) rk

from t1;t2

（3）取每个店铺排名前3的

select shop,user_id,ct

from t2

where rk<=3;

（4）最终SQL

select

shop,

user_id,

ct

from

(select

shop,

user_id,

ct,

rank() over(partition by shop order by ct) rk

from

(select

shop,

user_id,

count(*) ct

from visit

group by

shop,

user_id)t1

)t2

where rk<=3;