MaxCompute 挑战使用SQL进行序列数据处理-阿里云开发者社区

开发者社区> 阿里巴巴大数据计算> 正文
登录阅读全文

MaxCompute 挑战使用SQL进行序列数据处理

简介: MaxCompute 挑战使用SQL进行序列数据处理 --而不是用MR和函数

日常编写数据加工任务,主要的方法就是使用SQL。第一是因为自己对SQL掌握的比较好(十多年数据开发经验,就这几个关键字,也不敢跟别人说自己不行),所以,MR和函数涉及不多。在接触MaxCompute这些年,写过的函数应该不超过10个,主要还是因为自己JAVA水平挫。记得早些年写过一个身份证号码校验函数,当时有个项目反馈一段SQL原来2分钟,使用我的函数就变成12分钟了。当时这个项目组还找到MaxCompute的研发,研发负责人又找到我,让我把我的代码调优下。我很惶恐啊,我是什么渣,我自己心里知道啊。最后还是厚着脸皮求研发帮我优化了下,性能终于改进了。这以后,我更不敢随机作函数了,毕竟MaxCompute官方建议尽可能使用SQL,SQL是优化过的方法,自己用MR和自定义函数性能是很难保障的。这也导致我至今在这方面也是渣渣,当然我认为错不在我,我只是听了“妈妈”的话而已。

最近很奇妙,接连有两个项目遇到了序列值计算的问题,还都是要求不能使用函数和MR。同事把问题送给我,我发现光读懂题都要半天(题目有点绕),不在一线搞开发太久了,有点生疏了。同样的问题,第一次搞了一天,第二次还搞了半天,没说很快能搞出来的,未免有点丢范。所以,总结出来跟大家分享下。

先说下什么是序列值的处理。表中的记录本身是无序的,但是业务上数据都是有序的,一般来说时间就是一个自然的序列。比如利用我一天的作息的时点记录,计算我一天吃了几次饭,吃了多久。乍一看,好像要写个函数。

问题模拟如下:

问题:吃了几次饭,都吃了多久?

条件:1-两个吃饭状态间隔在1小时内,算作一顿饭

2-最后一个吃饭状态后的下一个其他状态的开始时间,是吃饭的结束时间

image.png

通过上面的分析,我们可以得到结果:大约吃了四次饭,因为晚上吃饭的时间很长,按照规则算作吃了两次饭(第四次看起来是去撸串了)。我是怎么做的呢?第一步,我先把无关的信息剔除了,第1行、第4行、最后1行。第二步,后我利用数据是连续的时间的特质,找到了状态的结束时间。第三步,我识别了状态间隔1小时这个特征,识别出了一个“吃饭”中混杂的其他无关状态,并且还分析得到第三个“吃饭”和第四个“吃饭”状态是两个独立的状态。

那么用SQL怎么实现?排序是一定的了,要排序还要处理状态,必须使用窗口函数。能选的窗口函数似乎只有lag、lead。

窗口函数:

LAG  按偏移量取当前行之前第几行的值。

LEAD 按偏移量取当前行之后第几行的值。

官方文档:https://help.aliyun.com/document_detail/34994.html

 

即便有了这个函数,还有一个问题很头疼,函数需要指定偏移量,而这个问题里面并不知道到底会出现多少个状态。是不是也没有用呢?看看再说。

问题分解分解如下:

使用LAG\LEAD函数取到前一条记录和后一条记录的状态和时间,分析记录:

1-当前状态不是“吃饭”,上一个状态也不是“吃饭”,记录不保留。

2-当前状态不是“吃饭”,上一个状态是“吃饭”,为上一个状态提供结束时间,记录不保留。

3-当前状态是“吃饭”,记录上一个和下一个状态都是“吃饭”,记录不保留。

4-当前状态是“吃饭”,记录下一个状态时间,作为当前状态结束时间,记录保留。

如下图:

image.png

然后我们就得到了下面一个表格:

image.png

