天池新人实战赛《阿里移动推荐算法》(二)—— 平台赛人工规则

　　　注：作为新手，基本思路来源于天池官网的视频教程，本文仅用于在天池的数加平台上学习后所作的个人总结，特此声明。

　　　视频网址：https://tianchi.shuju.aliyun.com/video.htm?spm=5176.100068.1234.7.7Kftz1 《新手入门赛-第三课时》

一、初始化数据：

　　step0_original_data

--odps sql
--**********************************************************************************--
--author:smilemoon
--create time:2017-01-16 9:46
--**********************************************************************************--
--导入用户历史行为表
DROP TABLE IF EXISTS DX_TRAIN_USER;

CREATE TABLE DX_TRAIN_USER
AS
SELECT *
FROM odps_tc_257100_f673506e024.tianchi_fresh_comp_train_user_online;

--导入商品历史行为表
DROP TABLE IF EXISTS DX_TRAIN_ITEM;

CREATE TABLE DX_TRAIN_ITEM
AS
SELECT *
FROM odps_tc_257100_f673506e024.tianchi_fresh_comp_train_item_online;

 

二、分割训练集和测试集数据：

　　思路：使用第1天到第29天（2014-11-18~2014-12-17）的数据作为训练集，第30天（2014-12-18）作为测试规则的集合。

--odps sql
--**********************************************************************************--
--author:smilemoon
--create time:2017-01-16 9:59
--**********************************************************************************--
-- 划分逻辑：根据赛题任务，确定划分的时间节点为“2014-12-18 00”
-- 训练集
DROP TABLE IF EXISTS DX_DATA_TRAIN;

-- 获取所有2014-12-18号之前的数据作为训练集
CREATE TABLE DX_DATA_TRAIN
AS
SELECT 
    *
FROM 
    DX_TRAIN_USER
WHERE 
    TIME < '2014-12-18 00';

-- 测试集
DROP TABLE IF EXISTS DX_DATA_TEST;

CREATE TABLE DX_DATA_TEST
AS
SELECT 
    A.USER_ID, A.ITEM_ID
FROM (
    --获取所有2014-12-18当天的购买记录（ BEHAVIOR_TYPE = 4），并按照user_id,item_id分组
    SELECT 
        USER_ID, ITEM_ID
    FROM 
        DX_TRAIN_USER
    WHERE TIME >= '2014-12-18 00'
        AND BEHAVIOR_TYPE = 4
    GROUP BY USER_ID, 
        ITEM_ID
)A
--左外连接DX_TRAIN_ITEM 规定的商品子集
--思考：为什么使用左外连接？
--左外连接：先保留所有左边的购买记录，右边可能会出现ITEM_ID为null的情况
--再根据 WHERE !ISNULL(B.ITEM_ID)排除掉
LEFT OUTER JOIN DX_TRAIN_ITEM B
-- RIGHT OUTER JOIN DX_TRAIN_ITEM B
-- FULL OUTER JOIN DX_TRAIN_ITEM B
ON A.ITEM_ID = B.ITEM_ID
WHERE !ISNULL(B.ITEM_ID)
GROUP BY A.USER_ID, A.ITEM_ID;

--能否使用右外连接和全外连接？
--经测试,右连接与全外连接得到的F1值没有发生改变，目前仅从F1的维度来看，不考虑执行效率，与左外链接没有什么区别。

 

三、单一维度人工规则构造：

--odps sql
--**********************************************************************************--
--author:smilemoon
--create time:2017-01-16 10:40
--**********************************************************************************--

--第一条规则(最近购物车规则)：用户最后一天加入购物车的商品，下一天会购买
--优化1：用户购买了当天加入购物车的商品，下一天不会购买。
--优化2：用户后半天加入购物车的商品，下一天更可能购买。

