oracle中连续问题详解

在 Oracle 数据库里，连续问题一般涉及的是对连续数值、日期或者事件的识别与处理。这类问题在实际应用中十分常见，像分析用户连续登录情况、查找连续销售增长区间、统计设备连续正常运行时间等都会用到。下面会详细讲解 Oracle 中连续问题的处理方法。

一、连续问题的常见类型

连续数值：例如，要找出用户 ID 中连续的数值段。
连续日期：比如，分析用户连续签到的天数。
连续事件：举例来说，统计设备连续正常运行的天数。

二、处理连续问题的关键思路

处理连续问题的核心思路是借助窗口函数或者自连接，把原始数据划分成不同的连续组，接着对这些组进行统计。下面介绍几种常用的方法：

三、具体实现方法

1. 差值法（Row Number 法）

这种方法适用于连续数值或者日期的场景，其原理是：对连续的数值或者日期组，用它们减去对应的行号，得到的值是相同的，基于这个值就能对数据进行分组。

示例 1：查找连续的数值
假设有一个nums表，里面有如下数据：

NUM
---
1
2
3
5
6
8
9
10

使用差值法查找连续的数值段：

SELECT MIN(num) AS start_num,MAX(num) AS end_num
FROM (SELECT num,num - ROW_NUMBER() OVER (ORDER BY num) AS grpFROM nums
) t
GROUP BY grp
ORDER BY start_num;

查询结果：

START_NUM | END_NUM
-------------------
1         | 3
5         | 6
8         | 10

示例 2：查找连续日期
假设有一个login_log表，记录了用户的登录日期：

USER_ID | LOGIN_DATE
---------------------
1       | 2025-06-18
1       | 2025-06-19
1       | 2025-06-20
1       | 2025-06-22
2       | 2025-06-20
2       | 2025-06-21
2       | 2025-06-23

查找每个用户的连续登录日期段：

SELECT user_id,MIN(login_date) AS start_date,MAX(login_date) AS end_date,COUNT(*) AS consecutive_days
FROM (SELECT user_id,login_date,login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grpFROM login_log
) t
GROUP BY user_id, grp
ORDER BY user_id, start_date;

查询结果：

USER_ID | START_DATE | END_DATE   | CONSECUTIVE_DAYS
-------------------------------------------------------
1       | 2025-06-18 | 2025-06-20 | 3
1       | 2025-06-22 | 2025-06-22 | 1
2       | 2025-06-20 | 2025-06-21 | 2
2       | 2025-06-23 | 2025-06-23 | 1

2. 使用 LAG/LEAD 函数

LAG 和 LEAD 函数能够访问前后行的数据，通过比较当前行与前后行的值，就可以判断是否连续。

示例：统计最长连续登录天数

WITH ranked_logins AS (SELECT user_id,login_date,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rnFROM login_log
),
consecutive_groups AS (SELECT r.user_id,r.login_date,r.login_date - r.rn AS grpFROM ranked_logins r
)
SELECT user_id,MAX(consecutive_days) AS max_consecutive_days
FROM (SELECT user_id,grp,COUNT(*) AS consecutive_daysFROM consecutive_groupsGROUP BY user_id, grp
) t
GROUP BY user_id;

3. 自连接法

自连接法是通过表的自身连接，找出相邻的记录，进而确定连续的范围。

示例：查找连续的数值段

SELECT MIN(a.num) AS start_num,MAX(b.num) AS end_num
FROM nums a
JOIN nums b ON a.num <= b.numAND NOT EXISTS (SELECT 1 FROM nums WHERE num = a.num - 1)AND NOT EXISTS (SELECT 1 FROM nums WHERE num = b.num + 1)
GROUP BY a.num - b.num
ORDER BY start_num;

四、处理断号问题

断号问题其实就是连续问题的反面，它是要找出缺失的数值或者日期。

示例：查找缺失的数值

WITH full_sequence AS (SELECT LEVEL + (SELECT MIN(num) FROM nums) - 1 AS numFROM DUALCONNECT BY LEVEL <= (SELECT MAX(num) - MIN(num) + 1 FROM nums)
)
SELECT fs.num AS missing_num
FROM full_sequence fs
LEFT JOIN nums n ON fs.num = n.num
WHERE n.num IS NULL;