sql面试题系列（三）

24-40, 涉及RFM分类，累计问题，异常值，多行合并concat，表的加法UNOIN，having 用法补充，lead窗口函数，sql执行顺序等

【补充】

HAVING 语法与 WHERE 语法类似，

HAVING 可以包含聚合函数
HAVING 子句可以引用选择列表中显示的任意项
HAVING 不可以使用聚合函数之后的别名（orderby可以）

24.RFM用户分类

是2020年1月份某电商平台的用户行为记录表（用户编号，商品编号，产品分类编号，用户行为类型，用户活跃日期，消费金额）。

RFM-最近一次消费时间间隔(R)、消费频率(F)、消费金额(M)。定义

R：最近一次购买的时间到现在（1.31日）间隔

F：一月购买次数

M：一月购买总金额

根据RFM得分制定打分规则，比如R很小, F和M很大认为是好的用户

if(分数>60, "及格", "不及格")

-- 计算RFM+打分
insert into t_RFM
select 用户编号,
	(case
   	when R>15 then 1
   	when R>5 and R<=15 then 2
   	when R<=5 then 3
   end) as R分,
........
.... -- FM同理
....
from 
(
  select 用户编号, 
    min(datediff("2020-01-31",用户活跃日期)) as R
    count(1) AS F,
    sum(购买金额) as M
  from 用户行为表
  where 用户行为类型 = "购买"
  and 用户活跃日期>="2020-01-01"
  group by 用户编号
);

-- 计算平均值，并比较用户相对于平均值的高低
select 用户id,(case
            when r_type = "高" and f_type = "高" and m_type = "高" then "重要价值用户"
            when r_type = "高" and f_type = "低" and m_type = "高" then "重要发展用户"
           	when r_type = "低" and f_type = "高" and m_type = "高" then "重要保持用户"
            when r_type = "低" and f_type = "低" and m_type = "高" then "重要挽留用户"
            ...
            ...
            end
            ) as rfm分类结果
from
(
  select 用户id, 
    if(R分 > r_mean, "高", "低") as r_type,
    if(f分 > f_mean, "高", "低") as f_type,
    if(m分 > m_mean, "高", "低") as m_type,
  from 
  t_RFM t1
  left join (
    select avg(R分) r_mean,avg(f分) f_mean ,avg(m分) m_mean
    from t_RFM
  ) t2 --可以没有on的筛选条件 
) tmp;

28.在职员工发放奖金

雇员奖金表是给员工发放奖金的记录：雇员编号，接收日期，奖金类型。其中奖金有3种类型：奖金类型的值是1，表示奖金金额为薪水salary的10%，奖金类型的值是2，表示奖金金额为薪水salary的20%，奖金类型的值是3，表示奖金金额为薪水salary的30%

雇员表：编号，出生日期，名，姓，性别，雇用日期

薪水表：编号，薪水，起始日期，结束日期（薪水变动的记录都在，最新的薪水结束日期为'9999-01-01'）

问题：查找雇员编号、名、姓、奖金类型、对应的当前薪水以及奖金金额。

select *, 
  (case 
    when c.奖金类型 = "1" then 0.1*当前薪水
    when c.奖金类型 = "2" then 0.3*当前薪水
    when c.奖金类型 = "3" then 0.3*当前薪水
  else null
  end) as 奖金金额
from
(
  select a.雇员编号,a.名,a.姓,b.薪水 as 当前薪水,c.奖金类型
  from 雇员表 a
  left join 薪水表 b
  on a.编号 = b.编号 and b.结束日期 = '9999-01-01'
  left join 奖金表 c
  on a.编号 = c.编号
) tmp

29. 贝壳找房—成交分析

“成交订单表”里记录了某房产平台（类似链家、贝壳等）每日房屋成交的明细。（贝壳面试题）字段“成交客源渠道”中的值是“客源角色人”、“业主线上委托”、“”表示线下渠道，其余的成交客源渠道是线上。字段“房屋用途”中的值有“住房”、”车位”、”车库”等。

1．当月截止昨天二手线上成交占比（含车位）>=50%的门店可获奖；

（线上成交占比=线上成交单量/总成交单量）

2．符合获奖条件的门店的第1单线上成交的，经纪人可获得800贝壳币（可以用于兑换奖金），第2单可获400贝壳币，第3单及以上可获200贝壳币，但车库不奖励（字段“房屋用途”中的值是”车位”、”车库”认为是车库）；

3.在一个连续的SQL中实现以上需求，不能拆分成多个SQL，必须输出奖励发放表格字段如下（可增加）；

（价格，经纪人，客源，时间，用途）

datediff(start, end) mysql
datediff(end, start) hive sql / sql server