DROP TABLE IF EXISTS DX_R_1;
CREATE TABLE DX_R_1 AS
SELECT  C.USER_ID,C.ITEM_ID
FROM (
    SELECT A.USER_ID,A.ITEM_ID
    FROM 
    (
        -- 查找在2014-12-17 12点到2014-12-18 0点之间发生过加入到购物车行为的记录 A
        SELECT USER_ID,ITEM_ID
        FROM DX_DATA_TRAIN
        WHERE TIME >= "2014-12-17 12" AND TIME < "2014-12-18 00" AND BEHAVIOR_TYPE == 3
        GROUP BY USER_ID,ITEM_ID
    )A
    LEFT OUTER JOIN
    (
        -- 查找在2014-12-17 12点到2014-12-18 0点之间发生过加入到购买行为的记录 B
　　　　　　 -- 经测试2014-12-17 13点的F1要略高些 5.95->5.97

        SELECT USER_ID,ITEM_ID
        FROM DX_DATA_TRAIN
        WHERE TIME >= "2014-12-17 12" AND TIME < "2014-12-18 00" AND BEHAVIOR_TYPE == 4
        GROUP BY USER_ID,ITEM_ID
    )B
    ON A.USER_ID == B.USER_ID AND A.ITEM_ID == B.ITEM_ID
    -- 取ISNULL(B.ITEM_ID) 是因为在当天加入购物车并购买的人 一般情况下第二天会继续购买的概率会很低
    WHERE ISNULL(B.ITEM_ID)
--查找在2014-12-17 12点到2014-12-18 0点之间发生过加入到购物车行为,但没有购买的记录 C
)C
-- 并且商品是在规定的商品子集之中
LEFT OUTER JOIN DX_TRAIN_ITEM D
ON C.ITEM_ID == D.ITEM_ID
WHERE !ISNULL(D.ITEM_ID)
GROUP BY C.USER_ID,C.ITEM_ID;

 

 

四、多维度规则构造：

　　

五、F1公式线下评估：

--odps sql
--**********************************************************************************--
--author:smilemoon
--create time:2017-01-16 11:12
--**********************************************************************************--


SELECT C.PRECISION,C.RECALL,2 * C.PRECISION * C.RECALL / (C.PRECISION + C.RECALL) AS F1
FROM
(
    --格式：IF(Condition,A,B)
    --意义：当Condition为TRUE时，返回A；当Condition为FALSE时，返回B。
    SELECT SUM(IF(!ISNULL(A.ITEM_ID) AND !ISNULL(B.ITEM_ID),1,0)) / SUM(IF(!ISNULL(A.ITEM_ID),1,0)) AS PRECISION,
           SUM(IF(!ISNULL(A.ITEM_ID) AND !ISNULL(B.ITEM_ID),1,0)) / SUM(IF(!ISNULL(B.ITEM_ID),1,0)) AS RECALL
    FROM DX_R_1 A
    --进行全外连接 先将所有的数据拼装成一张大表
    FULL OUTER JOIN DX_DATA_TEST B
    ON A.USER_ID == B.USER_ID AND A.ITEM_ID == B.ITEM_ID
)C;

 

 

 六、提交预测结果：

　　基本过程与规则相同，但是要修改训练数据集为所有数据，也就是DX_TRAIN_USER。日期范围也要从2016-11-18~2016-12-17扩大到2016-11-18~2016-12-18。

--odps sql
--**********************************************************************************--
--author:smilemoon
--create time:2017-01-16 11:35
--**********************************************************************************--

DROP TABLE IF EXISTS TIANCHI_MOBILE_RECOMMENDATION_PREDICT;
CREATE TABLE TIANCHI_MOBILE_RECOMMENDATION_PREDICT AS 
SELECT  C.USER_ID,C.ITEM_ID
FROM (
    SELECT A.USER_ID,A.ITEM_ID
    FROM 
    (
        SELECT USER_ID,ITEM_ID
        FROM DX_TRAIN_USER
        WHERE TIME >= "2014-12-18 12" AND TIME < "2014-12-19 00" AND BEHAVIOR_TYPE == 3
        GROUP BY USER_ID,ITEM_ID
    )A
    LEFT OUTER JOIN
    (
        SELECT USER_ID,ITEM_ID
        FROM DX_TRAIN_USER
        WHERE TIME >= "2014-12-18 12" AND TIME < "2014-12-19 00" AND BEHAVIOR_TYPE == 4
        GROUP BY USER_ID,ITEM_ID
    )B
    ON A.USER_ID == B.USER_ID AND A.ITEM_ID == B.ITEM_ID
    WHERE ISNULL(B.ITEM_ID)
)C
LEFT OUTER JOIN DX_TRAIN_ITEM D
ON C.ITEM_ID == D.ITEM_ID
WHERE !ISNULL(D.ITEM_ID)
GROUP BY C.USER_ID,C.ITEM_ID;