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