Script—Oracle Mysql 获取字段连续分段间隔

前言

日常工作中我们常常遇到需要查询表中某个字段值的连续信息,使用最多的场景就是我们在各个APP上看到的连续签到信息,是不是很熟悉?今天我们总结下实现类似需求的相关Sql脚本。

建立测试数据

模拟用户签到信息表,两个字段:userid 用户ID ,dt 签到日期,如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/*CREATE TABLE T_TEST AS
SELECT '1' USERID,TO_DATE('2015-01-10','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '1' USERID,TO_DATE('2015-01-09','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '1' USERID,TO_DATE('2015-01-08','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '1' USERID,TO_DATE('2015-01-05','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '1' USERID,TO_DATE('2015-01-04','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '1' USERID,TO_DATE('2015-01-02','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '1' USERID,TO_DATE('2015-01-01','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '1' USERID,TO_DATE('2014-12-30','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-22','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-21','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-20','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-19','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-16','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-15','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-14','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-10','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-09','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '2' USERID,TO_DATE('2015-01-08','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '3' USERID,TO_DATE('2015-01-16','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '3' USERID,TO_DATE('2015-01-15','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '3' USERID,TO_DATE('2015-01-14','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '3' USERID,TO_DATE('2015-01-10','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '3' USERID,TO_DATE('2015-01-09','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '3' USERID,TO_DATE('2015-01-08','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '3' USERID,TO_DATE('2015-01-07','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '4' USERID,TO_DATE('2015-01-06','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '4' USERID,TO_DATE('2015-01-05','yyyy-mm-dd') DT FROM DUAL UNION ALL
SELECT '4' USERID,TO_DATE('2015-01-03','yyyy-mm-dd') DT FROM DUAL ;*/

目标

BE:开始日期

EN:截止日期

DU:连续天数

1
2
3
4
5
6
7
8
9
10
11
12
13
USERID BE EN DU
------ ----------- ----------- ----------
1 2015-01-08 2015-01-10 3
1 2015-01-04 2015-01-05 2
1 2015-01-01 2015-01-02 2
1 2014-12-30 2014-12-30 1
2 2015-01-19 2015-01-22 4
2 2015-01-14 2015-01-16 3
2 2015-01-08 2015-01-10 3
3 2015-01-14 2015-01-16 3
3 2015-01-07 2015-01-10 4
4 2015-01-05 2015-01-06 2
4 2015-01-03 2015-01-03 1

方案一

原理

利用oracle分析函数lead,lag,取得每个用户签到日期降序后的前一个值和后一个值,在此基础上,分别取日期不等于后一日期+1(分段的最小值),前一日期-1的数据(分段的最小值),最后相同行号的数据即为一个连续段的起始值。

sql脚本

1
2
3
4
5
6
7
8
9
10
11
WITH T AS (SELECT USERID,DT
,NVL(LEAD(DT) OVER(PARTITION BY USERID ORDER BY DT DESC),DT-2) LEAD_DATE_REAL
,NVL(LAG(DT) OVER(PARTITION BY USERID ORDER BY DT DESC),DT+2) LAG_DATE_REAL
FROM T_TEST)
SELECT A.USERID
,A.DT BE
,B.DT EN
,B.DT - A.DT + 1 DU
FROM (SELECT ROWNUM RN,USERID,DT FROM T WHERE DT <> LEAD_DATE_REAL + 1) A
,(SELECT ROWNUM RN,USERID,DT FROM T WHERE DT <> LAG_DATE_REAL - 1) B
WHERE A.RN = B.RN;

方案二

原理

利用oracle分析函数lead,取得每个用户签到日期降序后的后一个值,first_value取每个用户日期降序后的第一个值,过滤掉日期连续的值后即为所有不连续日期,剩余日期即为分段的最小值,再利用lag取已经计算出的lead值的上一个值即为此分段的最大值(为空去first_value)。

sql脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT USERID
,BE
,EN
,EN - BE + 1 DU
FROM (SELECT USERID
,DT BE
,NVL(LAG(LEAD_DT_REAL) OVER(PARTITION BY USERID ORDER BY DT DESC),LAST_DT) EN
FROM (SELECT USERID
,DT
,NVL(LEAD(DT) OVER(PARTITION BY USERID ORDER BY DT DESC),DT - 2) LEAD_DT_REAL
,FIRST_VALUE(DT) OVER(PARTITION BY USERID ORDER BY DT DESC) LAST_DT
FROM T_TEST)
WHERE DT <> LEAD_DT_REAL + 1);

方案三

原理

此方案没有使用特殊函数,使用sql标准语法,因此受限范围小,大多数数据库都能使用。其原理既是使用两个自关联子查询获取到用户向前、向后不连续日期的集合,两个集合相同行数对应的日期即为连续日期段的起始值。表述有点绕,直接看脚本:

sql脚本

1
2
3
4
5
6
7
8
9
10
11
12
SELECT R1.USERID,R2.DT BE,R1.DT EN,R1.DT - R2.DT + 1 DU
FROM (SELECT ROWNUM RN ,C.USERID,C.DT
FROM (SELECT USERID,DT
FROM T_TEST A
WHERE NOT EXISTS (SELECT 1 FROM T_TEST B WHERE A.DT = B.DT - 1 AND A.USERID=B.USERID)
ORDER BY USERID,DT DESC) C ) R1
,(SELECT ROWNUM RN ,C.USERID,C.DT
FROM (SELECT USERID,DT
FROM T_TEST A
WHERE NOT EXISTS (SELECT 1 FROM T_TEST B WHERE A.DT = B.DT + 1 AND A.USERID=B.USERID)
ORDER BY USERID,DT DESC) C ) R2
WHERE R1.RN = R2.RN;

TIPS

如果你有有更多方案记得联系我!