很明显,这不是我们最后需要的。虽然我们找到了状态为“吃饭”的行,并且通过窗口函数给它找到了状态的结束实际。但是表格还需要再作一次处理,才能变成我们想要的结果。再次使用LAG\LEAD函数,我们需要把间隔在1小时内的“吃饭”状态进行合并。

 

问题再次分解分解如下:

使用LAG\LEAD函数取到前一条记录和后一条记录的开始和结束时间,分析记录:

1-当前记录的“开始时间”减去上个时点的“结束时间”,如果小于1小时,该行记录不保留。这一行记录的状态需要与上一行合并为一次“吃饭”状态。下图中绿色标注行。

2-下个时点的“开始时间”减去当前记录的“结束时间”,如果小于1小时,该行记录与下一行记录合并。修改当前时点“吃饭”状态的结束时间为下一个时点的结束时间。下图橙色标注行。

image.png

然后我们得到了下面的表格:

image.png

不管之前我们想的多复杂,需要用什么循环或者递归逻辑实现,但是现在问题解决了。我们通过这个表格回答了最开始题目的问题。这个人吃过4次饭,开始时间分别是7点10分、12点25分、17点40分、19点45分,每次持续的时间大约都在1小时。这个过程就是一个找到需要的信息,剔除无关信息的过程,只不过这个where有点复杂。

其实从分析问题的角度来看,这个问题本身就有点复杂,搞懂问题一般都需要一定的时间。从实现问题的角度来看,使用高级语言JAVA或者python实现更容易点,循环撸一遍有什么解决不了的(一遍不够再来一遍)。用SQL实现,看起来有点复杂(可能是我常年使用SQL语言的原因,我觉得我好像分析问题的过程跟实现的过程是一样的。),但是代码量一定是最少的(性能可能也是最佳的)。再从可维护性上去综合比较,还是使用SQL实现更优。

所以,后面再遇到类似的问题,你应该可以搞定了。如果有点困难,至少你可以再回过头来看下这个例子,毕竟我花了好久来设计。


SQL问题解答:

with ta as(

select*

from values

(1001,'06:05:00','sleep')

,(1001,'07:10:00','eat')

,(1001,'08:15:00','phone')

,(1001,'11:20:00','phone')

,(1001,'12:25:00','eat')

,(1001,'12:40:00','phone')

,(1001,'13:30:00','eat')

,(1001,'13:35:00','sleep')

,(1001,'17:40:00','eat')

,(1001,'18:05:00','eat')

,(1001,'18:25:00','eat')

,(1001,'18:30:00','phone')

,(1001,'19:45:00','eat')

,(1001,'20:55:00','phone')

,(1001,'22:00:00','sleep')

t(id,stime,stat))

-- 5 计算根据前后记录的时间,判断记录是否要被合并

selectid,stime

,case whens2<=60 thenetime2 else etime end asetime,stat

from(

-- 4 计算前后记录的时间差

selectid,stime,etime,stat

,datediff(stime,etime1,'mi') ass1

,datediff(stime2,etime,'mi') ass2

,etime2

from(

-- 3 计算前后记录的时间

selectid,stime,etime,stat

,lag (stime,1) over(partition byid order by stime asc)as stime1

,lag (etime,1) over(partition byid order by stime asc)as etime1

,lead(stime,1) over(partition byid order by stime asc)as stime2​​​

,lead(etime,1) over(partition byid order by stime asc)as etime2

from(

-- 2 识别前后记录状态,找到状态结束时间

selectid,stime,stat

,lead(stime,1) over(partition byid order by stime asc)as etime

,lag (stat,1) over(partition byid order by stime asc)as stat1

,lead(stat,1) over(partition byid order by stime asc)as stat2

from(

-- 1 把字符串转时间

selectid,to_date(concat('2021-06-29 ',stime),'yyyy-mm-dd hh:mi:ss') asstime,stat

fromta)t1)t2

wherestat='eat' and not(stat='eat' andstat1='eat' andstat2='eat'))t3)t4

wheres1 >60 ors1 is null

;


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里巴巴大数据计算
使用钉钉扫一扫加入圈子
+ 订阅

阿里大数据官方技术圈

官方博客
最新文章
相关文章
链接