某游戏的某促销活动,会向玩家推荐一个道具
1.题目需求如下:
某游戏的某促销活动,会向玩家推荐一个道具,同时会得到该道具的折扣券。折扣券无有效期,但购买道具一次后失效。推荐一个新的道具,也会导致旧的折扣券失效。假设道具推荐、查看、购买行为记录了如下数据表:
desc src_rec_action_day;
t_when string # 记录时间
role_id string # 游戏角色唯一标识
action string # 行为标识
itemid string # 道具id
action取值含义:
rec 向玩家推荐道具
look 玩家查看道具
buy 玩家成功付费购买道具
数据样例:
t_when role_id action itemid
2020-01-01 08:00:00 110 rec 10002 // 推荐道具10002
2020-01-01 08:10:00 110 look 10001
2020-01-01 08:20:00 110 look 10002
2020-01-01 10:00:00 110 rec 10001 // 推荐10001,原10002折扣券失效
2020-01-01 12:10:00 110 look 10001
2020-01-01 12:20:00 110 look 10002
2020-01-01 12:30:00 110 look 10001
2020-01-01 12:30:00 110 buy 10001 // 购买道具10001,折扣券生效
2020-01-01 12:40:00 110 buy 10002 // 购买道具10002,折扣券已失效
2020-01-01 12:45:00 110 look 10001
2020-01-01 12:50:00 110 buy 10001 // 再次购买道具10001,折扣券已失效
2020-01-01 13:00:00 110 rec 10003 // 推荐道具10003
2020-01-01 13:10:00 110 buy 10003 // 道具10003已在购物车,所以没有道
具查看记录,只有道具购买记录,10003折扣券会被使用
2020-01-01 13:20:00 110 look 10003
2020-01-01 13:30:00 110 buy 10003 // 之前折扣券已使用,所以以原价购买
数据说明:
1)一个道具的折扣券失效后,玩家仍然可以以原价购买推荐的道具。
2)同一个道具不会被重复推荐。
3)如果玩家使用折扣券购买道具,则认为这次rec-look-buy属于一个成功推荐过程,rec-buy(即中途缺少该道具的look记录)不属于成功推荐过程。
4)玩家可以推荐前查看道具,但不会获得折扣券。
结果输出:
输出所有成功推荐过程中,该道具的第一条查看记录(必然在rec到buy之间)。可根据对python和mysql的熟悉程度,选用其中之一实现。(python以src_rec_action_day.txt为输入文件,无字段标题,tab分隔)
注:同一个时刻对同一个玩家最多只会推荐一个道具。
数据样例输出结果:
2020-01-01 12:10:00 110 look 10001
2.SQL实现方式详解(MYSQL)
2.1WITH RECURSIVE rec_periods AS (...): 这是一个递归查询,用于获取每个角色的推荐记录及其有效期。根据题目推荐一个新的道具,也会导致旧的折扣券失效以及同一个道具不会被重复推荐
- LEAD函数:用于获取每个推荐记录的下一个记录的时间,以确定推荐记录的有效期。
- COALESCE函数:用于将推荐记录的有效期设置为当前记录的结束时间,如果没有下一个记录,则设置为'9999-12-31 23:59:59'。这是为了确保最后一个记录的结束时间是一个合理的值。
2.2 valid_buys AS (...): 这是一个查询,用于找出在推荐记录有效期内的首次购买。
- JOIN子句:用于将购买记录与推荐记录进行关联,并筛选出购买记录发生在推荐记录有效期内的记录。
- NOT EXISTS子句:用于确保购买记录是该道具的第一次购买。
2.3 first_looks AS (...): 这是一个查询,用于获取每个推荐期间的第一次查看记录,且要求在购买之前。
- JOIN子句:用于将查看记录与有效购买记录进行关联,并筛选出查看记录发生在推荐之后和购买之前的记录。
- NOT EXISTS子句:用于确保查看记录是该推荐期间的第一次查看。
- SELECT语句:最终结果,输出成功推荐流程中的首次查看记录。
- ORDER BY子句:按照查看时间进行排序。
2.4涉及到的函数用法:
- LEAD函数:LEAD(column, offset, default)。该函数返回指定列的当前记录的下一个记录的值。offset表示偏移量,用于确定下一个记录。如果没有下一个记录,则返回default值。
- COALESCE函数:COALESCE(value1, value2, ...)。该函数返回参数列表中第一个非NULL值。
- EXISTS子句:EXISTS(subquery)。该子句检查子查询是否返回任何行,如果返回行,则返回TRUE;否则返回FALSE。
- JOIN子句:JOIN table ON condition。该子句用于将两个或多个表进行连接,根据指定的条件进行匹配。
- NOT EXISTS子句:NOT EXISTS(subquery)。该子句检查子查询是否不返回任何行,如果不返回行,则返回TRUE;否则返回FALSE。
- SELECT语句:用于查询数据库中的数据,并返回结果集。
2.5 最终代码
WITH RECURSIVE rec_periods AS (-- 获取每个角色的推荐记录及其有效期SELECT role_id,itemid,t_when as start_time,COALESCE(LEAD(t_when) OVER (PARTITION BY role_id ORDER BY t_when),'9999-12-31 23:59:59') as end_timeFROM src_rec_action_dayWHERE action = 'rec'
),
valid_buys AS (-- 找出有效期内的首次购买SELECT b.role_id,b.itemid,b.t_when as buy_time, -- 添加购买时间r.start_timeFROM src_rec_action_day bJOIN rec_periods r ON b.role_id = r.role_id AND b.itemid = r.itemid ANDb.t_when > r.start_time AND b.t_when < r.end_timeWHERE b.action = 'buy'AND NOT EXISTS (-- 确保是该道具的第一次购买SELECT 1FROM src_rec_action_day b2WHERE b2.role_id = b.role_idAND b2.itemid = b.itemidAND b2.action = 'buy'AND b2.t_when < b.t_when)
),
first_looks AS (-- 获取每个推荐期间的第一次查看记录,且要求在购买之前SELECT DISTINCTl.t_when,l.role_id,l.action,l.itemidFROM src_rec_action_day lJOIN valid_buys v ON l.role_id = v.ro