用户来源去向SQL案例（附思路）

一、案例

现有一批数据记录为某天多个用户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）