欢迎关注微信公众号：开心数据
前言：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 sales
union all
select year,case when m2 then 2 else null end month,m2 amount from sales
union all
select year,case when m3 then 3 else null end month,m3 amount from sales
union all
select year,case when m4 then 4 else null end month,m4 amount from sales)t
order by year,month
2、某外卖平台交易表user_goods_table有如下字段
- user_name
- goods_kind 外卖种类
求每个用户购买外卖品类的偏好分布，并取出每个用户购买最多的外卖品类
这就需要用到窗口函数，每一行返回一个聚合值
参考题解
select a.user_name,a.goods_kind from (
select user_name,goods_kind,row_number() over(partition by user_name order by count(goods_kind) desc) r
from user_goods_table)a
where a.r=1
3、连续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)t1
group by t1.uid ,flag_date having cnt>=7
4、某顶尖支付平台交易表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 level
from sales )b
where b.level=1
5、以下两张表
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 a
left join mall_cate_1d b
on a.date = b.date and a.mall_id = b.mall_idwhere gmv > 0group 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) r
from mall_gmv_1d a
left join mall_cate_1d b
on a. mall_id=b.mall_id
where a.date='2019-06-25'
group by a.mall,b.cate)t
where t.r=1
