SQL金融行业项目：你每月消费多少？（累计额，链表，case when）

某理财银行有下面3个表。

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

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

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

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

1.计算2016年1-3月的消费总金额，生成如下格式的查询结果

2.提取2016年3月消费金额大于等于1288的客户名单，并给出这些客户信息

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

【解题步骤】

观察三张数据表看出交易表和客户表通过“客户号”关联，客户表和银行分行对应表通过“分行号”有关联。

1）交易表中的交易类型有两种（消费和转账），所以要对交易类型=消费 进行筛选。同时，要分析的是2016年的数据，所以也要对交易时间筛选。

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 bon 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 cwhere 累计消费金额>1288;

那么如何获得每个客户累计消费金额最先到达1288的记录呢？这时可以按照客户分组，对交易时间进行排序（窗口函数 row_number）

select * , row_number() over(partition by 交易客户 order by 交易时间) as 排序from cwhere 累计消费金额>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 eon a.客户号 = e.交易客户;

将前面的步骤一，步骤二整合在一起，得出客户名称，2016年3月总消费金额，2016年首次达到1288时间的三个字段

select a.客户名称, sum(b.交易金额) as 2016年3月总消费金额, e.交易时间 as 2016年3月首次达到1288的时间from 客户表 a inner join 交易表 bon 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 ainner join 客户表 as b on a.交易客户 = b.客户号inner join 银行分行对应表 as c on b.所属分行 = c.分行号;

筛选出2016年3月份的消费数据

select * from交易表 as ainner 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 ainner 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.通过观察数据表的关系，通过多表联结得出想要的字段

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。