Oracle连续数据处理示例

Oracle连续数据处理示例

下面这段内容讲解的功能是Oracle数据库中有一张表，表中存储了连续的时间记录，同时对应的还存储了一个标记位。现在要获取一个结果集：当标记位为0时，取前一个为1的时间数据，如果标记位为1时，取当前记录的时间数据。

先上干货。再解释

1、建表

create table test_date(
  t_TIME varchar(20),  --时间
  t_ISOM number default 0  --标记
)

2、初始化数据

DELETE FROM test_date;
insert into test_date(t_TIME,t_ISOM) values('20140101',1);
insert into test_date(t_TIME,t_ISOM) values('20140102',0);
insert into test_date(t_TIME,t_ISOM) values('20140103',0);
insert into test_date(t_TIME,t_ISOM) values('20140104',0);
insert into test_date(t_TIME,t_ISOM) values('20140105',0);
insert into test_date(t_TIME,t_ISOM) values('20140106',0);
insert into test_date(t_TIME,t_ISOM) values('20140107',0);
insert into test_date(t_TIME,t_ISOM) values('20140108',1);
insert into test_date(t_TIME,t_ISOM) values('20140109',1);
insert into test_date(t_TIME,t_ISOM) values('20140110',0);
insert into test_date(t_TIME,t_ISOM) values('20140111',1);
insert into test_date(t_TIME,t_ISOM) values('20140112',0);
insert into test_date(t_TIME,t_ISOM) values('20140113',0);
insert into test_date(t_TIME,t_ISOM) values('20140114',1);

3、获取结果数据

select
  case when T_ISOM=1 THEN T_TIME
  ELSE
      N
  END RESULT_TIME  --要的结果，comment by danielinbiti
  ,C.*
FROM
(
SELECT B.*,(M-B.T_TIME) AS M_D,(B.T_TIME-N) AS N_D FROM
(
  SELECT A.*,MAX(DNEXT) OVER(PARTITION BY x) AS M,MIN(DPRE) OVER(PARTITION BY x) AS N FROM
  (
    SELECT t_TIME,t_ISOM,ROW_NUMBER() OVER(ORDER BY t_TIME)-ROW_NUMBER() OVER(PARTITION BY t_ISOM ORDER BY t_TIME) x
    ,Lead(t_TIME)  over(order by t_TIME) as dnext,lag(t_TIME)  over(order by t_TIME) as dpre
          FROM test_date
  ) A order by t_time
) B
) c order by t_time

以上几步可以获取结果，当然可能对一些边缘数据有可能存在BUG，但这不影响主要原理的解释。边缘可以通过增加判断处理完成。

这里主要解释一下第三步骤的内容

1、首先里面一层SQL

SELECT t_TIME,t_ISOM,ROW_NUMBER() OVER(ORDER BY t_TIME)-ROW_NUMBER() OVER(PARTITION BY t_ISOM ORDER BY t_TIME) x
    ,Lead(t_TIME)  over(order by t_TIME) as dnext,lag(t_TIME)  over(order by t_TIME) as dpre
          FROM test_date

这里主要有ROW_NUMBER() OVER(ORDER BY t_TIME)和ROW_NUMBER() OVER(PARTITION BY t_ISOM ORDER BY t_TIME)这两个值的获取是处理连续性的关键

ROW_NUMBER() OVER(ORDER BY t_TIME)：根据时间排序获取ROW_NUMBER()，保证所有记录有连续编号

ROW_NUMBER() OVER(PARTITION BY t_ISOM ORDER BY t_TIME):根据标记位分组，再对时间排序，保证分组内记录有连续编号。

因为两个都是连续的，所以相减，那么每个分组都会得到一个值（这里说的是每个分组，所以每个分组内的记录也是一样的值），暂定为X

Lead和lag是统计函数，获取下一行和前一行的记录，这没有难度。

2、根据第一层的结果，对结果加工，根据X值分组，获取每个分组的最大和最小日期。

3、剩下的就可以任意摆布了，所有的结果都已经在第二层中计算出来的，可以根据自己想要任意组合获取想要的结果。比如当前记录最近得标记位是1的记录等等。