資料清洗小記：分類進行字串轉日期（某日期欄位存在多類格式處理），小記多類

資料清洗小記：分類進行字串轉日期（某日期欄位存在多類格式處理），小記多類

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處，否則有權追究著作權法律責任。

深藍的blog：http://blog.csdn.net/huangyanlong/article/details/46513855

 

【背景】

在清洗資料時，發現源端系統某時間類欄位下的資料存在三類格式，懷疑這是源端接受自三個或更多系統的來來源資料格式不統一造成的。之所以出現這種情況，因為源端該時間類欄位竟然用的varchar2格式，可能源端系統在接受不同系統上傳資料時沒做規範造成的。需要把該欄位下的資料按分類進行處理、清洗。

 

【解決】

我們可以利用case函數，對不同類型的資料進行分類處理，例如：

select CASE WHEN 條件1 THEN    處理方式1WHEN 條件2 THEN    處理方式2ELSE    處理方式3END 命名 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 SELECTID 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_NUMBERFROM 實驗表;COMMIT;SELECT * FROM 目標表;

小知識，簡而記之。

 

補充：對於英文格式日期處理

select to_date('1-JULY-15 22:23:11','DD-MON-YY hh24:mi:ss') FROM DUAL;

*******************************************藍的成長記系列****************************************************

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

藍的成長記——追逐DBA（1）：奔波於路上，挺進山東

藍的成長記——追逐DBA（2）：安裝！安裝！久違的記憶，引起我對DBA的重新認知

藍的成長記——追逐DBA（3）：古董上操作，資料匯入匯出成了問題

藍的成長記——追逐DBA（4）：追憶少年情愁，再探oracle安裝（Linux下10g、11g）

藍的成長記——追逐DBA（5）：不談技術談業務，惱人的應用系統

藍的成長記——追逐DBA（6）： 做事與做人：小技術，大為人

藍的成長記——追逐DBA（7）：基礎命令，地基之石

藍的成長記——追逐DBA（8）：重拾SP報告，回憶oracle的STATSPACK實驗

藍的成長記——追逐DBA（9）：國慶漸去，追逐DBA，新規劃，新啟程

藍的成長記——追逐DBA（10）：飛刀防身，熟絡而非專長：擺弄中介軟體Websphere

藍的成長記——追逐DBA（11）：回家後的安逸，暈暈乎乎醒了過來

藍的成長記——追逐DBA（12）：七天七收穫的SQL

藍的成長記——追逐DBA（13）：協調硬體廠商，六個故事：所見所感的“伺服器、儲存、交換器......”

藍的成長記——追逐DBA（14）：難忘的“雲”端，起步的hadoop部署

藍的成長記——追逐DBA（15）：以為FTP很“簡單”，誰成想一波三折

藍的成長記——追逐DBA（16）：DBA也喝酒，被捭闔了

藍的成長記——追逐DBA（17）：是分享，還是消費，在後IOE時代學會成長

******************************************************************************************************************

 

********************************************足球與oracle系列*************************************************

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

足球與oracle系列（1）：32路諸侯點兵，oracle32進程聯盟 之A組巴西SMON進程的大局觀

足球與oracle系列（2）：巴西揭幕戰預演，oracle體繫結構雜談

足球與oracle系列（3）：oracle進程排名，世界盃次回合即將戰罷！

足球與oracle系列（4）：從巴西慘敗於德國，想到，差異的RAC拓撲對比！ 

足球與oracle系列（5）：fifa14遊戲缺失的directX庫類比於oracle的rpm包！

足球與oracle系列（6）：伴隨建庫的亞洲杯——加油中國隊

******************************************************************************************************************