问题情景

在数据库中，存在表结构如下：

Column Type
id bigint(20)
number bigint(20)

要求找出 number 字段中所有至少连续出现两次的数据。

这类问题在业务场景中并不少见，例如，要求对一些连续的发票编号、订单编号进行处理。

在处理过几次这个问题后，想要通过文章将简要总结一下这类问题的解法。

SQL 窗口函数

这个问题在 LeetCode 的题库中也有类似的问题180. 连续出现的数字，都可以使用窗口函数来解决。

窗口函数

窗口函数是一种强大的 SQL 功能，用于对结果集中的某些行进行计算，同时保留每行的独立性。与聚合函数不同，窗口函数不会将行分组为单一输出，而是为每行提供额外的计算结果。

基本语法如下：

<窗口函数> OVER ([PARTITION BY <列>] [ORDER BY <列>] [<窗口框架>])

以下是一些常用的窗口函数及其功能：

  • ROW_NUMBER()：为每行分配唯一的序号，序号连续。

  • RANK()：为每行分配排名，相同值会跳过排名。

  • DENSE_RANK()：类似于 RANK()，但不会跳过排名。

  • NTILE(n)：将结果集分为 n 个桶，并为每行分配桶编号。

  • LAG() 和 LEAD()：分别返回当前行之前或之后的值。

  • SUM()、AVG()、MAX()、MIN()：聚合函数也可作为窗口函数使用。

解法

此解法基于 MySQL 8.0
使用了 窗口函数 和 CET(公共表达式)的特性

在这个问题中，ROW_NUMBER() 是关键。

  1. 先将 number 字段进行排序，并使用 ROW_NUMBER() 为每行分配序号。
  2. 再将 number 作为数值类型与 ROW_NUMBER() 生成的序号 做差。

示例:

number row_num diff
1 1 0
2 2 0
5 3 2
6 4 2
… … …

这里经过做差操作之后，发现连续的 number 值，其序号之间的差值都是相同的。

  1. 将 diff 做 GROUP BY 分组，每组中 number 的个数超过 1 个的，就是连续的。

经过上面三步之后，就可以得到结果了。

1
2
3
4
5
6
7
8
9
10
11
12
# 1、2 步合并，采用临时结果集
WITH diff AS (
  select id,
         num,
         num - ROW_NUMBER() over (order by num) as d
  from nums
)
# 查询出符合分组中个数超过 1 的行
SELECT id, num FROM diff
WHERE d in (
    SELECT d from diff GROUP BY d HAVING COUNT(*) > 1
);

代码解法

SQL 查表的方法虽然简单，但是真实场景中，数据量特别大时，谨慎使用。

通过代码来查询来处理连续数字的问题，更加简单，也更加可控。

对于直接查询的场景，使用代码特别简单，基本思路就是查出数据后排序，再循环遍历统计连续超过 1 的数字，所以这里不讨论查询的实现，而是介绍插入的做法。

加一个 flag 标记，表示是否连续, 在插入/删除时更新 flag，这样可以通过 flag 来判断是否连续，这种方式更具有性价比

当前有一个表，结构入下：

id number flag
1 2 1
2 3 1
3 6 0
4 8 0

插入数据

  1. 将待插入的数据，计算出其 +1 和 -1 后的数据
  2. 直接将待插入数据入库
  3. 使用 1 中的计算结果查询数据库中存在的记录
  4. 将 1 的计算结果和 3 查询结果进行对比，更新连续的记录

重点：
第 2 步中先入库的操作可以减少判断待插入数据本身存在连续的情况

删除数据

… 等待更新

更新数据

… 等待更新