你今天玩游戏了吗？游戏道具了解下

【面试题】

某游戏的某促销活动，会向玩家推荐一个道具，同时会得到该道具的折扣券。折扣券无有效期，但购买道具一次后失效。推荐一个新的道具，也会导致旧的折扣券失效。

假设道具推荐、查看、购买行为记录在了下面的“游戏道具记录表”中，包含4个字段：时间、玩家id、行为、道具id。

表中的“行为”字段的值包括：向玩家推荐道具、玩家查看道具、玩家成功付费购买道具

数据说明：

1）一个道具的折扣券失效后，玩家仍然可以以原价购买推荐的道具

2）同一个道具不会被重复推荐

3）如果玩家使用折扣券购买道具，则认为这次推荐道具->查看道具->购买道具属于一个成功推荐过程，推荐道具->购买道具（即中途缺少该道具的查看记录）不属于成功推荐过程。

4）玩家可以推荐前查看道具，但不会获得折扣券

【问题】查询所有成功推荐过程中，该道具的第一条查看记录。

【解题步骤】

1. 解题思路

这个业务问题一看很复杂，遇到复杂的问题，要想到用逻辑树分析方法，将复杂问题拆解为可以解决的子问题：

1）找出所有成功推荐的记录（成功推荐是玩家使用折扣券进行购买）

2）找出成功推荐时间与对应购买时间之间的查看记录

3）筛选出每一次成功推荐的第一条查看记录

2. 所有成功推荐的记录

一次成功推荐是“推荐道具->查看道具->购买道具”这样的过程。

1）获取所有推荐道具的数据

select 时间,玩家id,行为,道具id
from 游戏道具记录表
where 行为 = '推荐道具';

记为表t1。

2）获取所有购买道具的数据

select 时间,玩家id,行为,道具id
from 游戏道具记录表
where 行为 = '玩家购买';

记为表t2。

3）获取同一个玩家同一个道具的“推荐道具->购买道具”

以“所有推荐道具的数据”为左表，使用左联结，从“所有购买道具的数据”中获取同一个玩家同一个道具的购买记录（购买时间在推荐时间之后）。

由于一次推荐之后，可能存在多次购买记录，此处使用窗口函数每次推荐之后对应的购买时间顺序。

select t1.玩家id,t1.时间 as 推荐道具时间,t1.道具id as 推荐道具id,t2.时间 as 玩家购买时间,t2.道具id as 玩家购买道具id,row_number() over(partition by t1.玩家id,t1.时间,t1.道具id order by t2.时间) as 玩家购买时间顺序
from t1
left join 
t2 on t1.玩家id = t2.玩家id and t1.道具id = t2.道具id
where t1.时间 < t2.时间;

查询结果：

以上结果记为表t3。

4）对“玩家购买时间顺序”进行筛选

按照成功推荐的定义，“玩家购买时间顺序”为1时，才可能成为成功推荐。

因为，购买道具一次后道具对应的折扣券会失效。

select 玩家id,推荐道具时间,推荐道具id,玩家购买时间,玩家购买道具id
from t3
where 玩家购买时间顺序 = 1;

以上结果记为表tt1。

5）得到成功推荐的记录

上一步的结果还不是成功推荐。

因为，如果“推荐道具时间”与“玩家购买时间”之间有另外的一次推荐，这次的推荐的折扣券就失效了。

查询出“推荐道具时间”与“玩家购买时间”之间没有其他推荐的记录。

select tt1.玩家id,tt1.推荐道具时间,tt1.推荐道具id,tt1.玩家购买时间,tt1.玩家购买道具id
from tt1
left join t1 on tt1.玩家id = t1.玩家id and tt1.推荐道具时间 < t1.时间 and tt1.玩家购买时间 > t1.时间
where t1.行为 is null;

以上结果记为数据a1。

3. 获取成功推荐中的查看记录

1）获取所有查看道具的数据

select 时间,玩家id,行为,道具id
from 游戏道具记录表
where 行为 = '玩家查看';

记为表a2。

2）获取成功推荐记录中“推荐道具时间”与“玩家购买时间”之间的所有查看记录

select a1.玩家id,a1.推荐道具时间,a1.推荐道具id,a1.玩家购买时间,a1.玩家购买道具id,a2.时间 as 玩家查看时间,a2.道具id as 玩家查看道具id,row_number() over(partition by a1.玩家id,a1.玩家购买时间,a1.玩家购买道具id order by a2.时间) as 查看时间顺序
from a1
left join 
a2 on a1.玩家id = a2.玩家id and a1.推荐道具id = a2.道具id
where a2.时间 > a1.推荐道具时间 and a2.时间 <= a1.玩家购买时间;

同时，用窗口函数row_number()获取了“查看时间顺序”。

以上结果记为a3。

4. 获取成功推荐中的第一条查看记录

最后，筛选出第一次查看的记录，并恢复成初始的数据结构。

select 玩家查看时间 as 时间,玩家id,'玩家查看' as 行为,玩家查看道具id as 道具id
from a3
where 查看时间顺序 = 1;

将子查询代入：

select 玩家查看时间 as 时间,玩家id,'玩家查看' as 行为,玩家查看道具id as 道具id
from (
select a1.玩家id,a1.推荐道具时间,a1.推荐道具id,a1.玩家购买时间,a1.玩家购买道具id,a2.时间 as 玩家查看时间,a2.道具id as 玩家查看道具id,row_number() over(partition by a1.玩家id,a1.玩家购买时间,a1.玩家购买道具id order by a2.时间) as 查看时间顺序
from (
select tt1.玩家id,tt1.推荐道具时间,tt1.推荐道具id,tt1.玩家购买时间,tt1.玩家购买道具id
from (
select 玩家id,推荐道具时间,推荐道具id,玩家购买时间,玩家购买道具id
from (
select t1.玩家id,t1.时间 as 推荐道具时间,t1.道具id as 推荐道具id,t2.时间 as 玩家购买时间,t2.道具id as 玩家购买道具id,row_number() over(partition by t1.玩家id,t1.时间,t1.道具id order by t2.时间) as 玩家购买时间顺序
from (
select 时间,玩家id,行为,道具id
from 游戏道具记录表
where 行为 = '推荐道具'
) as t1
left join (
select 时间,玩家id,行为,道具id
from 游戏道具记录表
where 行为 = '玩家购买'
) as t2 on t1.玩家id = t2.玩家id and t1.道具id = t2.道具id
where t1.时间 < t2.时间
) as t3
where 玩家购买时间顺序 = 1
) as tt1
left join (
select 时间,玩家id,行为,道具id
from 游戏道具记录表
where 行为 = '推荐道具'
) as tt2 on tt1.玩家id = tt2.玩家id and tt1.推荐道具时间 < tt2.时间 and tt1.玩家购买时间 > tt2.时间
where tt2.行为 is null
) as a1
left join (
select 时间,玩家id,行为,道具id
from 游戏道具记录表
where 行为 = '玩家查看'
) as a2 on a1.玩家id = a2.玩家id and a1.推荐道具id = a2.道具id
where a2.时间 > a1.推荐道具时间 and a2.时间 <= a1.玩家购买时间
) as a3
where 查看时间顺序 = 1;