阿里推荐大赛：ODPS SQL 入门

第二季才是真正的开始，第一季的汗水、喜悦、纷争都通通过去了。第二季绝对是拼算法、拼模型、拼平台熟练度的比赛，相信坚持下来的话肯定收获颇丰。昨天也迫不及待地进入「天池」，居然是 Windows 系统，一点都提升不了逼格啊。看文档的过程中，又是 ODPS SQL、MapReduce、XLab/XLib、Tunnel、ODPS SDK什么的，相信很多人都跟我一样看的眼花，茫然不知所措，更别提对 MR 不熟悉、SQL 忘地差不多、Java 不熟练的同学了。到底先学哪个？到底哪个实现模型算法更快？

文档里推荐的开发流程是这样的：

首先看能否使用 ODPS SQL 搞定，能的话尽量用 SQL，简单，不容易出错；

编写 M/R 代码，使用本地调试功能进行基本的测试；

编写 M/R 单元测试用例，ODPS M/R 提供了 MRUnitTest 方便编写单元测试用例；

进行集群调试，验证结果。

所以从 SQL 入门应该是学习曲线最快的，而且也能方便进行分析，快速实现模型，快速提交结果。

如果您已经看过文档了，小弟觉得就可以跳过下面的啰嗦的内容了：）

##提交推荐结果
首先，第二季的数据格式与第一季一样，仍是4月15日至8月15日的数据，只不过量更大了（5亿条用户行为记录，不知道是否是全集？）。

原始数据集存在t_alibaba_bigdata_user_brand_total_1表中，完成用户品牌偏好预测后，需将推荐结果需放入t_tmall_add_user_brand_predict_dh表中，格式与第一季一样。系统会每天凌晨自动评分排名。

在 Readme 中有一个通过训练数据集完成用户品牌购买预测的例子，所用的规则是将每个用户点击最多的 10 个品牌，作为预测结果提交。SQL 代码如下：

drop table t_tmall_add_user_brand_predict_dh;
create table t_tmall_add_user_brand_predict_dh as
select
    user_id,
    wm_concat(',',brand_id) as brand
from(
    select
        user_id,
        brand_id,
        row_number() over (partition by user_id order by num desc) as rank
    from(
        select
            user_id,
            brand_id,
            count(brand_id) as num
        from
            t_alibaba_bigdata_user_brand_total_1
        where type='0'
        group by user_id,brand_id
    )a
)b
where rank <= 10
group by user_id;
``` 

这段代码已经有了 sql 编程的感觉了。分析下这段代码，由三层`select`组成。最底层是统计用户对品牌产生点击行为的次数。第二层`select`中有一行代码比较陌生：

```sql
row_number() over (partition by user_id order by num desc) as rank

row_num()是 ODPS SQL 的一个内建窗口函数，功能是在指定开窗口列中按指定列排序后返回所在行号。在这个例子中就是：将 user_id 作为窗口列，按 num 递减排序后，返回对应 user_id 的所在行号。这里使用rank()函数的话，也能实现相同的功能。

所以第二层select就是统计用户对品牌产生点击次数由高到低的排名。最后一层select就是只取每个用户点击最多的 10 个品牌，并将这些品牌用逗号连接作为brand属性的值。wm_concat(string separator, string str)是 ODPS SQL 的一个内建聚合函数，功能是用指定的 spearator 做分隔符，链接str中的值。

最后用create table .. as select ..将查询出来的结果表直接复制给t_tmall_add_user_brand_predict_dh表，完成推荐。注意到每个内层子查询必须要有别名。

看，用 sql 来做写推荐也不难嘛。模仿上面的代码，应该能写出其他规则的推荐。比如将 type='0' 改成 type='1'，就成了每个用户购买最多的 10 个品牌（有将近6的召回我是不会跟你说的）。

回顾第一季，最简单有效的一个规则应该是「8月份的用户购买记录」。下面是一个简单实现：

drop table t_tmall_add_user_brand_predict_dh;
create table t_tmall_add_user_brand_predict_dh as
select
    user_id,
    wm_concat(',',brand_id) as brand
from(
    select
        distinct user_id,brand_id
    from
        t_alibaba_bigdata_user_brand_total_1
    where type='1' and visit_datetime like '08-%'
)a
group by user_id;

我们使用了visit_datetime like '08-%'来过滤属于八月份的数据，但是想要得到最后一个月的数据呢？最后两个月的数据呢？