-- 输出奖励发放表(输出表格中增加了3个字段，分别是：是否线上、经纪人所在门店的线上占比、该单应发的贝壳币)
select from t3.成交价........., t3.是否线上,t3.线上成交占比,
	(case 
   	when 是否获奖门店 = "获奖门店" and 线上房屋成交时间排序 = 1 then 800 --符合获奖条件的门店的第1单线上成交的，经纪人可获得800贝壳币
   	when 是否获奖门店 = "获奖门店" and 线上房屋成交时间排序 = 2 then 400
   	when 是否获奖门店 = "获奖门店" and 线上房屋成交时间排序 = 3 then 200
   else 0
   end 
   )  as 应发放贝壳币
from
(
  select 
    t1.*,t2.线上成交占比,
    (case when 成交客源渠道 in (“客源角色人”,“业主线上委托”,“”) then "非线上" else "线上" end) as 是否线上,
    row_number() over(partition by 签约经纪人门店 order by (case when (房屋用途 in ("车位","车库"))or(成交客源渠道 in (“客源角色人”,“业主线上委托”,“”)) then null else 签约时间) asc) as 线上房屋成交时间排序,
    (case when t2.线上成交占比<0.5 then "非获奖门店" else "获奖门店") as 是否获奖门店--二手线上成交占比（含车位）>=50%的门店
  from 成交订单表 t1
  left join
  (
      select 
        签约经纪人门店
        count(distinct (case when 成交客源渠道 in (“客源角色人”,“业主线上委托”,“”) then null else 协议id end)) / count(distinct 协议id) as 线上成交占比
    from 成交订单表
    where datediff(d, 签约时间, getdate())>0 and datediff(m,签约时间, getdate()) = 0-- 当月截止昨天
    group by 签约经纪人门店;
  ) t2
  on t1.签约经纪人门店 = t2.签约经纪人门店
  where datediff(d, 签约时间, getdate())>0 and datediff(m, 签约时间, getdate()) = 0
) t3;

30.累计求和问题

“薪水表”中记录了员工发放的薪水。包含雇员编号，薪水、起始日期、结束日期。其中，薪水是指该雇员在起始日期到结束日期这段时间内的薪水。当前员工是指结束日期 = '9999-01-01'的员工。

业务问题：按照雇员编号升序排列，查找薪水的累计和（累计薪水）。其中累计薪水是前N个当前员工( 结束日期 = '9999-01-01')的薪水的累计和，其他以此类推。

select 雇员编号, 薪水
	sum() over(order by 雇员编号 asc) as 累计薪水
from 薪水表
where 结束日期 = "9999-01-01";

31.复购用户

"课程订单表”里记录了某在线教育App的用户购买课程的信息：用户id, 购买时间, 课程类型, 消费金额。

请使用sql将购买记录表中的信息，提取为复购分析表的格式，并用一条sql语句写出。

日期	当日首次购买用户数	当月复购用户数	次月复购用户数	第三月.....

复购用户：如果一个用户的首次购买日期是a，且该用户在a之后的第n月内，也有购买行为，这个用户被算做第n月复购用户。

datediff(m, 购买时间, 复购时间) = n：第n月复购用户

select 
 a.日期, 
 count(distinct (case when b.购买时间 is null then a.用户id else null end)) 当日首次购买用户数,
 count(distinct (case when datediff(m, b.购买时间, a.购买时间)=0 then a.用户id else null end)) as 当月复购用户数,-- b.购买时间 就是复购时间
 count(distinct (case when datediff(m, b.购买时间, a.购买时间)=1 then a.用户id else null end)) as 次月复购用户数,
 count(distinct (case when datediff(m, b.购买时间, a.购买时间)=2 then a.用户id else null end)) as 第三月复购用户数,
 .......
from 订单表 a
left join b
on a.用户id = b.用户id
and a.购买时间<b.购买时间
group by a.日期;

32. 金融行业实战

下表是某金融App的原始数据表：用户id,投资金额，年龄，性别，省份，城市等等

为方便后期分析，我们对数据进行分类。数据通常分为3类：用户数据、行为数据、产品数据。

用户数据：年龄、性别、手机省份、手机城市、注册时间、用户注册终端、用户注册渠道、会员级别等
最近登录数据：用户id，省份，城市，终端，ip，设备，时间
活跃数据：用户id+是否...7天内...类的问题
行为数据：用户id，投资金额，托管，开通日期，首次充值时间，首投时间，等等

问题：

1.理解数据

如果将该数据建立一个数据库，有哪些表，各表之间的关系是什么？

2.数据整理

假设该Excel数据是一个数据表（Datatable），请用SQL写出：

（1）最近一次登录，各城市各有多少用户在表中？

