目录

0 需求分析

1 数据准备

2 数据分析

3 小结

---

0 需求分析

如下为电商公司用户访问时间数据

id	ts
1001	17523641234
1001	17523641256
1002	17523641278
1001	17523641334
1002	17523641434
1001	17523641534
1001	17523641544
1002	17523641634
1001	17523641638
1001	17523641654

某个用户相邻的访问记录如果时间间隔小于 60 秒，则分为同一个组，结果为：

id	ts	groupid
1001	17523641234	1
1001	17523641256	1
1001	17523641334	2
1001	17523641534	3
1001	17523641544	3
1001	17523641638	4
1001	17523641654	4
1002	17523641278	1
1002	17523641434	2
1002	17523641634	3

 1 数据准备

（1）数据

vim visit.txt

1001	17523641234
1001	17523641256
1002	17523641278
1001	17523641334
1002	17523641434
1001	17523641534
1001	17523641544
1002	17523641634
1001	17523641638
1001	17523641654

(2)建表

create table if not exists visit(
 
id string,
ts string
 
)
 
row format delimited
fields terminated by '\t'
 
;

(3)加载数据

load data local inpath "/home/centos/dan_test/visit.txt" into table visit;

(4) 查询数据

hive> select * from visit;
OK
1001	17523641234
1001	17523641256
1002	17523641278
1001	17523641334
1002	17523641434
1001	17523641534
1001	17523641544
1002	17523641634
1001	17523641638
1001	17523641654
Time taken: 1.77 seconds, Fetched: 10 row(s)

2 数据分析

目标：某个用户相邻记录如果时间间隔小于 60 秒，则分为同一个组。

如果不用sql,正常的思路就是按ts排序，将相邻的两天记录先相减得到的差值先进行观察找规律

用SQL实现上述思路，具体SQL如下：

select
    id,
    ts,
    ts-lag(ts,1,0) over(partition by id order by ts)
from visit

计算结果如下：

--------------------------------------------------------------------------------
OK
1001	17523641234	1.7523641234E10
1001	17523641256	22.0
1001	17523641334	78.0
1001	17523641534	200.0
1001	17523641544	10.0
1001	17523641638	94.0
1001	17523641654	16.0
1002	17523641278	1.7523641278E10
1002	17523641434	156.0
1002	17523641634	200.0
Time taken: 10.196 seconds, Fetched: 10 row(s)

我们按照上述的中间结果手动按照题意进行分组，要求的是相邻时间间隔小于60的被分成一组，手动分组如下：

 我们在手动进行分组的过程中，发现这样一个规律当遇到时间间隔大于60的数据进行加一操作，也就是我们通常所说的累加器思想，类似于流式数据（按时间序列进行排序后）进入累加器中当满足某种条件后（或发生了某种变化后）计数器就加1，这样就把连续的时序数据就区分开了，因为我需要的是把每一次变化都分别放在一个组里，不变的放一个组里，我需要的是观察截止当前发生变化了的次数，那么计数器里面保留的就是截止当前发生变化的次数（可以理解为截止当前在线人数），如果按照这种思想去分组，那么中间没变化的会发生持续一段时间，如果有变化，会显示新增人数，这样不变的数据就被区分出来。

根据上述分析，我们知道累加的实现在SQL中就是sum() over(),于是我们最终的SQL如下：

select id,
       ts,
       sum(if(ts_diff>=60,1,0)) over(partition by id order by ts) as groupid
from(
select
    id,
    ts,
    ts-lag(ts,1,0) over(partition by id order by ts) as ts_diff
from visit
)t

计算结果如下：

--------------------------------------------------------------------------------
OK
1001	17523641234	1
1001	17523641256	1
1001	17523641334	2
1001	17523641534	3
1001	17523641544	3
1001	17523641638	4
1001	17523641654	4
1002	17523641278	1
1002	17523641434	2
1002	17523641634	3
Time taken: 23.026 seconds, Fetched: 10 row(s)

3 小结

本文总结了一种重新分组的方法，从不同的角度来认识该算法，该方法实际上在业务中经常被用到，之前我们讲到的分桶重排序算法等实际上和这个是类似的，只不过本文从不同的角度来解释该算法，让读者能够更深刻的认识该算法，从而更好的应用实践中，解决实际问题。