看来日期转换是一个必须解决的问题了。

日期转换

感谢 @Adamus_7 网友提醒，对日期的分割可以直接用字符串比较，例如查询最后一个月的记录：

select * from t_alibaba_bigdata_user_brand_total_1 where visit_datetime > '07-15'

但是为了以后加入对日期的分析，例如加入时间衰减因子，还是需要计算日期间隔。

ODPS SQL 提供了非常丰富的日期转换的内建函数，基本满足了我们的需求。

bigint datediff(datetime date1, datetime date2, string datepart)

DATEDIFF 函数的用途是计算两个时间date1，date2在指定时间单位datepart的差值。因此我们只要传进8月16日作为date1，visit_datetime作为date2，datepart以天为单位，就能得到日期差值。但是visit_datetime是一个只包含月日的字符串，因此我们还需要进行字符串拼接（concat 函数）和格式转换（to_date 函数）。

以下是代码示例：

select visit_datetime,
        datediff(
            to_date('2013-08-16','yyyy-mm-dd'),
            to_date(concat('2013-',visit_datetime),'yyyy-mm-dd'),
            'dd') as days
from t_alibaba_bigdata_user_brand_total_1
where user_id = '2538398'

结果就是visit_datetime距离8月16日之间的天数。根据这个属性我们就可以进行分割训练集。我们将前三个月的数据作为训练集，最后一个月的购买记录作为验证集。

我们可以创建训练集如下：

create table train_set as
select *
from(
    select * ,datediff(
            to_date('2013-08-16','yyyy-mm-dd'),
            to_date(concat('2013-',visit_datetime),'yyyy-mm-dd'),
            'dd') as days
    from t_alibaba_bigdata_user_brand_total_1
)a
where days > 30;

验证集如下：

create table validate_set as
select
    user_id,
    wm_concat(',',brand_id) as brand
from(
    select distinct user_id,brand_id
    from(
        select *,datediff(
            to_date('2013-08-16','yyyy-mm-dd'),
            to_date(concat('2013-',visit_datetime),'yyyy-mm-dd'),
            'dd') as days
        from t_alibaba_bigdata_user_brand_total_1
        where type = '1'
    )a
where days <= 30
)b
group by user_id;

这样训练集中会多一个字段days距离天数了，有了这个字段就可以搞很多有意思的模型了。现在本地的离线评估模型就差个评估算法了，而计算召回率、准确率的前提是计算我们推荐集的 pair 数。

计算推荐集、验证集条数

当然你可以将计算推荐集的最外层查询改成 count(*) ，就能计算出 pair 数。但是这样显然太浪费计算资源了，而且不够通用，以后融合模型、用 MR 怎么办？

我们发现文档里有个REGEXP_COUNT函数。

bigint regexp_count(string source, string pattern[, bigint start_position])

用途：计算 source 中从 start_position 开始，匹配指定模式 pattern 的子串的次数。

例如：

regexp_count('abababc', 'a.c') = 1

所以我们可以这样计算：

select sum(regexp_count(brand,',')+1) from t_tmall_add_user_brand_predict_dh;

就是计算brand中逗号的个数在加一（好弱的正则啊… 复杂正则实在不会写…）。同样的方法，也适用于验证集，计算出验证集validate_set有 254 多万条。

接下来计算推荐集和验证集中重复的个数就能算出F1值了。这个估计要用到 UDF 或者 M/R 了，下次再给上吧。

几点建议

觉得几个可能会比较有用的内建函数，比如substr、split_part，regexp_extract等等。

本文没有什么高深的东西，小弟在这只是抛砖引玉，相信认真看过文档的同学，对上面的东西肯定了然于心。对于没空看文档的同学，希望通过这篇文章能让您重拾文档。另外小弟提点个人的建议：

文档很重要，真的。熟读文档至少一遍，绝对比在那瞎猜函数的用处要好。
先将 SQL 研究透了，基本一些简单的模型规则都能用 SQL 实现。能用 SQL 的就用 SQL。
不能用 SQL 的再使用 MR 。
注重团队分工，不要一股脑儿都去学 sql ，一人主学 sql 一人主学 mr 一人搞模型/算法，注重组内组间多经验分享和交流。

这样学习曲线可能会平缓些。希望对怕第一个月就淘汰的队伍有点帮助吧。

本文匆忙之中赶完，周末还要赶项目（QAQ），文中纰漏之处还望指出~

-EOF-