Hive实战案例(二)：自定义函数实现根据某一字段内的数值对数据再分割
题目描述
一：每个id浏览时长、步长
二：如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏览时长、步长
数据内容
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:02 https://www.lagou.com/jobs/9590606.html?show=IEEE1FIJ3106A1H062HA
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:23 https://www.lagou.com/jobs/998375.html?show=EC1JGEC8G3HJC82JIHCD
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:09 https://www.lagou.com/jobs/8205098.html?show=G75J62JE63JE3678G98F
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:12 https://www.lagou.com/jobs/2280203.html?show=1957CGIA1702C1J9F0GH
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:31 https://www.lagou.com/jobs/5921958.html?show=BJ9CJJ6F0GH0CDGGHCCB
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:34 https://www.lagou.com/jobs/2569616.html?show=G5472AH6G1I61CGF9HGC
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:46 https://www.lagou.com/jobs/3892054.html?show=E771D8I4JJ0DE4DF575C
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:13 https://www.lagou.com/jobs/9559088.html?show=3EG4D1108IC3B446G2EB
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:14 https://www.lagou.com/jobs/3381768.html?show=99B480535EC2FA31DJ92
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:11 https://www.lagou.com/jobs/5100510.html?show=JGH3HJ36D7GHIEHEEFI6
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:20 https://www.lagou.com/jobs/2814357.html?show=6A6799246J9J4B6IC9HI
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:25 https://www.lagou.com/jobs/2428943.html?show=697DI68E5F133A1DD96D
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:41 https://www.lagou.com/jobs/2790534.html?show=1C72FF96F549G4A458BI
32258fe7130844399859aec54b6df5ff 2020/05/28 03:47 https://www.lagou.com/jobs/4319618.html?show=A9IB685E7CJ9DIAB2244
32258fe7130844399859aec54b6df5ff 2020/05/28 03:33 https://www.lagou.com/jobs/1944013.html?show=A70H86DF1EHG2E57H1HE
32258fe7130844399859aec54b6df5ff 2020/05/28 03:21 https://www.lagou.com/jobs/1013342.html?show=366DJ2870404637EC19D
32258fe7130844399859aec54b6df5ff 2020/05/28 03:54 https://www.lagou.com/jobs/4952649.html?show=DGCC1FH06B69I9B1GA08
32258fe7130844399859aec54b6df5ff 2020/05/28 03:48 https://www.lagou.com/jobs/4427940.html?show=JAF2067192A1H53IJ00G
32258fe7130844399859aec54b6df5ff 2020/05/28 03:08 https://www.lagou.com/jobs/231554.html?show=I1J8G8075B7G5IDA326C
32258fe7130844399859aec54b6df5ff 2020/05/28 05:09 https://www.lagou.com/jobs/4799769.html?show=J7BGJ4B50GFHG4FEJCB6
32258fe7130844399859aec54b6df5ff 2020/05/28 05:26 https://www.lagou.com/jobs/7373006.html?show=6J9JJ89EADI7DI0H82C3
32258fe7130844399859aec54b6df5ff 2020/05/28 05:11 https://www.lagou.com/jobs/5766122.html?show=6J224ECEABC7C9I62763
32258fe7130844399859aec54b6df5ff 2020/05/28 05:34 https://www.lagou.com/jobs/2962929.html?show=GH06BC9D6I2G7H3D79B8
32258fe7130844399859aec54b6df5ff 2020/05/28 05:18 https://www.lagou.com/jobs/5653876.html?show=H426J08J6H4JJB74HFJE
32258fe7130844399859aec54b6df5ff 2020/05/28 05:50 https://www.lagou.com/jobs/7040422.html?show=0C78E264AHEADEJ26643
32258fe7130844399859aec54b6df5ff 2020/05/28 05:45 https://www.lagou.com/jobs/2961967.html?show=A4702EJ6E5DJIA475AF1
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:04 https://www.lagou.com/jobs/5552238.html?show=3I84DE05EH1AB6D13B3G
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:21 https://www.lagou.com/jobs/1558623.html?show=CC6C7J0G326G2BJ3D179
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:48 https://www.lagou.com/jobs/9974358.html?show=7HJ4BIAGHD73F49G9JJC
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:56 https://www.lagou.com/jobs/2628314.html?show=H1110A1AA14H64DA876C
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:50 https://www.lagou.com/jobs/6317002.html?show=664G909C9EG7JC63IB7D
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:53 https://www.lagou.com/jobs/1925810.html?show=FGEC1A7I60JJDAJGAF1A
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:00 https://www.lagou.com/jobs/5946589.html?show=240EB7E488G6FH0G27JF
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:48 https://www.lagou.com/jobs/776158.html?show=03FI36B82792CEBJHI29
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:14 https://www.lagou.com/jobs/97519.html?show=JHF32AB5EH58HEC2F63G
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:50 https://www.lagou.com/jobs/5196791.html?show=HDCJCA8JE1BF1IFE6HF6
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:58 https://www.lagou.com/jobs/6289105.html?show=I8D80BJFC3F3FEGGHA5C
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:13 https://www.lagou.com/jobs/7901649.html?show=2GHE5B24F5ABC13I6EB4
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:39 https://www.lagou.com/jobs/3214603.html?show=F6G8632470DAE5E760BG
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:15 https://www.lagou.com/jobs/6981846.html?show=1E7F19G856JA9JD8AB9D
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:43 https://www.lagou.com/jobs/1141030.html?show=5E96FFJA82E1I2BF2FEE
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:14 https://www.lagou.com/jobs/8929830.html?show=89H155HCJ41H228010I5
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:36 https://www.lagou.com/jobs/2646629.html?show=B38GA2D1E10EBFE8F6F7
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:24 https://www.lagou.com/jobs/7111580.html?show=FC6FD5F45B12ABIF02GD
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:06 https://www.lagou.com/jobs/8038667.html?show=HG4HE7CGI00A7A1F2J5F
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:51 https://www.lagou.com/jobs/4024837.html?show=JB27071067EGBE8D060C
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:33 https://www.lagou.com/jobs/7463120.html?show=D42J0IC234DIA481EF82
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:09 https://www.lagou.com/jobs/8292709.html?show=H96I861CGIGIF571H2JJ
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:24 https://www.lagou.com/jobs/5115760.html?show=H93JAJFDJH19HEF1E918
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:47 https://www.lagou.com/jobs/4543947.html?show=FG0BGA0CFDF6270IJE32
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:17 https://www.lagou.com/jobs/2188473.html?show=80JBIA9GFAJ76FD980AE
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:46 https://www.lagou.com/jobs/9320424.html?show=1G50E0G0804JAJH2HBA1
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:00 https://www.lagou.com/jobs/8308905.html?show=21DGJA045F8E64JHA0D6
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:08 https://www.lagou.com/jobs/9159707.html?show=44EDBC5B43A444FH001C
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:10 https://www.lagou.com/jobs/9532255.html?show=A1I8GI28GF0B14E97D64
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:51 https://www.lagou.com/jobs/9785185.html?show=FIHCCA16AJDA32EC4332
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:56 https://www.lagou.com/jobs/1117353.html?show=78GH99D70424B013G303
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:20 https://www.lagou.com/jobs/1029027.html?show=F31BH181E6E8JJ4AD295
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:16 https://www.lagou.com/jobs/9539487.html?show=47G414184H33E14DH159
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:10 https://www.lagou.com/jobs/8051736.html?show=JJ189D6F4HD6F0E7A2AG
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:51 https://www.lagou.com/jobs/5261931.html?show=7DA832A31BI430197F48
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:32 https://www.lagou.com/jobs/7521003.html?show=ACDFI9730A2B646I0270
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:11 https://www.lagou.com/jobs/3408361.html?show=999AGGBH0DC2E35J097B
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:32 https://www.lagou.com/jobs/874257.html?show=58IF72BB8F74ID23GE87
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:37 https://www.lagou.com/jobs/3485939.html?show=460DIAJ135CC950J3C77
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:35 https://www.lagou.com/jobs/8439256.html?show=81II8DB2J2IF8AIFJ67F
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:22 https://www.lagou.com/jobs/673620.html?show=H02AJA95GBE98768ADHF
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:07 https://www.lagou.com/jobs/3039181.html?show=A4EDIFDEJB2J40I64F04
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:56 https://www.lagou.com/jobs/7363821.html?show=5I687EDH2C3A1JJAF57D
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:58 https://www.lagou.com/jobs/8879039.html?show=929I8CG2CDB9AE0268JI
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:27 https://www.lagou.com/jobs/2273737.html?show=D8C799HJ092G9I4230EH
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:17 https://www.lagou.com/jobs/6261949.html?show=876583DAG4FIEI637F6E
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:46 https://www.lagou.com/jobs/6987631.html?show=936BIDF2F4352A39H6FF
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:17 https://www.lagou.com/jobs/2379044.html?show=6J2A9DDHB4787CD90134
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:35 https://www.lagou.com/jobs/4658655.html?show=F87D3565JD253ED6FIHE
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:56 https://www.lagou.com/jobs/8550343.html?show=DB23C4598E005CGHH06D
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:53 https://www.lagou.com/jobs/166497.html?show=B9B3GFIB5EIC9E32J5IJ
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:16 https://www.lagou.com/jobs/3463570.html?show=20BBBA585JF22I953GBG
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:23 https://www.lagou.com/jobs/4105412.html?show=IAG945B35D5DA6F1E992
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:52 https://www.lagou.com/jobs/6541296.html?show=47BH0G2A7IGFHIH61A85
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:45 https://www.lagou.com/jobs/8701046.html?show=IDE79JG4DI0J6508F0HH
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:32 https://www.lagou.com/jobs/9080852.html?show=5JA75D16G22BE0H881G2
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:47 https://www.lagou.com/jobs/7148755.html?show=F58GI58H74989HD65173
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:46 https://www.lagou.com/jobs/4610986.html?show=G24C84DCG9FD5GFBFCEE
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:54 https://www.lagou.com/jobs/2566998.html?show=90BIIIIA346E50A5DA67
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:17 https://www.lagou.com/jobs/7418962.html?show=9F3AB45F1C3HAH58B8B8
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:16 https://www.lagou.com/jobs/1307719.html?show=1J85244F2F81JCBGHH9C
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:56 https://www.lagou.com/jobs/8686135.html?show=73E0E5J74EA8A5B8C0FD
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:14 https://www.lagou.com/jobs/81114.html?show=DCIJI9H51I5BHGC1587E
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:58 https://www.lagou.com/jobs/3454023.html?show=FI5EICD1F25F005J3CJG
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:13 https://www.lagou.com/jobs/1609611.html?show=87F3GAA5DH97H6G6I3J2
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:51 https://www.lagou.com/jobs/6306362.html?show=8775525F5EG213C94EE7
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:33 https://www.lagou.com/jobs/9309683.html?show=3HHEBFA7BA8J8BH3GGIG
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:37 https://www.lagou.com/jobs/3769247.html?show=BD7FE2HE8AED2F5J6818
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:56 https://www.lagou.com/jobs/2542380.html?show=DE8IH40GG47E096E0BE5
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:38 https://www.lagou.com/jobs/7732574.html?show=24AEBB54FA71D9F7JIDA
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:05 https://www.lagou.com/jobs/2349225.html?show=5JI210IFIJ0I707G4II7
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:39 https://www.lagou.com/jobs/2872366.html?show=9BCGBGHBE73IG5AF4569
准备工作
– 建表语句
create table t3(
id string,
dt string,
browseid string
)row format delimited fields terminated by ‘\t’;
– 导入数据
load data local inpath “/root/data/t3.dat” into table t3;
计算规则说明
id dt browseid
id：唯一的用户id
dt：用户在这个时间点点击进入了一个页面
browseid：用户浏览了哪个页面
简化数据(以下为某个用户，在一天内的浏览记录)：
1 08:20 1.html
1 08:23 2.html
1 08:24 3.html
1 08:40 4.html
1 09:33 5.html
1 09:40 6.html
1 09:30 7.html
1 09:36 8.html
1 09:37 9.html
1 09:41 a.html
一、每个id浏览时长、步长
用户1的浏览时长 = 09:41 - 08:20 = 81分钟
用户1的浏览步长 = count数 = 10次
二、如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏览时长、步长
用户1在 8:40 - 09:30 的间隔超过了30。生产中认为：
用户1在 08:20 - 08:40 浏览一次网站。这次浏览时长为20分钟，步长为4
用户1在 09:30 - 09:41 又浏览一次网站。这次浏览时长为11分钟，步长为6
示例输出
对于测试数据SQL1的结果：
934e8bee978a42c7a8dbb4cfa8af0b4f 104.0 13
对于测试数据SQL2的结果：
934e8bee978a42c7a8dbb4cfa8af0b4f 32.0 6
934e8bee978a42c7a8dbb4cfa8af0b4f 35.0 7
解题思路
一、每个id浏览时长、步长
首先分析题意，数据和实例输出可知，对于第一问，要求的即是每个id最后一条浏览记录的时间与第一条浏览记录的时间的差值，以及浏览的总条数count(*)。
浏览步长比较容易求出，只要利用id进行分组，然后利用聚合函数 count(*) 即可求出。
主要分析如何求浏览时长。这里容易联想到要使用序列函数中的first_value()和last_value()来求出按id字段分区后的第一条和最后一条记录。然后对这两个值进行相减，得到的结果对应每个id值都是相同的。这里在利用over()函数进行开窗时，利用rows between unbounded preceding and unbounded followin 来限定求值的范围为每个分区的第一条和最后一条数据之间，保证求到的是第一条和最后一条数据。
因此，利用该差值和id字段来进行group by分组，即可得到浏览时长，以及利用count(*)求出的浏览步长。这里要注意一点就是这个dt字段是string类型的，要计算相差的时长时，要首先利用unix_timestamp()将dt字段的值转换为时间戳格式进行相减，得到的结果单位是秒，所以还需要将结果/60，才能最后能到相差的分钟。
代码示例：
with tmp as (
select id, unix_timestamp(dt, "yyyy/MM/dd HH:mm") dt, browseid
from t3
)
select id, cast(diff / 60 as int) time, count(*) step
from
(select id, dt, browseid,
(last_value(dt) over(partition by id order by dt rows between unbounded preceding and unbounded following) -
first_value(dt) over(partition by id order by dt rows between unbounded preceding and unbounded following)) diff
from tmp
) tmp2
group by id, cast(diff / 60 as int)
order by time
二、如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏览时长、步长
首先分析题意和示例输出可知，对比问题一，发现问题二的需求是说当某个用户的浏览间隔时间超过了30分钟，则认为是一次独立的浏览记录。看示例输出的结果也知道，最后的分组应该是id字段，再加上一个设计的字段，才能把同一个id划分到不同的组里。
首先要判断两条记录的浏览时间间隔是否大于30分钟，因此就需要计算两条记录间 dt 字段的差值：
- 利用lag()函数，将dt字段下移一行。
- 然后将原id字段减去该列的值，得到每条记录中，时间与上一条记录时间的差值，同样需要利用问题一中的方法利用将差值转换为分钟来表示。
首先利用该思路得到的结果如下图所示：
以示例输出中的用户为例，最后一行即是某一数据行与其上一行数据在时间上相减后求得的差值，单位是分钟。
通过上述过程就可以看出来哪条记录的时间差值>30分钟。接下来，就是思考如何以该>30分钟的行对每一个用户记录进行再切分。比如这里的37分钟的记录，以该记录为切分，上面是一个分组，下面是一个分组，这样就可以得到满足题目要求的结果。
对于这个分析，自己也思考了很久，一个比较直观的逻辑其实是如果可以设计一个字段值，该字段值在每个用户中，以差值>30的记录为切分，标以不同的signal。这样，在每一块切分的数据块内部，该signal的值一样，且不同块之间的signal不同，这样就可以利用id字段和该signal一起作为分组字段，就可以实现题目要求。
那要怎么实现上述想法呢，尝试了很多Hive里的窗口函数，好像都没有办法实现该功能。于是最后，想到了是不是可以利用自定义函数的方式，自己定义函数来完成上述的逻辑呢？
于是，按照自定义函数的方式进行了操作。自定义函数的逻辑思路就是：定义一个int类型的类变量count，初始值设为0，然后在evaluate方法中，实现对参数的大小判断，即如果该参数的值>30，则count + 1，返回count。如果不大于，则直接返回count。其实就是在实现上面的思路。
自定义函数如下图所示：
public class segment extends UDF
{
private int count = 0;
public int evaluate(final Text x)
{
if (x == null || Integer.parseInt(x.toString()) < 30)
{
return count;
}
else{
count ++;
return count;
}
}
}
通过该函数，就相当于对表中的每条记录按照时间差值进行了划分，在同一划分块内的signal值是相同的。比如下图所示的结果，通过最后一列的内容就可以看到上述的逻辑过程。
可以看到，该signal的值对该用户的数据按差值为37的那一行进行了分割。实现了上述的设想。剩下的部分就比较简单了。
然后这里有个问题，就是此时按用户id和该signal划分组时，每个组里的第一个值，相当于都是差值>30那条记录的值(因为>30 count才加1，此时每组数据的值就跟第一条数据的值相等)，通过下图的倒数第二列可以看出。
因此分组后就不能只利用聚合函数sum()对倒数第三列(diff)的值进行相加，还需要减去每个分组中的第一个值。此时又利用了first_value函数，在over()中利用id和signal两个字段进行分组，获取每个分块的diff列的第一个数值，然后在group by的时候，利用sum(diff) - first_value来得到最终的结果。最终的结果如代码运行图中所示，可知，结果满足题意。
代码示例
with tmp as (
select id, dt, lag(dt) over(partition by id order by dt) lag_dt,
nvl(cast((unix_timestamp(dt, "yyyy/MM/dd HH:mm") - lag(unix_timestamp(dt, "yyyy/MM/dd HH:mm")) over(partition by id order by dt)) / 60 as int), 0) diff
from t3
)
select id, (sum(diff) - first_value) step, count(*) step
from
( select id, dt, lag_dt, diff, segment(diff) seg,
first_value(diff) over(partition by id, segment(diff) order by dt) first_value
from tmp
) tmp2
group by id, seg, first_value
3417
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
