用户来源去向SQL案例(附思路)

简杨
简杨
简杨
8
文章
0
评论
2020-04-1802:05:00 评论 295 2065字
摘要

最近遇到一道关于用户来源及去向的SQL题目,本文专门模拟数据从问题出发,整理思路并给出解答。总的来说这道SQL题目并不难,关键在于思路是否清晰,其次才是基础知识的考验。

一、案例

现有一批数据记录为某天多个用户app的使用情况,数据存放在record表中,其中有四个字段类型:

id:记录的id标识

user_id:用户的id标识

app_name:用户打开app的应用名称

create_time:用户打开app的时间

用户来源去向SQL案例(附思路)

record表记录

现要求得不同来源去向的用户数,即淘宝->京东、京东->唯品会等来源去向的用户数,并写出对应的SQL语句。

二、思路及解答

首先,观察数据后发现用户可能有多次打开同一app和用户只打开一个app的情况。其次,打开app的时间都在某天所以这里不考虑按不同天来计算。这里我们需要将原问题拆解为小问题,需要思考的问题有(如有遗漏可在评论区留言):

如何表示用户使用app的来源及去向?

怎么判断app的使用顺序?

用户多次打开app会不会影响统计结果?

用户只打开一个app并没有去向如何处理?

如何计算不同来源去向的用户数?

解决问题1和2需要查询到用打开app后下一个打开app是哪个。在SQL语言里面如果要得到两两交叉的结果,需要使用到交叉关联,同时需要是同个用户打开的app,具体实现代码如下:

01 SELECT

02 a.user_id,

03 a.app_name AS start_app,

04 a.create_time AS start_time,

05 b.app_name AS end_app,

06 b.create_time AS end_time

07 FROM record a

08 JOIN record b

09 WHERE a.user_id = b.user_id

用户来源去向SQL案例(附思路)

查询1的结果

从上图的查询结果可以看到两个问题,交叉关联后来源与去向重合,来源app打开时间要大于去向打开时间。于是我们需要添加两个条件即start_app不等于end_app,start_time要小于end_time,修改后的代码如下:

01 SELECT

02 a.user_id,

03 a.app_name AS start_app,

04 a.create_time AS start_time,

05 b.app_name AS end_app,

06 b.create_time AS end_time

07 FROM record a

08 JOIN record b

09 WHERE a.user_id = b.user_id

10 AND a.app_name != b.app_name

11 AND a.create_time < b.create_time

用户来源去向SQL案例(附思路)

查询2的结果

这样的处理同时问题4也解决,因为当用户只打开一个app的时候交叉关联后去向app还是自身,所以在上述操作中已经过滤。从上图可以发现1001用户在打开唯品会前打开过两次淘宝,故有两条来源去向的记录,这里就会造成重复统计,所以我们需要用户最后一次的来源去向记录即可,具体修改如下:

01 SELECT

02 a.user_id,

03 a.app_name AS start_app,

04 MAX(a.create_time) AS start_time,

05 b.app_name AS end_app,

06 b.create_time AS end_time

07 FROM record a

08 JOIN record b

09 WHERE a.user_id = b.user_id

10 AND a.app_name != b.app_name

11 AND a.create_time < b.create_time

12 GROUP BY a.user_id, a.app_name, b.app_name

用户来源去向SQL案例(附思路)

查询3的结果

现在,我们需要计算不同来源去向的用户占比,即求得来源和去向分组后的user_id除重的结果,具体实现代码如下:

01 SELECT

02 start_app,

03 end_app,

04 COUNT(DISTINCT user_id) AS user_num

05 FROM (

06 SELECT

07 a.user_id,

08 a.app_name AS start_app,

09 MAX(a.create_time) AS start_time,

10 b.app_name AS end_app,

11 b.create_time AS end_time

12 FROM record a

13 JOIN record b

14 WHERE a.user_id = b.user_id

15 AND a.app_name != b.app_name

16 AND a.create_time < b.create_time

17 GROUP BY a.user_id, a.app_name, b.app_name

18 ) groups

19 GROUP BY start_app, end_app

用户来源去向SQL案例(附思路)

最后汇总的结果

三、总结

做SQL题目与写代码一样,重要的是能得到最终的结果,故查询效率最后考虑,需要优先考虑查询后的结果是否需要进行筛选以及重复记录的情况。将问题拆解为小问题有助于降低问题难度,同时也能对多方面有所考虑。总的来说这道SQL题目并不难,关键在于思路是否清晰,其次才是基础知识的考验。

End.

作者:简杨君(挖数网特邀认证作者)

本文为中国统计网原创文章,未经允许禁止转载,需要转载请微信联系授权(微信号:ishujiang)

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: