常見(jiàn)場(chǎng)景

     經(jīng)常會(huì)遇到一些分析連續(xù)次數(shù)類的需求，會(huì)需要對(duì)數(shù)據(jù)判斷是否連續(xù)，比如“用戶是否連續(xù)登入" ，“最多連續(xù)點(diǎn)擊次數(shù)”等等。

     對(duì)于連續(xù)2次，連續(xù)3次這種固定的連續(xù)次數(shù)的需求，我們可以通過(guò)自關(guān)聯(lián)數(shù)據(jù)表(join)2次或者3次或者lead/lag開(kāi)窗函數(shù)實(shí)現(xiàn)。

     而對(duì)于不確定連續(xù)次數(shù)的需求，比如連續(xù)出現(xiàn)100次，總不能進(jìn)行100次自關(guān)聯(lián)或者寫(xiě)100個(gè)函數(shù)，因此我們可以增加輔助列的方式判斷是否連續(xù)，我們可以通過(guò)整體自然排序-分組排序的做法進(jìn)行判斷

示例

我們直接引用某網(wǎng)站上的示例數(shù)據(jù)：有Logs表包含id列和num列

需求：查詢所有至少連續(xù)出現(xiàn)三次的數(shù)字

具體數(shù)據(jù)如下：

Logs 表：
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

1.通過(guò)自關(guān)聯(lián)方式實(shí)現(xiàn)

2.lead/lag函數(shù)方法實(shí)現(xiàn)

SELECT DISTINCT NUM FROM (

  SELECT  ID,

          NUM,

          LAG(NUM,1) OVER(ORDER BY ID) LAG,

          LEAD(NUM,1) OVER(ORDER BY ID) LEAD

  FROM LOGS) T

WHERE  NUM=LAG AND NUM = LEAD  --通過(guò)偏移+1/-1行數(shù)據(jù)判斷是否連續(xù)

3.通過(guò)增加輔助列方式判斷連續(xù)

分別增加需要判斷組內(nèi)的自增長(zhǎng)列以及分組增長(zhǎng)列

核心思路：對(duì)于連續(xù)的一組數(shù)據(jù)，他的自然序列和分組排序序列的差必然相同，通過(guò)統(tǒng)計(jì)計(jì)算這個(gè)相同差出現(xiàn)的次數(shù)，可以判斷他的連續(xù)性，即同一個(gè)差值統(tǒng)計(jì)出現(xiàn)2次，則連續(xù)2次，出現(xiàn)3次則為連續(xù)3次

SELECT DISTINCT Num FROM (  SELECT NUM,         COUNT(1) as SerialCount   FROM     (SELECT Id,Num,      row_number() over(order by id) -      ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup     FROM       LOGS     ) as SubGROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result --判斷出現(xiàn)的次數(shù)統(tǒng)計(jì)