【背景】
在清洗數據時,發現源端系統某時間類字段下的數據存在三類格式,懷疑這是源端接受自三個或更多系統的來源數據格式不統一造成的。之所以出現這種情況,因為源端該時間類字段竟然用的varchar2格式,可能源端系統在接受不同系統上傳數據時沒做規范造成的。需要把該字段下的數據按分類進行處理、清洗。
【解決】
我們可以利用case函數,對不同類型的數據進行分類處理,例如:
select CASE WHEN 條件1 THEN 處理方式1 WHEN 條件2 THEN 處理方式2 ELSE 處理方式3 END 命名 from 源端表;
【實驗】
創建實驗表,如下:
create table 實驗表 (ID varchar2(32) default sys_guid(), DATE_TIME varchar2(50), MEMO varchar2(32) );
插入實驗數據,模擬出三類時間格式類型,如下:
insert into 實驗表 (DATE_TIME,MEMO) values('2017-08-11 23.0:18.0:30.0','1'); insert into 實驗表 (DATE_TIME,MEMO) values('2015-05-27 12.0:24.0:20.0','1'); insert into 實驗表 (DATE_TIME,MEMO) values('20140409 11:00:12 PM','2'); insert into 實驗表 (DATE_TIME,MEMO) values('20120401 10:10:00 AM','2'); insert into 實驗表 (DATE_TIME,MEMO) values('2013 02 08 08:12:23:000 PM','3'); insert into 實驗表 (DATE_TIME,MEMO) values('2015 01 31 09:00:00:000 PM','3'); commit; select * from 實驗表;
創建目標表,如下:
create table 目標表 (ID VARCHAR2(32), RESULT_TIME DATE, LEVEL_NUMBER VARCHAR2(32) );
不處理情況下,抽取數據,報錯如下:
INSERT /*+append*/ INTO 目標表 NOLOGGING SELECT ID ID, CASE WHEN DATE_TIME LIKE '%-%-%' THEN TO_DATE(REPLACE(DATE_TIME,'.0',''),'YYYY-MM-DD HH24:MI:SS') WHEN DATE_TIME LIKE '% % % %:%:%:%' THEN TO_DATE(REPLACE(DATE_TIME,':000',''),'yyyy mm dd HH:MI:SS AM','NLS_DATE_LANGUAGE=American') ELSE TO_DATE(DATE_TIME,'yyyy mm dd HH:MI:SS AM','NLS_DATE_LANGUAGE=American') END RESULT_TIME, MEMO LEVEL_NUMBER FROM 實驗表; COMMIT; SELECT * FROM 目標表;
小知識,簡而記之。
補充:對於英文格式日期處理
select to_date('1-JULY-15 22:23:11','DD-MON-YY hh24:mi:ss') FROM DUAL;