数据分析师 ---- SQL强化(1)
写在前面
最近在找工作中发现,数据分析师的笔试多数会涉及SQL,但是笔试中SQL的难度和我们在学习中和平常遇到的不是一个层次,笔试中的问题更加的贴近业务,对于应届生来说还是比较有难度的(也可能是我太菜)。
这个SQL专栏会记录自己在面试或者在刷题中遇到比较有价值的题目,希望能够帮助大家,也希望大家多多点赞和关注。
题目
请用一句SQL取出所有用户对商品的行为特征,特征分为已购买、购买未收藏、收藏未购买、收藏且购买
订单表:orders
收藏表:favorites
最后输出:
题意分析:
通过题目我们可以很清楚的知道这是一个多表连接的问题,连接两个表之后根据字段的内容进行判断
关于多表查询涉及的知识点
第一步:表连接以及表拼接
通过对题目的分析我们能够发现这是一个两个表全连接的问题,在Oracle数据库中可以直接进行全连接,但是在MySQL数据库则不支持全连接,我们可以考虑将两个查询的内容进行拼接union all
关键字
select o.user_id, o.item_id,o.pay_time,f.fav_time
from orders o left join favorites f
on o.user_id = f.user_id and o.item_id = f.item_id
UNION ALL
select f.user_id, f.item_id,o.pay_time,f.fav_time
from orders o right join favorites f
on o.user_id = f.user_id and o.item_id = f.item_id
where o.user_id is null
解释拼接全连接代码中采用了左外连接和右内连接进行,因为这样查询的数据不会重复,直接使用
union all
进行合并
从优化的角度看使用union all 的效率会比union的效率高
第二步:新建列以及填充值
通过上表的查询我们能够发现可以根据付款的时间以及收藏的时间进行判断用户的购买和收藏情况
使用case when
进行区分
select distinct user_id,item_id,
case when pay_time is not null then 1 else 0 end '已购买',
case when pay_time is not null and fav_time is null then 1 else 0 end '购买未收藏',
case when pay_time is null and fav_time is not null then 1 else 0 end '收藏未购买',
case when pay_time is not null and fav_time is null then 1 else 0 end '收藏且购买'
from (
select o.user_id, o.item_id,o.pay_time,f.fav_time
from orders o left join favorites f
on o.user_id = f.user_id and o.item_id = f.item_id
UNION ALL
select f.user_id, f.item_id,o.pay_time,f.fav_time
from orders o right join favorites f
on o.user_id = f.user_id and o.item_id = f.item_id
where o.user_id is null
) tmp
order by user_id, item_id;
同样也可以只用if
进行判断
select distinct user_id,item_id,
if(pay_time,1,0) '已购买',
if(pay_time is not null and fav_time is null,1,0) '购买未收藏',
if(pay_time is null and fav_time is not null,1,0) '收藏未购买',
if(pay_time is not null and fav_time is null,1,0) '收藏且购买'
from (
select o.user_id, o.item_id,o.pay_time,f.fav_time
from orders o left join favorites f
on o.user_id = f.user_id and o.item_id = f.item_id
UNION ALL
select f.user_id, f.item_id,o.pay_time,f.fav_time
from orders o right join favorites f
on o.user_id = f.user_id and o.item_id = f.item_id
where o.user_id is null
) tmp
order by user_id, item_id;
文章来源:https://www.toymoban.com/news/detail-420388.html
总结
这道题主要考察了多表查询,全外连接,union
,union all
,case when
, 难点就是一开始不知道如何下手,不知道怎么将两个表的数据结合起来,也不知道怎么新增列,还可能只知道处理一个表中的数据。
其实在遇见这种题目的时候我们可以一步一步来,将问题拆解,先将两个表关联起来,后面在根据字段的情况在进行新增列。
文章来源地址https://www.toymoban.com/news/detail-420388.html
到了这里,关于数据分析师 ---- SQL强化(1)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!