某游戏的某促销活动，会向玩家推荐一个道具

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_time
    FROM src_rec_action_day
    WHERE action = 'rec'
),
valid_buys AS (
    -- 找出有效期内的首次购买
    SELECT 
        b.role_id,
        b.itemid,
        b.t_when as buy_time,  -- 添加购买时间
        r.start_time
    FROM src_rec_action_day b
    JOIN rec_periods r ON 
        b.role_id = r.role_id AND 
        b.itemid = r.itemid AND
        b.t_when > r.start_time AND 
        b.t_when < r.end_time
    WHERE b.action = 'buy'
    AND NOT EXISTS (
        -- 确保是该道具的第一次购买
        SELECT 1
        FROM src_rec_action_day b2
        WHERE b2.role_id = b.role_id
        AND b2.itemid = b.itemid
        AND b2.action = 'buy'
        AND b2.t_when < b.t_when
    )
),
first_looks AS (
    -- 获取每个推荐期间的第一次查看记录，且要求在购买之前
    SELECT DISTINCT
        l.t_when,
        l.role_id,
        l.action,
        l.itemid
    FROM src_rec_action_day l
    JOIN valid_buys v ON 
        l.role_id = v.role_id AND 
        l.itemid = v.itemid AND
        l.t_when > v.start_time AND  -- 确保查看发生在推荐之后
        l.t_when < v.buy_time        -- 确保查看发生在购买之前
    WHERE l.action = 'look'
    AND NOT EXISTS (
        -- 确保是该推荐期间的第一次查看
        SELECT 1
        FROM src_rec_action_day l2
        WHERE l2.role_id = l.role_id
        AND l2.itemid = l.itemid
        AND l2.action = 'look'
        AND l2.t_when > v.start_time
        AND l2.t_when < l.t_when
    )
)
-- 最终结果：输出成功推荐流程中的首次查看记录
SELECT 
    f.t_when,
    f.role_id,
    f.