某理财银行有下面3个表。
交易表记录了每天交易的客户交易时间、客户号、消费类型和消费金额。其中,交易类型有两种值:消费和转账。
编辑
客户表记录了客户信息,包括客户号,客户名称和客户所属的银行分行号
编辑
分行号记录每个分行的信息,包括分行号、分行名称及对应上级分行。
编辑
该理财银行要求对客户及销售额分析报告,要求如下:
1.计算2016年1-3月的消费总金额,生成如下格式的查询结果
编辑
2.提取2016年3月消费金额大于等于1288的客户名单,并给出这些客户信息
编辑
3.汇总各省分行(省分行下属支行也需要汇总至省分行)的2016年3月的总消费金额
【解题步骤】
观察三张数据表看出交易表和客户表通过“客户号”关联,客户表和银行分行对应表通过“分行号”有关联。
编辑
编辑
select *
from 交易表
where 交易类型 = "消费" and year(交易时间) = 2016;
2)对交易时间按格式分类(year(),month()),用case when 语句对交易时间进行判断,符合2016年1-3月的记录并显示金额,不符合显示空值。
select
(case when year(交易时间) = 2016 and month(交易时间) = 1
then 交易金额 else null end) as 2016年1月,
(case when year(交易时间) = 2016 and month(交易时间) = 2
then 交易金额 else null end) as 2016年2月,
(case when year(交易时间) = 2016 and month(交易时间) = 3
then 交易金额 else null end) as 2016年3月
from 交易表;
编辑
3)符合记录的交易金额求和,得出1-3月份总的消费额
编辑
select
sum(case when year(交易时间) = 2016 and month(交易时间) = 1
then 交易金额 else null end) as 2016年1月,
sum(case when year(交易时间) = 2016 and month(交易时间) = 2
then 交易金额 else null end) as 2016年2月,
sum(case when year(交易时间) = 2016 and month(交易时间) = 3
then 交易金额 else null end) as 2016年3月
from 交易表
where 交易类型 = "消费" and year(交易时间) = 2016;
编辑
2.提取2016年3月消费金额大于等于1288的客户名单,并显示这些列客户名称,2016年3月总消费金额,2016年3月首次达到1288的时间
编辑
1)2016年3月总消费金额
"客户名称“在客户表,交易金额在交易表中,涉及到2个表,所以要用到多表联结。
编辑
select a.客户名称,b.*
from 客户表 as a
inner join 交易表 as b
on a.客户号 = b.交易客户;
2016年3月总消费金额:先找出2016年3月份的消费数据 ,然后分析每个客户的总消费金额。
筛选交易时间为2016年3月的记录(between * and *),同时交易类似是消费。
select a.客户名称,b.*
from 客户表 as a
inner join 交易表 as b
on a.客户号 = b.交易客户
where b.交易时间 between "2016-03-01" and "2016-03-31"
and b.交易类型 = "消费";
每个客户的交易金额,涉及到“每个”就要想到《猴子 从零学会SQL》里讲过的“分组汇总”来解决。
按交易客户分组(group by),汇总交易金额(求和sum)。
select a.客户名称,
sum(b.交易金额) as 2016年3月总消费金额
from 客户表 as a
inner join 交易表 as b
on a.客户号 = b.交易客户
where b.交易时间 between "2016-03-01" and "2016-03-31"
and b.交易类型 = "消费"
group by a.客户名称;
编辑
2)2016年3月首次达到1288的时间
通过交易表对每个客户的交易金额逐条累计求和,找出最接近交易金额大于或者等于1288的记录。例如下图是交易客户1的累加:
编辑
第1行的累计消费金额为第一条消费金额12.5
第2行的累计薪水为雇员第一条消费金额+第二条消费金额12.5+200之和
依次类推...
如何计算出每行的累计薪水?
累计求和问题要想到《猴子 从零学会SQL》里讲过的窗口函数,语法如下。
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
用聚合函数作为窗口函数,有累计的功能。因为本题是累计“求和”,所以用聚合函数sum。
select 交易客户,交易时间,
sum(交易金额) over (partition by 交易客户
order by 交易时间) as "累计消费金额"
from 交易表
where 交易时间 between "2016-03-01" and "2016-03-31"
and 交易类型 = "消费";
编辑
select *
from c
where 累计消费金额>1288;
编辑
那么如何获得每个客户累计消费金额最先到达1288的记录呢?这时可以按照客户分组,对交易时间进行排序(窗口函数 row_number)
select * ,
row_number() over(partition by 交易客户
order by 交易时间) as 排序
from c
where 累计消费金额>1288;
编辑
观察得到,每个客户的第一条记录,即累计金额最接近1288的记录和交易时间。
将上诉查询结果记为表d,筛选第一条记录就是。
select *
from d
where 排序=1;
编辑
3)将上诉查询结果记为表e(每个客户首次累计消费达到1288),通过“交易客户”和客户表联结,就可以得到分析要求的客户名称、客户2016年3月首次达到1288的时间
select a.客户名称,e.交易时间 as 2016年3月首次达到1288的时间
from 客户表 a inner join e
on a.客户号 = e.交易客户;
将前面的步骤一,步骤二整合在一起,得出客户名称,2016年3月总消费金额,2016年首次达到1288时间的三个字段
select a.客户名称,
sum(b.交易金额) as 2016年3月总消费金额,
e.交易时间 as 2016年3月首次达到1288的时间
from 客户表 a
inner join 交易表 b
on a.客户号 = b.交易客户
inner join
(
select *
from
(
select * ,
row_number()over partition by 交易客户
order by 交易时间) as 排序
from
(
select 交易客户,交易时间,
sum(交易金额) over (partition by 交易客户
order by 交易时间) as "累计消费金额"
from 交易表
where 交易时间 between "2016-03-01" and "2016-03-31"
and 交易类型 = "消费"
) as c where 累计消费金额>1288
) as d where 排序=1
) as e
on a.客户号 = e.交易客户
where b.交易时间
between "2016-03-01" and "2016-03-31"
and b.交易类型="消费"
group by a.客户名称;
编辑
3.汇总各省分行(省分行下属支行也需要汇总至省分行)的2016年3月的总消费金额
①得出每个分行2016年3月的消费总金额:通过客户表找到每个客户的所属分行,汇总每个分行的消费金额;
②得出每个省行的消费总金额:再通过银行分行对应表找到每个分行对应的上级分行,得出对应省行的总消费金额
编辑
1)得出每个分行2016年3月的消费总金额
因为涉及到3张表的字段,需要3张表联结
select *
from交易表 as a
inner join 客户表 as b
on a.交易客户 = b.客户号
inner join 银行分行对应表 as c
on b.所属分行 = c.分行号;
筛选出2016年3月份的消费数据
select *
from交易表 as a
inner join 客户表 as b
on a.交易客户 = b.客户号
inner join 银行分行对应表 as c
on b.所属分行 = c.分行号
where a.交易时间 between "2016-03-01" and "2016-03-31"
and a.交易类型 = "消费";
每个分行(分组group by)总交易金额(汇总求和sum)和分行对应的上级分行
select b.所属分行,
sum(a.交易金额) as 消费总金额,
c.上级分行
from交易表 as a
inner join 客户表 as b
on a.交易客户 = b.客户号
inner join 银行分行对应表 as c
on b.所属分行 = c.分行号
where a.交易时间 between "2016-03-01" and "2016-03-31"
and a.交易类型 = "消费"
group by b.所属分行;
编辑
2)得出每个省行的消费总金额
将上面查询结果记为表d,通过观察可知
分行号为"4","2","3"属于江苏省分行
分行号"5","6","7"属于广东省分行
利用case when 对分行号做判断,符合条件的消费金额相加,得出各省分行的总消费金额。
select
sum(case when 上级分行 in ("4","2","3")
then 消费总金额 else null end )as "江苏省分行",
sum(case when 上级分行 in ("5","6","7")
then 消费总金额 else null end ) as "广东省分行"
from d;
将上面的步骤1和步骤2和在一起就得到了最终的sql。
编辑
select
sum(case when 所属分行 in ("4","2","3")
then 消费总金额 else null end )as "江苏省分行",
sum(case when 所属分行 in ("5","6","7")
then 消费总金额 else null end ) as "广东省分行"
from
(select b.所属分行,
sum(a.交易金额) as 消费总金额,
c.上级分行
from 交易表 a
inner join 客户表 b on a.交易客户 = b.客户号
inner join 银行分行对应表 c on b.所属分行 = c.分行号
where
a.交易时间 between "2016-03-01" and "2016-03-31"
and a.交易类型 = "消费"
group by b.所属分行) as d;
编辑
【本题考点】
本面试题通过与业务需求结合,考核SQL的综合能力,实际业务场景比较强,通过观察表之间的关系拆分问题写出业务需求。
1.如何将复杂的业务问题,使用多维度拆解分析方法去解决
2.遇到多条件判断的问题,要想到用case语句来实现
3.窗口函数的应用场景,①遇到排名问题,要想到使用窗口函数来实现。②对于“累计”问题,要想到用聚合函数作为窗口函数。比如累计求和,用sum。
4.通过观察数据表的关系,通过多表联结得出想要的字段