（2）“用户ID”之间的可能关系？（比如家庭成员，情侣等）【解法】ip地址相同但是uid不同，连接用户表看看差异

（3）请找出数据表中的异常值，并以此阐述你如何控制数据质量并以本数据为例设计数据质量报表。

【回答】

1.首先分析业务流程：访问->注册->浏览商品->充值投资托管，日常登录-浏览商品

各个表按照用户id链接，用户数据-行为数据-登录数据-活跃数据

2.定义变量&赋值（mysql中不用先声明）

-- 除了set select 也可以：
select @变量名:= 字段名 from 表名 where 过滤语句;
select @num:=value;

-- (1)
select 省份,城市,count(用户id)
from 最近登录表
group by 省份,城市
-- (2)家庭成员，情侣，特点：ip地址一致id不同，
select *
from 用户表
where 用户ID in (
  select distinct 用户id
  from 最近登录表
  group by ip地址
  having count(distinct 用户id)>1
);
-- (3)异常值
select 
	用户id
from 用户表
left join
(
  select 
    mean(年龄)+ 3*stdev(年龄) as 异常值2,
    mean(年龄)- 3*stdev(年龄) as 异常值1
  from 用户表
) tmp
where 年龄<异常值1 or 年龄>异常值2;

33.交易记录分析

某商场为了分析用户购买渠道。表1是用户交易记录表，记录了用户id、交易日期、交易类型和交易金额。表2是用户类型表，记录了用户支付类型（微信、支付宝、信用卡等），分别有type1、type2。

要求：

1.请在 type1的用户类型中，找出总交易金额最大的用户。

2.筛选每个用户的第2笔交易记录。

3.如下表：如何实现表3的数据格式？【group_concat】

用户id	交易日期	交易类型
A1	2017-06-01,2018-01-01...	抽奖,游戏,.....

-- （1）总交易金额最大的用户
select t1.用户id, 总交易金额 
from 用户类型表 t1
left join 
(
  select 用户id, sum(交易金额) as 总交易金额
  from 用户交易记录表
  group by 用户id
) t2
on t1.用户id = t2.用户id
and t1.用户类型 = "type1"
order by 总交易金额
limit ,1;  -- 返回第一行记录
-- (2)每个用户的第2笔交易记录
select *
from(
  select *,row_number()over(partition by 用户id order by 交易日期 asc) as rk
	from 用户交易记录表
) tmp
where rk =2;
--（3）实现表3的数据格式
select 用户id,
  group_concat(日期) as 交易日期,
  group_concat(交易类型) as 交易类型
from 用户交易记录表
group by 用户id;

34.月消费分析

交易表记录了每天交易的客户交易时间、客户号、消费类型和消费金额。其中，交易类型有两种值：消费和转账。

客户表记录了客户信息，包括客户号，客户名称和客户所属的银行分行号

分行表记录每个分行的信息，包括分行号、分行名称及对应上级分行。

【问题】该理财银行要求对客户及销售额分析报告，要求如下：

1.计算2016年1-3月的消费总金额

2.2016年3月消费金额>=1288的客户信息，及其3月内首次达到1288的时间

3.汇总各省分行（省分行下属支行也需要汇总至省分行）的2016年3月的总消费金额

总行对应分行号=1，省分行=上级分行为1的分行

--(1)
select 
	sum(case when 交易时间 between "2016-01-01" and "2016-01-31" then 消费金额 else 0) as 2016年1月,....
from 交易表

--(2)
select t1.客户号, t2.客户名称, 
	min(交易时间) as 首次达到1288的时间, 
	max(累计消费金额) as 总消费金额
from
(
  select 客户号, 交易时间,
    sum(消费金额) over (partition by 客户号 order by 交易时间 asc) as 累计消费金额
  from 交易表
  where 交易时间 between "2016-03-01" and "2016-03-31"
  order by 客户号, 累计消费金额 asc
) t1
left join 客户表 t2 on t1.客户号 = t2.客户号
where t1.累计消费金额>1288
group by t1.客户号；

-- （3）交易表-客户号-分行号-分行表
-- 傻逼题目
select 分行号, sum(消费金额) as 客户总消费
from 客户表 t1
left join 交易表 t2
on t1.客户号 = t2.客户号
where t2.交易时间 between "2016-03-01" and "2016-03-31"
group by 分行号;

36.中位数

成绩表：用户编号，岗位，成绩

问题：写一个sql语句查询每个岗位的成绩中位数位置的范围

C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2。Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]。

求余数： % 或者 mod

select 岗位
    (case when 人数%2=0 then 人数/2 else (人数-1)/2)as 起始位置,
    (case when 人数%2=0 then 人数/2+1 else (人数+1)/2)as 终止位置
