前言:sql是数据分析师笔试必考的考点之一,常考的题型有行列转换、联表查询,这些都比较简单,一般考的最难的就是hivesql窗口函数联表查询,普通的聚合函数每组(Group by)只返回一个值,而窗口函数则可为窗口中的每行都返回一个值。常考的窗口函数也就那几个,也是数据分析工作中经常用到的,row_number、rank、dense_rank(要搞清楚他们的区别),以及ntile,lead等等。下面分享几道数据分析笔试中的经典sql题。
1、行列转换(某东数据分析笔试题)
表sales
year | m1 | m2 | m3 | m4 |
1991 | 1.1 | 1.2 | 1.1 | 1.3 |
1992 | 1.2 | 1.3 | 1.3 | 1.4 |
请转换成这个样子
year | month | amount |
1991 | 1 | 1.1 |
1991 | 2 | 1.2 |
1991 | 3 | 1.1 |
1991 | 4 | 1.3 |
1992 | 1 | 1.2 |
1992 | 2 | 1.3 |
1992 | 3 | 1.3 |
1992 | 4 | 1.4 |
参考题解:
select * from(select year,case when m1 then 1 else null end month,m1 amount from salesunion all select year,case when m2 then 2 else null end month,m2 amount from salesunion allselect year,case when m3 then 3 else null end month,m3 amount from salesunion all select year,case when m4 then 4 else null end month,m4 amount from sales)torder by year,month
2、连续7天登陆的客户
表login_users 有如下字段
uid
logdate
关键在于如何判断连续,利用窗口函数row_number,对uid分组排序后,用登陆日期减去排序的序号,如果是连续的话,那得到的日期flag_date就会相同,再利用uid和falg_date分组并求和,再筛选出大于7的就行
参考题解:
select t1.uid,count(1) as cnt from (select uid,logdate,rank,date_sub(t.logdate,t.rank) as flag_date from(select uid,logdate, row_number() over(partition by uid order by logdate ) as rank from login_users)t)t1group by t1.uid ,flag_date having cnt>=7
3、某顶尖支付平台交易表sales字段user_name,amount,求支付金额在前20%的用户
参考题解:
select b.user_name from(select user_name, ntile(5) over(partition by user_name order by sum(amount) desc) as levelfrom sales )bwhere b.level=1
4、以下两张表
1、统计每个类目每天的成交店铺数、成交额和用户均成交额
2、统计2019-06-25当天每个类目成交额前10%的店铺清单(类目/店铺ID/成交额)
参考题解1:关联的时候注意关联条件有两个,date和mall_id
select a.date,b.cate,count(1),sum(gmv),avg(gmv)from mall_gmv_1d aleft join mall_cate_1d bon a.date = b.date and a.mall_id = b.mall_id where gmv > 0 group by a.date,b.cate
参考题解2:
select t.date,t.mall_id,t.total from(select a.mall_id,b.cate,sum(a.gmv) total, ntile(10) over(partition b.cate order by sum(gmv) desc) rfrom mall_gmv_1d aleft join mall_cate_1d bon a. mall_id=b.mall_idwhere a.date='2019-06-25'group by a.mall,b.cate)twhere t.r=1
5、某著名游戏公司笔试题
A表为登录表:dt(登录时间),userid,登录时长,zhandoulifanwei(int)B表为宝石级别表:dt,userid,baoshileixing,baoshijibie(int)求2019年7月1日到2019年7月7日之间,各战斗力范围内进行过宝石升级的人数,范围如下:0-10000
10000-20000
20000-30000
30000以上
参考题解:
Select AA.zhandouli, count(CC.userid) from (select userid, (case zhandoulifanwei when between 0 and 10000 then 'fanwei1' when between 10000 and 20000 then 'fanwei2' when between 20000 and 30000 then 'fanwei3' when >30000 then 'fanwei4' else null end) as zhadouli from A where dt between '2019-07-01' and '2019-07-07')AAright join (Select userid, min(baoshijibie) mi, max(baoshijibie) ma from B Where dt between '2019-07-01' and '2019-07-07' Group by userid having mi!=ma)CCOn AA.userid=CC.useridGroup by AA.zhandouli