金融行业项目你每月消费多少

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

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

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

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

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

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

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

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

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

1.计算年1-3月的消费总金额，并以月份作为列名的格式显示

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

select*from交易表where交易类型="消费"andyear(交易时间)=;

2）对交易时间按格式分类（year(),month()），用casewhen语句对交易时间进行判断，符合年1-3月的记录并显示金额，不符合显示空值。

select(casewhenyear(交易时间)=andmonth(交易时间)=1then交易金额elsenullend)as年1月,(casewhenyear(交易时间)=andmonth(交易时间)=2then交易金额elsenullend)as年2月,(casewhenyear(交易时间)=andmonth(交易时间)=3then交易金额elsenullend)as年3月from交易表;

查询结果如下

3）符合记录的交易金额求和，得出1-3月份总的消费额

selectsum(casewhenyear(交易时间)=andmonth(交易时间)=1then交易金额elsenullend)as年1月,sum(casewhenyear(交易时间)=andmonth(交易时间)=2then交易金额elsenullend)as年2月,sum(casewhenyear(交易时间)=andmonth(交易时间)=3then交易金额elsenullend)as年3月from交易表where交易类型="消费"andyear(交易时间)=;

查询结果如下

2.提取年3月消费金额大于等于的客户名单，并显示这些列客户名称，年3月总消费金额，年3月首次达到的时间

该如何得出这两列呢？

1）年3月总消费金额

"客户名称“在客户表，交易金额在交易表中，涉及到2个表，所以要用到多表联结。

selecta.客户名称,b.*from客户表asainnerjoin交易表asbona.客户号=b.交易客户;

年3月总消费金额：先找出年3月份的消费数据，然后分析每个客户的总消费金额。

筛选交易时间为年3月的记录（between*and*)，同时交易类似是消费。

selecta.客户名称,b.*from客户表asainnerjoin交易表asbona.客户号=b.交易客户whereb.交易时间between"-03-01"and"-03-31"andb.交易类型="消费";

每个客户的交易金额，涉及到“每个”就要想到《猴子从零学会SQL》里讲过的“分组汇总”来解决。

按交易客户分组(groupby)，汇总交易金额（求和sum）。

selecta.客户名称,sum(b.交易金额)as年3月总消费金额from客户表asainnerjoin交易表asbona.客户号=b.交易客户whereb.交易时间between"-03-01"and"-03-31"andb.交易类型="消费"groupbya.客户名称;

查询结果如下

2）年3月首次达到的时间

通过交易表对每个客户的交易金额逐条累计求和，找出最接近交易金额大于或者等于的记录。例如下图是交易客户1的累加：

第1行的累计消费金额为第一条消费金额12.5

第2行的累计薪水为雇员第一条消费金额+第二条消费金额12.5+之和

依次类推...

如何计算出每行的累计薪水？

累计求和问题要想到《猴子从零学会SQL》里讲过的窗口函数，语法如下。

窗口函数over(partitionby用于分组的列名orderby用于排序的列名)

用聚合函数作为窗口函数，有累计的功能。因为本题是累计“求和”，所以用聚合函数sum。

select交易客户,交易时间,sum(交易金额)over(partitionby交易客户orderby交易时间)as"累计消费金额"from交易表where交易时间between"-03-01"and"-03-31"and交易类型="消费";

查询结果如下

将上诉查询结果记录为表c，筛选累计消费金额大于的记录

select*fromcwhere累计消费金额;

查询结果如下：

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

select*,row_number()over(partitionby交易客户orderby交易时间)as排序fromcwhere累计消费金额；

查询结果如下：

观察得到，每个客户的第一条记录，即累计金额最接近的记录和交易时间。

将上诉查询结果记为表d，筛选第一条记录就是。

select*fromdwhere排序=1;

查询结果如下

3）将上诉查询结果记为表e（每个客户首次累计消费达到），通过“交易客户”和客户表联结，就可以得到分析要求的客户名称、客户年3月首次达到的时间

selecta.客户名称,e.交易时间as年3月首次达到的时间from客户表ainnerjoineona.客户号=e.交易客户;

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

selecta.客户名称,sum(b.交易金额)as年3月总消费金额,e.交易时间as年3月首次达到的时间from客户表ainnerjoin交易表bona.客户号=b.交易客户innerjoin(select*from(select*,row_number()overpartitionby交易客户orderby交易时间)as排序from(select交易客户,交易时间,sum(交易金额)over(partitionby交易客户orderby交易时间)as"累计消费金额"from交易表where交易时间between"-03-01"and"-03-31"and交易类型="消费")ascwhere累计消费金额)asdwhere排序=1)aseona.客户号=e.交易客户whereb.交易时间between"-03-01"and"-03-31"andb.交易类型="消费"groupbya.客户名称;

查询结果如下

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

①得出每个分行年3月的消费总金额：通过客户表找到每个客户的所属分行，汇总每个分行的消费金额；

②得出每个省行的消费总金额：再通过银行分行对应表找到每个分行对应的上级分行，得出对应省行的总消费金额

1）得出每个分行年3月的消费总金额

因为涉及到3张表的字段，需要3张表联结

select*from交易表asainnerjoin客户表asbona.交易客户=b.客户号innerjoin银行分行对应表asconb.所属分行=c.分行号;

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

select*from交易表asainnerjoin客户表asbona.交易客户=b.客户号innerjoin银行分行对应表asconb.所属分行=c.分行号wherea.交易时间between"-03-01"and"-03-31"anda.交易类型="消费";

每个分行（分组groupby）总交易金额（汇总求和sum）和分行对应的上级分行

selectb.所属分行,sum(a.交易金额)as消费总金额,c.上级分行from交易表asainnerjoin客户表asbona.交易客户=b.客户号innerjoin银行分行对应表asconb.所属分行=c.分行号wherea.交易时间between"-03-01"and"-03-31"anda.交易类型="消费"groupbyb.所属分行;

查询结果如下

2）得出每个省行的消费总金额

将上面查询结果记为表d，通过观察可知

分行号为"4","2","3"属于江苏省分行

分行号"5","6","7"属于广东省分行

利用casewhen对分行号做判断，符合条件的消费金额相加，得出各省分行的总消费金额。

selectsum(casewhen上级分行in("4","2","3")then消费总金额elsenullend)as"江苏省分行",sum(casewhen上级分行in("5","6","7")then消费总金额elsenullend)as"广东省分行"fromd;

将上面的步骤1和步骤2和在一起就得到了最终的sql。

selectsum(casewhen所属分行in("4","2","3")then消费总金额elsenullend)as"江苏省分行",sum(casewhen所属分行in("5","6","7")then消费总金额elsenullend)as"广东省分行"from(selectb.所属分行,sum(a.交易金额)as消费总金额,c.上级分行from交易表ainnerjoin客户表bona.交易客户=b.客户号innerjoin银行分行对应表conb.所属分行=c.分行号wherea.交易时间between"-03-01"and"-03-31"anda.交易类型="消费"groupbyb.所属分行)asd;

查询结果如下

本面试题通过与业务需求结合，考核SQL的综合能力，实际业务场景比较强，通过观察表之间的关系拆分问题写出业务需求。

1.如何将复杂的业务问题，使用多维度拆解分析方法去解决

2.遇到多条件判断的问题，要想到用case语句来实现

3.窗口函数的应用场景，①遇到排名问题，要想到使用窗口函数来实现。②对于“累计”问题，要想到用聚合函数作为窗口函数。比如累计求和，用sum。

4.通过观察数据表的关系，通过多表联结得出想要的字段

推荐：如何从零学会sql？

转载请注明：http://www.iockb.com/wadwh/16096.html