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的記錄等等。