一、案例
现有一批数据记录为某天多个用户app的使用情况,数据存放在record表中,其中有四个字段类型:
id:记录的id标识
user_id:用户的id标识
app_name:用户打开app的应用名称
create_time:用户打开app的时间
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
查询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

查询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
查询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题目并不难,关键在于思路是否清晰,其次才是基础知识的考验。
End.
作者:简杨君(挖数网特邀认证作者)
本文为中国统计网原创文章,未经允许禁止转载,需要转载请微信联系授权(微信号:ishujiang)
- 我的微信公众号
- 微信扫一扫
-
- 我的微信公众号
- 微信扫一扫
-
评论