日常編寫數(shù)據(jù)加工任務(wù)，主要的方法就是使用SQL。第一是因?yàn)樽约簩?duì)SQL掌握的比較好（十多年數(shù)據(jù)開發(fā)經(jīng)驗(yàn)，就這幾個(gè)關(guān)鍵字，也不敢跟別人說自己不行），所以，MR和函數(shù)涉及不多。在接觸MaxCompute這些年，寫過的函數(shù)應(yīng)該不超過10個(gè)，主要還是因?yàn)樽约篔AVA水平挫。記得早些年寫過一個(gè)身份證號(hào)碼校驗(yàn)函數(shù)，當(dāng)時(shí)有個(gè)項(xiàng)目反饋一段SQL原來2分鐘，使用我的函數(shù)就變成12分鐘了。當(dāng)時(shí)這個(gè)項(xiàng)目組還找到MaxCompute的研發(fā)，研發(fā)負(fù)責(zé)人又找到我，讓我把我的代碼調(diào)優(yōu)下。我很惶恐啊，我是什么渣，我自己心里知道啊。最后還是厚著臉皮求研發(fā)幫我優(yōu)化了下，性能終于改進(jìn)了。這以后，我更不敢隨機(jī)作函數(shù)了，畢竟MaxCompute官方建議盡可能使用SQL，SQL是優(yōu)化過的方法，自己用MR和自定義函數(shù)性能是很難保障的。這也導(dǎo)致我至今在這方面也是渣渣，當(dāng)然我認(rèn)為錯(cuò)不在我，我只是聽了“媽媽”的話而已。

最近很奇妙，接連有兩個(gè)項(xiàng)目遇到了序列值計(jì)算的問題，還都是要求不能使用函數(shù)和MR。同事把問題送給我，我發(fā)現(xiàn)光讀懂題都要半天（題目有點(diǎn)繞），不在一線搞開發(fā)太久了，有點(diǎn)生疏了。同樣的問題，第一次搞了一天，第二次還搞了半天，沒說很快能搞出來的，未免有點(diǎn)丟范。所以，總結(jié)出來跟大家分享下。

先說下什么是序列值的處理。表中的記錄本身是無序的，但是業(yè)務(wù)上數(shù)據(jù)都是有序的，一般來說時(shí)間就是一個(gè)自然的序列。比如利用我一天的作息的時(shí)點(diǎn)記錄，計(jì)算我一天吃了幾次飯，吃了多久。乍一看，好像要寫個(gè)函數(shù)。

問題模擬如下：

問題：吃了幾次飯，都吃了多久？

條件：1-兩個(gè)“吃飯”狀態(tài)間隔在1小時(shí)內(nèi)，算作一頓飯

2-最后一個(gè)“吃飯”狀態(tài)后的下一個(gè)其他狀態(tài)的開始時(shí)間，是“吃飯”的結(jié)束時(shí)間

通過上面的分析，我們可以得到結(jié)果：大約吃了四次飯，因?yàn)橥砩铣燥埖臅r(shí)間很長(zhǎng)，按照規(guī)則算作吃了兩次飯（第四次看起來是去擼串了）。我是怎么做的呢？第一步，我先把無關(guān)的信息剔除了，第1行、第4行、最后1行。第二步，后我利用數(shù)據(jù)是連續(xù)的時(shí)間的特質(zhì)，找到了狀態(tài)的結(jié)束時(shí)間。第三步，我識(shí)別了狀態(tài)間隔1小時(shí)這個(gè)特征，識(shí)別出了一個(gè)“吃飯”中混雜的其他無關(guān)狀態(tài)，并且還分析得到第三個(gè)“吃飯”和第四個(gè)“吃飯”狀態(tài)是兩個(gè)獨(dú)立的狀態(tài)。

那么用SQL怎么實(shí)現(xiàn)？排序是一定的了，要排序還要處理狀態(tài)，必須使用窗口函數(shù)。能選的窗口函數(shù)似乎只有l(wèi)ag、lead。

窗口函數(shù)：

LAG 按偏移量取當(dāng)前行之前第幾行的值。

LEAD 按偏移量取當(dāng)前行之后第幾行的值。

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

即便有了這個(gè)函數(shù)，還有一個(gè)問題很頭疼，函數(shù)需要指定偏移量，而這個(gè)問題里面并不知道到底會(huì)出現(xiàn)多少個(gè)狀態(tài)。是不是也沒有用呢？看看再說。

問題分解分解如下：

使用LAG\LEAD函數(shù)取到前一條記錄和后一條記錄的狀態(tài)和時(shí)間，分析記錄：