from
(
 select 岗位,
   count(用户编号) as 人数,
 from 成绩表
 group by 岗位
) tmp;

37.平均薪水

薪水表中记录了员工的编号，所在部门编号，和薪水。查询出每个部门除去最高、最低薪水后的平均薪水，并保留整数。（字节跳动面试题）

保留整数：format(数字, 小数位数)

select 部门编号, format(avg(薪水),0) as 平均薪水
from
(
select *,
	dense_rank() over(partition by 部门编号 order by 薪水 desc) as rk1
	dense_rank() over(partition by 部门编号 order by 薪水 asc) as rk2
from 薪水表
) tmp
where rk1>1 and rk2>2
group by 部门编号;

38.课程续费分析

新学员购买50节课为一个学习阶段，学习完想要进入下个阶段必须再次购买，即续费（假设所有学员只能续费一次）并且每个学员可选择不同老师进行学习。表一：学员上课表，表二：续费表

1.现求出续费学员在续费前3个月内的总课量，3个月给学员上课老师数量

2.现求出每个续费学员在续费前的最后一节课的时间，以及对应的上课老师。

--(1)
select 学员id , count(上课时间) as 总课量, count distinct(老师id) as 老师数
from 学院上课表 t1 
left join 购买表 t2
on t1.学生id = t2.学生id
and t2.订单类型 = 2
and datediff(m, t2.续费时间, t1.上课时间)<3 --续费前3个月内
group by 学员id;
--（2）
select 学员id ,上课时间, 老师id
from
(
select 学员id ,上课时间, 老师id, row_number()over(partition by 学员id order by 上课时间) as rk
from 学院上课表 t1 
left join 购买表 t2
on t1.学生id = t2.学生id
and t2.订单类型 = 2
and datediff(d, t2.续费时间, t1.上课时间)>0 --在续费之前
group by 学员id
) tmp
where rk = 1

39.小红书用户行为

用户订单表：记录用户id、购买的商品id、用户下单的时间及商品的种类。

用户收藏商品表：记录用户id、用户收藏的商品id及收藏时间。

问题：请用一句sql语句得出以下查询结果，得到所有用户的商品行为特征，其中用户行为分类为4种：已购买、购买未收藏、收藏未购买、收藏且购买。

(
select t1.用户id, t1.商品id
	1 as "已购买"
	(case when t2.商品id is null then 1 else 0 end) as "购买未收藏"
	0 as "收藏未购买"
	(case when t2.商品id is not null then 1 else 0 end) as "收藏且购买"
from 
(
  select distinct 用户id, 商品id
  from 用户订单表
  group by 用户id, 商品id
) t1
left join 
(
  select distinct 用户id, 商品id
  from 用户收藏表
  group by 用户id, 商品id
) t2
on t1.用户id = t2.用户id and t1.商品id = t2.商品id
order by t1.用户id
)
-- 上面是所有购买的，考虑是否有过收藏
union 
-- 下面补充没购买的，但是有收藏的
(
select  t2.用户id, t2.商品id
	0  as "已购买"
	0 as "购买未收藏"
	1  as "收藏未购买"
	0 "收藏且购买"
from 
(
  select distinct 用户id, 商品id
  from 用户订单表
  group by 用户id, 商品id
) t1
right join 
(
  select distinct 用户id, 商品id
  from 用户收藏表
  group by 用户id, 商品id
) t2
on t1.用户id = t2.用户id and t1.商品id = t2.商品id
order by t1.用户id
where t1.用户id is null -- 未购买
);

40.连续得分的球员

两只篮球队进行了激烈的比赛，比分交替上升。比赛结束后，你有一张两队分数的明细表：球队、球员号码、球员姓名、得分分数以及得分时间。

问题：每个球队里为该球队连续三次（及以上）得分的球员

窗口函数 **lead(想查询的列, 向下n行)**over (....)，一般用于计算差值，例如：

1）计算花费时间。例如：某数据是每个用户浏览网页的时间记录，将记录的时间错位之后，进行两列相减就可以得到每个用户浏览每个网页实际花费的时间。

2）计算与上次相比薪水涨幅。

-- 首先按照球队和得分时间排序，再找到连续出现三次的球员姓名
-- 用一个lead作窗口函数就可以解决
select distinct 球员姓名
from
(
  select 球员姓名,
       lead(球员姓名,1) over(partition by 球队 order by 得分时间) as 姓名1,
       lead(球员姓名,2) over(partition by 球队 order by 得分时间) as 姓名2
	from 分数表
) tmp
where 球员姓名 = 姓名1 and 姓名1 = 姓名2;

【补充】SQL运行顺序:

from join on where group by avg/sum having select distinct order by