sql行列转换_数据分析笔试经典sql题解

前言：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