Hive基于SQL创建漏斗模型

总结

为了突出重点，总结就写在最前面了。从拿到需求开始，我们经历了以下步骤来完成工作：

1. 需求分析
2. 设计测试数据集及测试用例
3. 数据清洗
4. 需要实现
5. 测试

其中数据清洗主要是做了两个工作：

• 去掉用户每一次访问中重复的页面记录，只保留每个页面的最后一次访问记录。
• 将用户访问记录进行合并，将所有访问过的页面和访问时间整合到1行当中。

实现的判断依据如下：

• 乱序漏斗：访问的页面数小于4，或者页面顺序不对。
• 顺序漏斗：访问的页面数等于4，且页面顺序严格对应。
• 顺序间隔漏斗：访问的页面数等于4，且页面顺序严格对应，且访问B页面的时间-访问A页面的时间超过2小时。

实现过程中主要使用了下面的函数：

• rank
• concat_ws
• collect_set
• split
• unix_timestamp
• 子查询

需求

概述

一般来说，客户会按照A->B->C->D的顺序来访问页面，而且越到后面的页面访问率就越低，比如A是网站首页，B是列表页，C是详情页，D是支付页，根据常识，很容易知道A页面的访问率是最高的而D则是最低的。

现在有一个需求：使用SQL语句来完成以下的工作：

• 找出乱序访问的用户访问记录，即不按照A->B->C->D的顺序访问的
• 找出顺序访问的用户访问记录，即严格按照A->B->C->D的顺序访问的
• 找出顺序的并符合间隔条件的用户访问记录，即严格按照A->B->C->D的顺序访问的，并且访问A后超过2小时再访问B的。

分析

这个需求表述的不是很清楚，我们再来明确一下各种情况应该怎么处理。

首先要完成这种数据漏斗，必须能够将一个用户的每次访问严格的界定开来，即有办法区分每个用户的每次访问都请求了哪些网址，这项工作显然不应该是我们这次的任务，故假定数据集符合该要求。

其次，还有一些特殊的情况的处理需要进一步明确：

• 如果一个用户在访问下一个页面之前多次访问上一个页面应该如何处理间隔？如A->A->B->C->D
• 如果一个用户按照顺序访问完后又重新访问某个页面应该如何处理？是算顺序还是逆序？如A->B->C->D->C

对于第1个问题，按照最后一次访问的时间为准；对于第2个问题，则约定其为乱序访问。

数据集及测试用例

我们并没有现成的数据集可以使用。为了说明问题，需要我们自己创建一个数据集用于测试。

表结构设计

为了说明问题方便，在不影响结论的情况下，应该让表结构尽可能的简单。必须的字段包括：

• session_id 用于界定每个用户的一次访问，在一次访问中的多个请求该字段相同
• url 请求链接地址
• req_time 访问页面的时间

测试数据

用等价类划分法来给一些测试数据。等价类大概划成下面这样：

基于这样的等价类划分，可以给出下面的测试数据集：

页面无缺失，顺序，超过间隔，无重复页面
session_id  req_url     req_time
s_01        a.html      2017-03-26 08:00:00
s_01        b.html      2017-03-26 10:01:00
s_01        c.html      2017-03-26 10:03:00
s_01        d.html      2017-03-26 10:04:00

页面无缺失，顺序，超过间隔，有重复页面
session_id  req_url     req_time
s_02        a.html      2017-03-26 08:00:00
s_02        a.html      2017-03-26 09:00:00
s_02        b.html      2017-03-26 11:01:00
s_02        c.html      2017-03-26 11:03:00
s_02        d.html      2017-03-26 11:04:00

页面无缺失，顺序，间隔不足，无重复页面
session_id  req_url     req_time
s_03        a.html      2017-03-26 08:00:00
s_03        b.html      2017-03-26 09:01:00
s_03        c.html      2017-03-26 09:03:00
s_03        d.html      2017-03-26 09:04:00

页面无缺失，顺序，间隔不足&#x