SQL常用算法-判断数据是否连续

常见场景

经常会遇到一些分析连续次数类的需求，会需要对数据判断是否连续，比如“用户是否连续登入" ，“最多连续点击次数”等等。

对于连续2次，连续3次这种固定的连续次数的需求，我们可以通过自关联数据表(join)2次或者3次或者lead/lag开窗函数实现。

而对于不确定连续次数的需求，比如连续出现100次，总不能进行100次自关联或者写100个函数，因此我们可以增加辅助列的方式判断是否连续，我们可以通过整体自然排序-分组排序的做法进行判断

示例

我们直接引用某网站上的示例数据：有Logs表包含id列和num列

需求：查询所有至少连续出现三次的数字

具体数据如下：

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

1.通过自关联方式实现

SELECT DISTINCT
     l1.NUM
FROM
    LOGS l1,
    LOGS l2,
    LOGS l3                --判断连续出现三次，因此自关联三次
WHERE
    l1.Id = l2.Id - 1       ---自关联上下级数据
    AND l2.Id = l3.Id – 1
    AND l1.NUM= l2.NUM
    AND l2.NUM= l3.NUM

2.lead/lag函数方法实现

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  --通过偏移+1/-1行数据判断是否连续

3.通过增加辅助列方式判断连续

分别增加需要判断组内的自增长列以及分组增长列

核心思路：对于连续的一组数据，他的自然序列和分组排序序列的差必然相同，通过统计计算这个相同差出现的次数，可以判断他的连续性，即同一个差值统计出现2次，则连续2次，出现3次则为连续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 --判断出现的次数统计