你今天玩游戏了吗？游戏道具了解下
【面试题】
某游戏的某促销活动，会向玩家推荐一个道具，同时会得到该道具的折扣券。折扣券无有效期，但购买道具一次后失效。推荐一个新的道具，也会导致旧的折扣券失效。
假设道具推荐、查看、购买行为记录在了下面的“游戏道具记录表”中，包含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;
【本题考点】
1）考查对窗口函数的了解；
2）考查对多表联结的了解。