华体会体育丨华体会app 在线留言 联系我们
全国服务热线:

11645358933

您的位置:主页 > 新闻资讯 > 热门新闻 >

热门新闻

华体会体育丨华体会app—金融行业项目:你每月消费几多?

来源:华体会体育点击: 发布时间:2022-02-13 08:18
本文摘要:某理财银行有下面3个表。生意业务表记载了天天生意业务的客户生意业务时间、客户号、消费类型和消费金额。 其中,生意业务类型有两种值:消费和转账。客户表记载了客户信息,包罗客户号,客户名称和客户所属的银行分行号。 分行号记载每个分行的信息,包罗分行号、分行名称及对应上级分行。

华体会体育

某理财银行有下面3个表。生意业务表记载了天天生意业务的客户生意业务时间、客户号、消费类型和消费金额。

其中,生意业务类型有两种值:消费和转账。客户表记载了客户信息,包罗客户号,客户名称和客户所属的银行分行号。

分行号记载每个分行的信息,包罗分行号、分行名称及对应上级分行。该理财银行要求对客户及销售额分析陈诉,要求如下:1.盘算2016年1-3月的消费总金额,生成如下花样的查询效果2.提取2016年3月消费金额大于即是1288的客户名单,并给出这些客户信息3.汇总各省分行(省分行下属支行也需要汇总至省分行)的2016年3月的总消费金额【解题步骤】视察三张数据表看出生意业务表和客户表通过“客户号”关联,客户表和银行分行对应表通过“分行号”有关联。1.盘算2016年1-3月的消费总金额,并以月份作为列名的花样显示1)生意业务表中的生意业务类型有两种(消费和转账),所以要对生意业务类型=消费 举行筛选。

同时,要分析的是2016年的数据,所以也要对生意业务时间筛选。1 select *2 from 生意业务表3 where生意业务类型="消费" and year(生意业务时间) = 2016;2)对生意业务时间按花样分类(year(),month()),用case when 语句对生意业务时间举行判断,切合2016年1-3月的记载并显示金额,不切合显示空值。

1 select 2 (casewhenyear(生意业务时间)=2016andmonth(生意业务时间)=13 then生意业务金额elsenullend)as2016年1月,4 (casewhenyear(生意业务时间)=2016andmonth(生意业务时间)=25 then生意业务金额elsenullend)as2016年2月, 6 (casewhenyear(生意业务时间)=2016andmonth(生意业务时间)=37 then生意业务金额elsenullend)as2016年3月8 from 生意业务表;查询效果如下3)切合记载的生意业务金额求和,得出1-3月份总的消费额1 select 2 sum(case when year(生意业务时间) = 2016 and month(生意业务时间) = 13 then 生意业务金额 else null end) as 2016年1月,4 sum(case when year(生意业务时间) = 2016 and month(生意业务时间) = 2 5 then 生意业务金额 else null end) as 2016年2月, 6 sum(case when year(生意业务时间) = 2016 and month(生意业务时间) = 3 7 then 生意业务金额 else null end) as 2016年3月8 from 生意业务表9 where 生意业务类型 = "消费" and year(生意业务时间) = 2016;查询效果如下2.提取2016年3月消费金额大于即是1288的客户名单,并显示这些列客户名称,2016年3月总消费金额,2016年3月首次到达1288的时间该如何得出这两列呢?1)2016年3月总消费金额"客户名称“在客户表,生意业务金额在生意业务表中,涉及到2个表,所以要用到多表联络。1 selecta.客户名称,b.*2 from 客户表 as a 3 inner join 生意业务表 as b4 on a.客户号 = b.生意业务客户;2016年3月总消费金额:先找出2016年3月份的消费数据 ,然后分析每个客户的总消费金额。筛选生意业务时间为2016年3月的记载(between * and *),同时生意业务类似是消费。