1-當(dāng)前狀態(tài)不是“吃飯”，上一個(gè)狀態(tài)也不是“吃飯”，記錄不保留。

2-當(dāng)前狀態(tài)不是“吃飯”，上一個(gè)狀態(tài)是“吃飯”，為上一個(gè)狀態(tài)提供結(jié)束時(shí)間，記錄不保留。

3-當(dāng)前狀態(tài)是“吃飯”，記錄上一個(gè)和下一個(gè)狀態(tài)都是“吃飯”，記錄不保留。

4-當(dāng)前狀態(tài)是“吃飯”，記錄下一個(gè)狀態(tài)時(shí)間，作為當(dāng)前狀態(tài)結(jié)束時(shí)間，記錄保留。

如下圖：

然后我們就得到了下面一個(gè)表格：

很明顯，這不是我們最后需要的。雖然我們找到了狀態(tài)為“吃飯”的行，并且通過窗口函數(shù)給它找到了狀態(tài)的結(jié)束實(shí)際。但是表格還需要再作一次處理，才能變成我們想要的結(jié)果。再次使用LAG\LEAD函數(shù)，我們需要把間隔在1小時(shí)內(nèi)的“吃飯”狀態(tài)進(jìn)行合并。

問題再次分解分解如下：

使用LAG\LEAD函數(shù)取到前一條記錄和后一條記錄的開始和結(jié)束時(shí)間，分析記錄：

1-當(dāng)前記錄的“開始時(shí)間”減去上個(gè)時(shí)點(diǎn)的“結(jié)束時(shí)間”，如果小于1小時(shí)，該行記錄不保留。這一行記錄的狀態(tài)需要與上一行合并為一次“吃飯”狀態(tài)。下圖中綠色標(biāo)注行。

2-下個(gè)時(shí)點(diǎn)的“開始時(shí)間”減去當(dāng)前記錄的“結(jié)束時(shí)間”，如果小于1小時(shí)，該行記錄與下一行記錄合并。修改當(dāng)前時(shí)點(diǎn)“吃飯”狀態(tài)的結(jié)束時(shí)間為下一個(gè)時(shí)點(diǎn)的結(jié)束時(shí)間。下圖橙色標(biāo)注行。

然后我們得到了下面的表格：

不管之前我們想的多復(fù)雜，需要用什么循環(huán)或者遞歸邏輯實(shí)現(xiàn)，但是現(xiàn)在問題解決了。我們通過這個(gè)表格回答了最開始題目的問題。這個(gè)人吃過4次飯，開始時(shí)間分別是7點(diǎn)10分、12點(diǎn)25分、17點(diǎn)40分、19點(diǎn)45分，每次持續(xù)的時(shí)間大約都在1小時(shí)。這個(gè)過程就是一個(gè)找到需要的信息，剔除無關(guān)信息的過程，只不過這個(gè)where有點(diǎn)復(fù)雜。

其實(shí)從分析問題的角度來看，這個(gè)問題本身就有點(diǎn)復(fù)雜，搞懂問題一般都需要一定的時(shí)間。從實(shí)現(xiàn)問題的角度來看，使用高級(jí)語言JAVA或者python實(shí)現(xiàn)更容易點(diǎn)，循環(huán)擼一遍有什么解決不了的（一遍不夠再來一遍）。用SQL實(shí)現(xiàn)，看起來有點(diǎn)復(fù)雜（可能是我常年使用SQL語言的原因，我覺得我好像分析問題的過程跟實(shí)現(xiàn)的過程是一樣的。），但是代碼量一定是最少的（性能可能也是最佳的）。再?gòu)目删S護(hù)性上去綜合比較，還是使用SQL實(shí)現(xiàn)更優(yōu)。

所以，后面再遇到類似的問題，你應(yīng)該可以搞定了。如果有點(diǎn)困難，至少你可以再回過頭來看下這個(gè)例子，畢竟我花了好久來設(shè)計(jì)。

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 計(jì)算根據(jù)前后記錄的時(shí)間，判斷記錄是否要被合并
selectid,stime
,case whens2<=60 thenetime2 else etime end asetime,stat
from(
-- 4 計(jì)算前后記錄的時(shí)間差
selectid,stime,etime,stat
,datediff(stime,etime1,'mi') ass1
,datediff(stime2,etime,'mi') ass2
,etime2
from(
-- 3 計(jì)算前后記錄的時(shí)間
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 識(shí)別前后記錄狀態(tài)，找到狀態(tài)結(jié)束時(shí)間
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 把字符串轉(zhuǎn)時(shí)間
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
;