如何分析交易记录?

猴子聊人物 2020-09-17 09:34


【题目】


某商场为了分析用户购买渠道。表1是用户交易记录表,记录了用户id、交易日期、交易类型和交易金额。


表2是用户类型表,记录了用户支付类型(微信、支付宝、信用卡等),分别有type1、type2。



要求:

1.请在 type1的用户类型中,找出总交易金额最大的用户。

2.筛选每个用户的第2笔交易记录。

3.如下表:如何实现表3的数据格式?



4.两表做关联时,其中一张表的关联键有大量的null值会造成什么影响?如何规避?


【解题思路】


1.type1的用户类型中,找出单用户的总交易金额最大的一位用户?


我们先来把这个业务需求翻译成大白话:

1)表中的字段要有用户类型、交易金额、用户id

2)“单用户的总交易金额”是指每种类型用户的总交易金额

3)找出用户类型=type1,总交易金额最大的用户


1)表中的字段要有用户类型、交易金额、用户id


交易金额、用户id在用户交易记录表中,用户类型在用户类型表中,涉及到两个表的字段,所以需要进行多表联结。


观察两个表,得知两表的相同字段为用户id ,所以通过用户id联结两张表。


使用哪种联结呢?拿出《猴子 从零学会SQL》里面的多表联结图。



因为后面要分析“每种类型用户的总交易金额”,所以保留左表(用户交易记录表)中的全部用户数据。


select 用户交易记录表.* ,用户类型表.用户类型from 用户交易记录表left join 用户类型表on 用户交易记录表.用户id=用户类型表.用户id;


查询结果:


2)每种类型用户的总交易金额


当有“每个”出现的时候,要想到《猴子 从零学会SQL》中讲过的用分组汇总来实现该业务问题。


根据各用户类型、用户id分组 (group by),统计每个用户数总费用(求和函数sum)


select 用户交易记录表.*,用户类型表.用户类型,       sum(用户交易记录表.交易金额) as 总金额from 用户交易记录表left join 用户类型表on 用户交易记录表.用户id=用户类型表.用户idgroup by 用户类型表.用户类型,用户交易记录表.用户id;


查询结果:



3)找出用户类型=type1,总交易金额最大的用户


用where 筛选用户类型=type1的用户,按照总金额降序排序(order by decs),找出的第一行(limit 1)记录即为交易金额最大的用户。





select 用户类型表.用户类型,  用户交易记录表.用户id,       sum(用户交易记录表.交易金额) as 总金额from 用户交易记录表left join 用户类型表on  用户交易记录表.用户id  = 用户类型表.用户idwhere 用户类型表.用户类型  =  'type1'group by 用户类型表.用户类型,  用户交易记录表.用户idorder by 总金额 desc  limit 1;


查询结果:



2.筛选每个用户的第2笔交易记录?


1)题目要求查询“每个用户”,当每个出现的时候,就要想到分组汇总(group by或者窗口函数的partiotion  by)。


2)第2笔交易记录,是指按照交易时间对每个用户的交易记录进行排名,然后取出排名第2的数据。


又涉及到分组,又涉及到排名的问题,要想到用《猴子 从零学会SQL》里讲过的窗口函数来实现。


所以使用分组(窗口函数partiotion by 用户id),并按最后交易时间升序排列(order by交易时间 asc),套入窗口函数的语法,得出下面的sql语句:



select 用户交易记录表.*,        row_number() over(partition by 用户交易记录表.用户id                           order by 用户交易记录表.交易日期 ascas 交易笔数from 用户交易记录表;


查询结果:



2)用where 筛选出每个用户的第2条记录,就是每个用户的第2笔交易记录

 

select * from(select 用户交易记录表.*,row_number() over(                 partition by 用户交易记录表.用户id                  order  by 用户交易记录表.交易日期 asc ) as 交易笔数from 用户交易记录表) as awhere 交易笔数=2


查询结果:





3.如下表:如何实现表3的数据格式?



题目要求将同一用户、不用的交易时间和交易类型多行合并为一行,用group_concat函数可解决。



sql入下:

select 用户id,group_concat(交易日期), group_concat(交易类型) from 用户交易记录表group by 用户id;


4.两表做关联时,其中一张表的关联键有大量的null值会造成什么影响?如何规避?


因为在关联表中的全部数据不一定在另一张表都匹配,这样没有匹配到的话就会出现null,避免出现表连接出现null值,只要在表关联时加一个where条件进行判断(not null ),具体见下图



【本题考点】


1.如何将复杂的业务问题,使用多维度拆解分析方法,翻译成大白话的能力。


2.遇到排名问题,要想到使用窗口函数来实现。


3.多表联结各个情况如何去实现?把上图看懂就可以解决99%的多表联结问题了。


推荐:如何从零学会sql?


推荐阅读