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

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

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

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

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

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

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

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

【解题步骤】

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

1.计算2016年1-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 交易类型 = "消费";

查询结果如下

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

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

ITPub博客

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

最新文章