MySQL--查找至少连续出现三次的数字--方法总结

第一步：创建表

mysql> Create table if Not Exists Logs (id int, num int);
Query OK, 0 rows affected (0.24 sec)

mysql> insert into Logs (id, num) values ('1', '1');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Logs (id, num) values ('2', '1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Logs (id, num) values ('3', '1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Logs (id, num) values ('4', '2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Logs (id, num) values ('5', '1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Logs (id, num) values ('6', '2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Logs (id, num) values ('7', '2');
Query OK, 1 row affected (0.01 sec)


mysql> select * from logs;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    4 |    2 |
|    5 |    1 |
|    6 |    2 |
|    7 |    2 |
+------+------+
7 rows in set (0.00 sec)

第二步：查找连续出现三次以上的数字

法一：将logs表分别命名为l1、l2、l3----此方法只能解决连续出现少数的问题，出现次数多的（100次以上）解决困难。

mysql> SELECT DISTINCT
    ->     l1.Num AS ConsecutiveNums
    -> 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
    -> ;
+-----------------+
| ConsecutiveNums |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.01 sec)

法二：使用到：row_number() over(partition by Num order by Id）

mysql> 
SELECT 
    DISTINCT Num as ConsecutiveNums 
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 Sub
GROUP BY
    Num,SerialNumberSubGroup 
HAVING 
    COUNT(1) >= 3) as Result;

+-----------------+
| ConsecutiveNums |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.01 sec)

其中：

mysql> 
SELECT
    Id,Num,
    row_number() over(order by id) as '1'
FROM
    logs;

+------+------+---+
| Id   | Num  | 1 |
+------+------+---+
|    1 |    1 | 1 |
|    2 |    1 | 2 |
|    3 |    1 | 3 |
|    4 |    2 | 4 |
|    5 |    1 | 5 |
|    6 |    2 | 6 |
|    7 |    2 | 7 |
+------+------+---+
7 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------
#这些num值一样的分组排序

mysql> 
SELECT
    Id,Num,
    row_number() over(partition by Num order by Id) as '2'
FROM
    logs;


+------+------+---+
| Id   | Num  | 2 |
+------+------+---+
|    1 |    1 | 1 |
|    2 |    1 | 2 |
|    3 |    1 | 3 |
|    5 |    1 | 4 |
|    4 |    2 | 1 |
|    6 |    2 | 2 |
|    7 |    2 | 3 |
+------+------+---+
7 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------


mysql> 
SELECT
    Id,Num,
    row_number() over(order by id) -
    row_number() over(partition by Num order by Id) as SerialNumberSubGroup
FROM
    logs;


+------+------+----------------------+
| Id   | Num  | SerialNumberSubGroup |
+------+------+----------------------+
|    1 |    1 |                    0 |
|    2 |    1 |                    0 |
|    3 |    1 |                    0 |
|    5 |    1 |                    1 |
|    4 |    2 |                    3 |
|    6 |    2 |                    4 |
|    7 |    2 |                    4 |
+------+------+----------------------+
7 rows in set (0.00 sec)

法三：

lead()函数：Lead函数是跟偏移量相关的分析函数

语法：lead(col,n,default) 用于统计窗口内往下第n行值

参数1为列名，即需要查找的字段

参数2为往下第n行（可选，默认为1），即往后查找的num行的数据

参数3为默认值（当往下第n行为null时候，取默认值，如不指定，则为null

over()表示lead()操作的数据都在over()的范围内，里面可以使用以下子句

• partition by 语句(用于分组)

• order by 语句()用于排序)

譬如：over(partition by a order by b) 表示以a字段进行分组，再以b字段进行排序，对数据进行查询。

lag()函数

语法：lag(col,n,DEFAULT) 用于统计窗口内往上第n行值

参数1为列名，即需要查找的字段

参数2为往上第n行（可选，默认为1），即往后查找的num行的数据

参数3为默认值（当往上第n行为null时候，取默认值，如不指定，则为null

mysql> 
select 
    distinct num as ConsecutiveNums
from (
    select 
        num,
        lag(num, 1, null) over (order by id) lag_num,
        lead(num, 1, null) over (order by id) lead_num
    from 
        logs) as l
where 
    l.Num = l.lag_num and l.Num = l.lead_num;

+-----------------+
| ConsecutiveNums |
+-----------------+
|               1 |
+-----------------+