1 select a.客户名称, 2 sum(b.生意业务金额) as 2016年3月总消费金额3 from客户表asa4 innerjoin生意业务表asb5 ona.客户号=b.生意业务客户6 whereb.生意业务时间between"2016-03-01"and"2016-03-31" 7 and b.生意业务类型 = "消费"8 groupbya.客户名称;每个客户的生意业务金额,涉及到“每个”就要想到《猴子 从零学会SQL》里讲过的“分组汇总”来解决。按生意业务客户分组(group by),汇总生意业务金额(求和sum)。1 select a.客户名称, 2 sum(b.生意业务金额) as 2016年3月总消费金额3 from客户表asa4 innerjoin生意业务表asb5 ona.客户号=b.生意业务客户6 whereb.生意业务时间between"2016-03-01"and"2016-03-31" 7 and b.生意业务类型 = "消费"8 groupbya.客户名称;查询效果如下2)2016年3月首次到达1288的时间通过生意业务表对每个客户的生意业务金额逐条累计求和,找出最靠近生意业务金额大于或者即是1288的记载。

例如下图是生意业务客户1的累加:第1行的累计消费金额为第一条消费金额12.5第2行的累计薪水为雇员第一条消费金额+第二条消费金额12.5+200之和依次类推...如何盘算出每行的累计薪水?累计求和问题要想到《猴子 从零学会SQL》里讲过的窗口函数,语法如下。1 <窗口函数> over (partition by <用于分组的列名> 2 order by <用于排序的列名>)用聚合函数作为窗口函数,有累计的功效。因为本题是累计“求和”,所以用聚合函数sum。1 select 生意业务客户,生意业务时间, 2 sum(生意业务金额) over (partition by 生意业务客户3 orderby生意业务时间)as"累计消费金额"4 from生意业务表where生意业务时间between"2016-03-01"5 and"2016-03-31"and生意业务类型="消费";查询效果如下将上诉查询效果记载为表c,筛选累计消费金额大于1288的记载1 select *2 from c3 where累计消费金额>1288;查询效果如下:那么如何获得每个客户累计消费金额最先到达1288的记载呢?这时可以根据客户分组,对生意业务时间举行排序(窗口函数 row_number)1 select * , 2 row_number() over(partition by 生意业务客户 3 orderby生意业务时间)as排序4 fromc5 where累计消费金额>1288;查询效果如下:视察获得,每个客户的第一条记载,即累计金额最靠近1288的记载和生意业务时间。

