简介：MaxCompute 挑战应用 SQL 进行序列数据处理 – 而不是用 MR 和函数
日常编写数据加工工作，次要的办法就是应用 SQL。第一是因为本人对 SQL 把握的比拟好（十多年数据开发教训，就这几个关键字，也不敢跟他人说本人不行），所以，MR 和函数波及不多。在接触 MaxCompute 这些年，写过的函数应该不超过 10 个，次要还是因为本人 JAVA 程度挫。记得早些年写过一个身份证号码校验函数，过后有个我的项目反馈一段 SQL 原来 2 分钟，应用我的函数就变成 12 分钟了。过后这个项目组还找到 MaxCompute 的研发，研发负责人又找到我，让我把我的代码调优下。我很惶恐啊，我是什么渣，我本人心里晓得啊。最初还是厚着脸皮求研发帮我优化了下，性能终于改良了。这当前，我更不敢随机作函数了，毕竟 MaxCompute 官网倡议尽可能应用 SQL，SQL 是优化过的办法，本人用 MR 和自定义函数性能是很难保障的。这也导致我至今在这方面也是渣渣，当然我认为错不在我，我只是听了“妈妈”的话而已。
最近很微妙，接连有两个我的项目遇到了序列值计算的问题，还都是要求不能应用函数和 MR。共事把问题送给我，我发现光读懂题都要半天（题目有点绕），不在一线搞开发太久了，有点陌生了。同样的问题，第一次搞了一天，第二次还搞了半天，没说很快能搞进去的，未免有点丢范。所以，总结进去跟大家分享下。
先说下什么是序列值的解决。表中的记录自身是无序的，然而业务上数据都是有序的，一般来说工夫就是一个天然的序列。比方利用我一天的作息的时点记录，计算我一天吃了几次饭，吃了多久。乍一看，如同要写个函数。
问题模仿如下：
问题：吃了几次饭，都吃了多久？
条件：1- 两个“吃饭”状态距离在 1 小时内，算作一顿饭
2- 最初一个“吃饭”状态后的下一个其余状态的开始工夫，是“吃饭”的完结工夫
通过下面的剖析，咱们能够失去后果：大概吃了四次饭，因为早晨吃饭的工夫很长，依照规定算作吃了两次饭（第四次看起来是去撸串了）。我是怎么做的呢？第一步，我先把无关的信息剔除了，第 1 行、第 4 行、最初 1 行。第二步，后我利用数据是间断的工夫的特质，找到了状态的完结工夫。第三步，我辨认了状态距离 1 小时这个特色，辨认出了一个“吃饭”中混淆的其余无关状态，并且还剖析失去第三个“吃饭”和第四个“吃饭”状态是两个独立的状态。
那么用 SQL 怎么实现？排序是肯定的了，要排序还要解决状态，必须应用窗口函数。能选的窗口函数仿佛只有 lag、lead。
窗口函数：
LAG 按偏移量取以后行之前第几行的值。
LEAD 按偏移量取以后行之后第几行的值。
官网文档：https://help.aliyun.com/docum…
即使有了这个函数，还有一个问题很头疼，函数须要指定偏移量，而这个问题外面并不知道到底会呈现多少个状态。是不是也没有用呢？看看再说。
问题合成合成如下：
应用 LAG\LEAD 函数取到前一条记录和后一条记录的状态和工夫，剖析记录：
1- 以后状态不是“吃饭”，上一个状态也不是“吃饭”，记录不保留。
2- 以后状态不是“吃饭”，上一个状态是“吃饭”，为上一个状态提供完结工夫，记录不保留。
3- 以后状态是“吃饭”，记录上一个和下一个状态都是“吃饭”，记录不保留。
4- 以后状态是“吃饭”，记录下一个状态工夫，作为以后状态完结工夫，记录保留。
如下图：
而后咱们就失去了上面一个表格：
很显著，这不是咱们最初须要的。尽管咱们找到了状态为“吃饭”的行，并且通过窗口函数给它找到了状态的完结理论。然而表格还须要再作一次解决，能力变成咱们想要的后果。再次应用 LAG\LEAD 函数，咱们须要把距离在 1 小时内的“吃饭”状态进行合并。
问题再次合成合成如下：
应用 LAG\LEAD 函数取到前一条记录和后一条记录的开始和完结工夫，剖析记录：
1- 以后记录的“开始工夫”减去上个时点的“完结工夫”，如果小于 1 小时，该行记录不保留。这一行记录的状态须要与上一行合并为一次“吃饭”状态。下图中绿色标注行。
2- 下个时点的“开始工夫”减去以后记录的“完结工夫”，如果小于 1 小时，该行记录与下一行记录合并。批改以后时点“吃饭”状态的完结工夫为下一个时点的完结工夫。下图橙色标注行。
而后咱们失去了上面的表格：
不论之前咱们想的多简单，须要用什么循环或者递归逻辑实现，然而当初问题解决了。咱们通过这个表格答复了最开始题目的问题。这个人吃过 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
;原文链接
本文为阿里云原创内容，未经容许不得转载。