如何分析交易记录？

【题目】

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

表2是用户类型表&＃xff0c;记录了用户支付类型&＃xff08;微信、支付宝、信用卡等&＃xff09;&＃xff0c;分别有type1、type2。

要求&＃xff1a;

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

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

3.如下表&＃xff1a;如何实现表3的数据格式&＃xff1f;

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

【解题思路】

1.type1的用户类型中&＃xff0c;找出单用户的总交易金额最大的一位用户&＃xff1f;

我们先来把这个业务需求翻译成大白话&＃xff1a;

1&＃xff09;表中的字段要有用户类型、交易金额、用户id

2&＃xff09;“单用户的总交易金额”是指每种类型用户的总交易金额

3&＃xff09;找出用户类型&＃61;type1&＃xff0c;总交易金额最大的用户

1&＃xff09;表中的字段要有用户类型、交易金额、用户id

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

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

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

因为后面要分析“每种类型用户的总交易金额”&＃xff0c;所以保留左表&＃xff08;用户交易记录表&＃xff09;中的全部用户数据。

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


查询结果&＃xff1a;

2&＃xff09;每种类型用户的总交易金额

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

根据各用户类型、用户id分组 &＃xff08;group by&＃xff09;&＃xff0c;统计每个用户数总费用&＃xff08;求和函数sum&＃xff09;

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


查询结果&＃xff1a;

3&＃xff09;找出用户类型&＃61;type1&＃xff0c;总交易金额最大的用户

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

select 用户类型表.用户类型,  用户交易记录表.用户id,sum(用户交易记录表.交易金额) as 总金额
from 用户交易记录表
left join 用户类型表
on 用户交易记录表.用户id &＃61; 用户类型表.用户id
where 用户类型表.用户类型 &＃61; &＃39;type1&＃39;
group by 用户类型表.用户类型, 用户交易记录表.用户id
order by 总金额 desc limit 1;


查询结果&＃xff1a;

2.筛选每个用户的第2笔交易记录&＃xff1f;

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

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

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

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

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


查询结果&＃xff1a;

2&＃xff09;用where 筛选出每个用户的第2条记录&＃xff0c;就是每个用户的第2笔交易记录

select * from
(select 用户交易记录表.*,
row_number() over(partition by 用户交易记录表.用户id order by 用户交易记录表.交易日期 asc ) as 交易笔数
from 用户交易记录表) as a
where 交易笔数&＃61;2&＃xff1b;


查询结果&＃xff1a;

3.如下表&＃xff1a;如何实现表3的数据格式&＃xff1f;

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

sql入下&＃xff1a;

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


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

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

【本题考点】

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

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

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

推荐&＃xff1a;如何从零学会sql&＃xff1f;