将上诉查询效果记为表d,筛选第一条记载就是。1 select *2 from d 3 where排序=1;查询效果如下3)将上诉查询效果记为表e(每个客户首次累计消费到达1288),通过“生意业务客户”和客户表联络,就可以获得分析要求的客户名称、客户2016年3月首次到达1288的时间1 select a.客户名称,e.生意业务时间2 as 2016年3月首次到达1288的时间from客户表ainnerjoine3 ona.客户号=e.生意业务客户;将前面的步骤一,步骤二整合在一起,得出客户名称,2016年3月总消费金额,2016年首次到达1288时间的三个字段1 select a.客户名称, 2 sum(b.生意业务金额) as 2016年3月总消费金额, 3 e.生意业务时间 as 2016年3月首次到达1288的时间4 from客户表a5 innerjoin生意业务表b6 ona.客户号=b.生意业务客户7 innerjoin8 ( 9 select * 10 from 11 (12 select * ,13 row_number()over partition by 生意业务客户14 order by 生意业务时间) as 排序 15 from16 ( 17 select 生意业务客户,生意业务时间, 18 sum(生意业务金额) over (partition by 生意业务客户 19 order by 生意业务时间) as "累计消费20 金额" 21 from 生意业务表22 where 生意业务时间 between "2016-03-01" and "2016-03-31" 23 and 生意业务类型 = "消费" 24 ) as c where 累计消费金额>1288 25 ) as d where 排序=1 26 ) as e on a.27 客户号 = e.生意业务客户28 where b.生意业务时间 29 between "2016-03-01" 30 and "2016-03-31" and b.生意业务类型="消费" groupbya.客户名称;查询效果如下①得出每个分行2016年3月的消费总金额:通过客户表找到每个客户的所属分行,汇总每个分行的消费金额;②得出每个省行的消费总金额:再通过银行分行对应表找到每个分行对应的上级分行,得出对应省行的总消费金额1)得出每个分行2016年3月的消费总金额因为涉及到3张表的字段,需要3张表联络1 select * 2 from生意业务表 asa3 inner join 客户表 as b 4 on a.生意业务客户 = b.客户号5 inner join 银行分行对应表 as c6 onb.所属分行=c.分行号;筛选出2016年3月份的消费数据1 select *2 from生意业务表 asa3 inner join 客户表 as b4 on a.生意业务客户 = b.客户号5 inner join 银行分行对应表 as c 6 onb.所属分行=c.分行号7 where a.生意业务时间 between "2016-03-01" and "2016-03-31"8 and a.生意业务类型 = "消费";每个分行(分组group by)总生意业务金额(汇总求和sum)和分行对应的上级分行 1 select b.所属分行, 2 sum(a.生意业务金额) as 消费总金额, 3 c.上级分行4 from生意业务表 asa5 inner join 客户表 as b6 on a.生意业务客户 = b.客户号7 inner join 银行分行对应表 as c8 on b.所属分行 = c.分行号9 where a.生意业务时间between"2016-03-01"and"2016-03-31" 10 and a.生意业务类型 = "消费"11 groupbyb.所属分行;查询效果如下2)得出每个省行的消费总金额将上面查询效果记为表d,通过视察可知分行号为"4","2","3"属于江苏省分行分行号"5","6","7"属于广东省分行使用case when 对分行号做判断,切合条件的消费金额相加,得出各省分行的总消费金额。

1 select2 sum(case when 上级分行 in ("4","2","3") 3 then 消费总金额 else null end )as "江苏省分行", 4 sum(case when 上级分行 in ("5","6","7") 5 then 消费总金额 else null end ) as "广东省分行"6 fromd;将上面的步骤1和步骤2和在一起就获得了最终的sql。1 select 2 sum(case when 所属分行 in ("4","2","3") 3 then 消费总金额 else null end )as "江苏省分行", 4 sum(case when 所属分行 in ("5","6","7") 5 then 消费总金额 else null end ) as "广东省分行" 6 from 7 (select b.所属分行, 8 sum(a.生意业务金额) as 消费总金额, 9 c.上级分行 10 from 生意业务表 a11 inner join 客户表 b on a.生意业务客户 = b.客户号 12 inner join 银行分行对应表 c on b.所属分行 = c.分行号13 where14 a.生意业务时间 between "2016-03-01" and "2016-03-31"15 and a.生意业务类型 = "消费" 16 group by b.所属分行) as d;查询效果如下【本题考点】本面试题通过与业务需求联合,考核SQL的综合能力,实际业务场景比力强,通过视察表之间的关系拆分问题写出业务需求。1.如何将庞大的业务问题,使用多维度拆解分析方法去解决2.遇到多条件判断的问题,要想到用case语句来实现3.窗口函数的应用场景,①遇到排名问题,要想到使用窗口函数来实现。

华体会体育

②对于“累计”问题,要想到用聚合函数作为窗口函数。好比累计求和,用sum。4.通过视察数据表的关系,通过多表联络得出想要的字段推荐:如何从零学会sql?。


本文关键词:金融,行业,项目,你,每月,消费,几多,某,理财,华体会体育

本文来源:华体会体育-www.lab18.cn

新闻资讯

联系我们

QQ:545835808

手机:11645358933

电话:0395-818691555

邮箱:admin@lab18.cn

地址:青海省果洛藏族自治州从江县人李大楼459号