簡(jiǎn)介： MaxCompute 挑戰(zhàn)使用SQL進(jìn)行序列數(shù)據(jù)處理 --而不是用MR和函數(shù)

日常編寫數(shù)據(jù)加工任務(wù)，主要的方法就是使用SQL。第一是因?yàn)樽约簩?duì)SQL掌握的比較好（十多年數(shù)據(jù)開(kāi)發(fā)經(jīng)驗(yàn)，就這幾個(gè)關(guān)鍵字，也不敢跟別人說(shuō)自己不行），所以，MR和函數(shù)涉及不多。在接觸MaxCompute這些年，寫過(guò)的函數(shù)應(yīng)該不超過(guò)10個(gè)，主要還是因?yàn)樽约篔AVA水平挫。記得早些年寫過(guò)一個(gè)身份證號(hào)碼校驗(yàn)函數(shù)，當(dāng)時(shí)有個(gè)項(xiàng)目反饋一段SQL原來(lái)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)化過(guò)的方法，自己用MR和自定義函數(shù)性能是很難保障的。這也導(dǎo)致我至今在這方面也是渣渣，當(dāng)然我認(rèn)為錯(cuò)不在我，我只是聽(tīng)了“媽媽”的話而已。

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

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

問(wèn)題模擬如下：

問(wèn)題：吃了幾次飯，都吃了多久？

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

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

通過(guò)上面的分析，我們可以得到結(jié)果：大約吃了四次飯，因?yàn)橥砩铣燥埖臅r(shí)間很長(zhǎng)，按照規(guī)則算作吃了兩次飯（第四次看起來(lái)是去擼串了）。我是怎么做的呢？第一步，我先把無(wú)關(guān)的信息剔除了，第1行、第4行、最后1行。第二步，后我利用數(shù)據(jù)是連續(xù)的時(shí)間的特質(zhì)，找到了狀態(tài)的結(jié)束時(shí)間。第三步，我識(shí)別了狀態(tài)間隔1小時(shí)這個(gè)特征，識(shí)別出了一個(gè)“吃飯”中混雜的其他無(wú)關(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è)問(wèn)題很頭疼，函數(shù)需要指定偏移量，而這個(gè)問(wèn)題里面并不知道到底會(huì)出現(xiàn)多少個(gè)狀態(tài)。是不是也沒(méi)有用呢？看看再說(shuō)。

問(wèn)題分解分解如下：

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

?

問(wèn)題再次分解分解如下：

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

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

2-下個(gè)時(shí)點(diǎn)的“開(kāi)始時(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)在問(wèn)題解決了。我們通過(guò)這個(gè)表格回答了最開(kāi)始題目的問(wèn)題。這個(gè)人吃過(guò)4次飯，開(kāi)始時(shí)間分別是7點(diǎn)10分、12點(diǎn)25分、17點(diǎn)40分、19點(diǎn)45分，每次持續(xù)的時(shí)間大約都在1小時(shí)。這個(gè)過(guò)程就是一個(gè)找到需要的信息，剔除無(wú)關(guān)信息的過(guò)程，只不過(guò)這個(gè)where有點(diǎn)復(fù)雜。

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

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

SQL問(wèn)題解答：

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

;

原文鏈接
本文為阿里云原創(chuàng)內(nèi)容，未經(jīng)允許不得轉(zhuǎn)載。

總結(jié)

以上是生活随笔為你收集整理的MaxCompute 挑战使用SQL进行序列数据处理的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。