本科生畢業論文（設計）題目:港口貨運統計與決策支持系統的設計與實現姓名:高旻學院:信息科學技術學院專業:計算機科學與技術班級:計科92學號:19209226導教師:胡濱職稱:講師2013年5月15日南京農業大學教務處制目錄摘要…………………………2關鍵詞…………………………2Abstract………………………2Keywords……………………2引言……………21課題研究意義及內容………………………21.1課題研究的背景與意義…………………31.2課題研究的內容…………32相關理論與技術基礎………………………32.1ETL介紹…………………32.1.1ETL增量更新…………32.1.2ETL的數據質量………………………42.2kettle工具簡介…………42.3oracle語句的應用………………………52.3.1oracle分析函數………………………52.3.2獲得數據中的數字部分………………52.4powerbuilder的特點……………………63港口貨運統計與決策支持系統分析與設計………………73.1需求分析…………………73.1.1港口物流操作流程……………………73.1.2系統目標………………73.2數據庫設計………………73.2.1數據庫分析……………83.2.2數據庫結構設計………………………83.3ETL設計…………………103.3.1ETL分析………………103.3.2ETL設計………………103.4查詢及統計功能設計…………………213.4.1查詢和統計模塊………………………213.4.2登錄模塊……………214港口貨運統計與決策支持系統的實現…………………224.1ETL轉換的實現…………224.1.1建立視圖…………224.1.2kettle程序…………234.2界面實現………………255總結與展望………………30致謝…………………………31參考文獻……………………31港口貨運統計與決策支持系統的設計與實現計算機科學與技術專業學生高旻指導老師胡濱摘要：如今的集裝箱運輸高速發展，在網絡環境下的信息服務，不再僅僅是傳輸信息，而是越來越多的專注于提供增值信息服務。研究箱狀態信息查詢和相關數據統計以支持決策分析成為趨勢。本文設計并實現了對連云港電子口岸諸多系統所涉及的數據進行抽取、轉換、再造，建立一個數據底帳，在此底賬的基礎上對進出口箱量、中轉箱量等進行統計分析，對箱和提單在整個進出口操作中各個環節的狀態進行聯合查詢。本文首先闡述了課題研究的背景、意義和內容，介紹了用到的主要理論知識，然后說明了數據庫設計、ETL設計和界面設計。關鍵詞：ETL；統計；集裝箱DesignandImplementationofPortCargoStatisticsandDecisionSupportSystemStudentmajoringinComputerScienceandTechnologyGaominTutorHubinAbstract：Today,containertransporthasdevelopedrapidlyinthenetworkenvironment.Theinformationservicesofcontainertransportnolongerjusttransportinformation,butmoreandmorefocusonprovidingvalue-addedinformationservices.ThissubjecttransformstherawdatafromtheLianyungangEDICentertocreateadataledger,andthenstatisticsimportandexportcontainervolumeandquerystateofcontaineronthebasisofthisledger.Thispaperfirstdescribesthebackground,significanceandcontentoftheresearch.Thenthispaperwritesthemaintheoreticalknowledgeaboutthissubject.Andthenthispaperexplainsdatabasedesign,ETLdesignandinterfacedesign.Keywords:ETL;statistics;container目前，在港口企業，一般都建立有運輸配送系統、倉儲管理系統等信息系統。這些系統為港口企業累計了海量的業務信息。但是，這些軟件都較簡單、技術含量低，不具備提取并加工所存儲的信息的功能，導致不能對企業中高層的經營管理提供決策支持，無法滿足日益規范化的貨代行業發展。如何使港口企業在日常生產經營過程中產生的大量經營業務數據為公司決策層在進行經營決策時發揮更大的作用，正是統計與決策支持系統所關注的問題。所以，建立一個統計與決策支持系統就成為目前港口企業信息化建設的工作之一。本課題便是研究港口貨運統計與決策支持系統的設計與實現。1課題研究意義及內容1.1課題研究的背景與意義連云港電子口岸是以港航電子數據交換業務為主的公司。隨著口岸業務數據的大規模增長，連云港電子口岸逐步建形成了網上訂艙、電子提箱、電子裝箱單、場站系統、理貨入庫單系統等諸多業務系統。在出口業務方面，已經逐步形成了一條從訂艙到放行裝船的完整系統鏈條。本課題研究便是基于連云港電子口岸諸多系統所涉及的數據。如今的集裝箱運輸高速發展，在網絡環境下的信息服務，不再僅僅是傳輸信息，而是越來越多的專注于提供增值信息服務。對已有信息進行分析加工，是所得到的新信息所具有的價值明顯高于原有信息，這就是信息增值服務。這樣，連云港電子口岸的僅以傳送信息作為信息服務核心功能的系統就逐漸不能滿足需求了。如何對信息進行進一步的提煉以便用于增值服務中變的至關重要。所以，研究“港口貨運統計與決策支持系統的設計與實現”具有可行性和必要性。1.2課題研究的內容首先，對各種歷史數據進行抽取、轉換、再造，整理出一些有輔助決策的數據來，主要用到的是kettle工具，數據庫用的是oracle。即從各個系統里面，識別并找出最原始的數據，將這部分數據抽取出來，建立一個數據底帳；然后針對這個底帳，再回溯關聯各個系統，追蹤各個系統的狀態變化，延時分析同步到底帳上，豐富底帳數據。這樣，底帳數據其實就是一個集裝箱從錄入系統、做各種業務、最后完結的一個完整周期。對與輔助決策、或者協調各個業務系統都是有很重要的意義的。一方面對整體的數據是一個新的認識，可以輔助決策，另一方面可以更好的協調當前運行的項目。其次，從對已整理出的底賬數據進行查詢、統計，界面的軟件用的是powerbuilder。對進出口箱量、中轉箱量等進行統計分析，對箱和提單在整個進出口操作中各個環節的狀態進行聯合查詢。一方面對整體的數據是一個新的認識，可以輔助決策，另一方面可以更好的協調當前運行的項目。2相關理論與技術基礎2.1ETL介紹ETL的三個字母分別代表的是extract、transform、load，即抽取、轉換、加載[1]。數據抽取即是從數據源中抽取目的系統需要的數據；數據轉換即是將數據抽取中獲取的數據按系統需求轉換，使其成為目的系統要求的形式，并對不一致、不需要的數據進行清洗和加工；數據加載即將轉換后的數據載入目的系統數據源。ETL的核心在于T這個過程，也就是轉換，而抽取和裝載一般可以作為轉換的輸入和輸出或者作為一個單獨的部件，其復雜度沒有轉換部件高。ETL過程一般都是批量操作，不是針對單條記錄的insert、update和select等操作。ETL原本用于構建數據倉庫[2]，負責將分布的、異構的數據源中的數據抽取到臨時中間層中進行清洗、轉換、集成，最后加載到數據倉庫，成數為據挖掘的基礎。現在也越來越多地將ETL應用于一般信息系統中數據的遷移、交換和同步[3]。2.1.1ETL增量更新ETL過程可分為全量過程和增量過程，前者對應首次的數據集成，后者對應著數據的增量維護。增量更新按照數據種類的不同大概可以分成四種：只增加，不更新；只更新，不增加；即增加也更新；有刪除，有增加，有更新。這四種增量更新的思路大致相同，使用的步驟可能略有不同。增量更新的核心問題在與如何找出自上次更新以后的數據。方法一是使用時間戳。在原數據庫增加一個時間戳，然后在轉換之后的對應表保留這個時間戳，然后每次抽取數據的時候，先讀取這個目標數據庫表的時間戳的最大值，把這個值當作參數傳給原數據庫的相應表，根據這個時間戳來做限定條件來抽取數據，抽取之后同樣要保留這個時間戳。方法二是使用觸發器。分別對insert,update,delete操作建立觸發器，將源數據表中進行過這些操作的數據加入到新表，在進行ETL的過程的時候讀這個新表，在讀完之后將這個表里面的數據刪除掉。方法三是定時執行增量更新。比如兩個小時或每天或者一個星期進行一次更新，這個時候可以不需要在目標表中增加一個時間戳字段來判斷ETL進行的最大時間，直接在取得原數據庫的時間加上限定條件。這個時候需要傳一個參數，用getSystemInfo步驟來取得，而且你還可以控制時間的精度，比如到天還是到秒的時間。但這種方法，對某時段因為某種原因沒有更新的情況處理比較麻煩，可能需要手工處理回來，如果失敗的情況經常發生，那還是使用在目標數據庫中增加一個時間字段取最大時間戳的方式比較通用。其他方法還有使用日志文件和使用全量數據等。2.1.2ETL的數據質量ETL很難保證數據的百分百準確。數據不一定要求百分百準確，但要知道為什么不準確。首先在數據源方面，就可能存在影響數據質量的原因，如數據源中存在數據格式錯誤、數據不一致、數據錄入不全等。在ETL過程要有處理這些質量有問題數據的保證。這些問題數據，是丟棄還是處理，無法逃避。如果沒有質量保證，那么在這個過程中，錯誤會逐漸放大，影響下一環節。ETL過程中也有對數據準確性產生重大影響的因素，可以分為下面幾類：規則描述錯誤。設計人員對數據源系統理解的不充分，導致規則理解錯誤，這是一方面。另一方面，是規則的描述，如果無二義性地描述規則也是要探求的一個課題。2、ETL開發錯誤。即時規則很明確，ETL開發的過程中也會發生一些錯誤，例如邏輯錯誤等。例如對同一數據可能存在增刪改三種操作時，沒有為三種操作排序，而是讓其同時進行，導致可能出現數據已刪除卻又有對其修改得操作需要執行。3、人為處理錯誤。在整體ETL流程沒有完成之前，通常會手工試運行ETL過程，這可能導致誤刪了數據、重復裝載數據等。如果在源頭不能保證比較干凈的數據，那么后面的分析功能的可信度也都成問題。2.2kettle工具簡介Kettle是一款國外開源的etl工具，純java編寫[4]，可以在Window、Linux、Unix上運行，綠色無需安裝，可以運行于多個平臺，數據抽取高效穩定。要運行KETTLE要求先安裝JDK1.5或以上版本。Kettle中有兩種腳本文件，其一是transformation即轉換，后綴為.ktr，其二為job即作業,后綴為.kjb。transformation完成針對數據的基礎轉換，好比工廠里的生產流水線，每個組件相當于一個員工；job則完成整個工作流的控制，好比工廠里的管理。如果用記事本打開文件可發現轉換和作業都是xml類型文件。作業分為分串行執行和并行執行，串行執行是先執行完其中一條線再執行另一條線，并行是兩條線同時執行，同一條線上的兩個步聚會先執行前面的再執行后面的，每個步驟執行結果分兩種：true(成功)/false(失敗),根據返回結果可以控制流程走向。轉換是一開始所有步驟同時運行，記錄會從最前端的步驟向后傳遞，傳遞到相應步驟則該記錄被該步驟作相應處理，處理完成再把記錄往后傳遞，記錄傳遞分復制和分發兩種模式。Kettle家族目前包括4個產品：Spoon、Pan、CHEF、Kitchen。SPOON允許你通過圖形界面來設計ETL轉換。CHEF允許創建任務。PAN允許你批量運行由Spoon設計的ETL轉換，它是一個后臺執行的程序，是一個數據轉換引擎，沒有圖形界面。Kitchen可以運行利用XML或數據資源庫描述的任務，也是一個后臺運行的程序。Kettle提供了兩種資源庫的選擇方式：數據庫存放、本地文件存放。數據庫存放是通過數據庫連接直接在數據庫里面創建kettle表，表里面記錄著你所做的任何保存過的記錄以及轉換和任務。此方法是遠程存放的方式，具有可多人共享一個資源庫的優勢，但是也存在資源庫不穩定的缺點。2.3oracle語句的應用2.3.1oracle分析函數在日常的生產環境中，我們接觸得比較多的是OLTP系統即聯機事務處理系統，與此不同的是OLAP系統即聯機分析處理系統。OLAP系統一般用于系統決策使用。通常和數據倉庫、數據分析、數據挖掘等概念聯系在一起。這些系統的特點是數據量大，對實時響應的要求不高或者根本不關注這方面的要求，以查詢、統計操作為主。如查找上一年度各個銷售區域排名前10的員工或按區域查找上一年度訂單總額占區域訂單總額20%以上的客戶或查找上一年度銷售最好和最差的產品。這兩個例子和一般的查詢有些不同，表現在：需要對同樣的數據進行不同級別的聚合操作；需要在表內將多條數據和同一條數據進行多次的比較；需要在排序完的結果集上進行額外的過濾操作。這時，一般的select語句中的orderby和groupby便不能滿足查詢要求，需要用到分析函數。分析函數允許對一個數據集進排序和篩選,這是SQL不能實現的。分析函數通常用于計算數據累計值、數據移動值、數據中間值和輸出集合報表。分析函數通過將行分組后，再計算這些分組的值。與聚合函數不同之處在于能夠對每一分組返回多行值。分析函數根據分析子句將行分組，一個分組稱為一個窗口，每一行都對應有一個在行上滑動的窗口。該窗口確定當前行的計算范圍，窗口大小可以用多個物理行進行度量，也可以使用邏輯區間進行度量，如時間。分析函數是在查詢中除orderby外最后執行的操作，所以分析函數只能用于選擇列或orderby子句，而不能用于where、groupby和having子句中。分析函數的語法:

FUNCTION_NAME(<argument>,<argument>...)

OVER

(<Partition-Clause><Order-by-Clause><WindowingClause>)如:sum(sal)over(partitionbydeptnoorderbyename)asnew_alias。其中sum就是函數名，(sal)是分析函數的參數，每個函數有0~3個參數，參數可以是表達式，例如:sum(sal+comm)；over是一個關鍵字，用于標識分析函數，否則查詢分析器不能區別sum()聚集函數和sum()分析函數；partitionbydeptno是可選的分區子句，如果不存在任何分區子句，則全部的結果集可看作一個單一的大區；orderbyename是可選的orderby子句，有些函數需要它，有些則不需要，依靠已排序數據的那些函數，如：用于訪問結果集中前一行和后一行的LAG和LEAD時必須使用，而AVG則不需要。在使用了任何排序的開窗函數時，該子句是強制性的,，指定了在計算分析函數時一組內的數據是如何排序的。2.3.2獲得數據中的數字部分方法一是使用translate函數。translate函數語法為TRANSLATE(string,from_str,to_str)。此函數返回將from_str中的每個字符替換為to_str中的相應字符以后的string。TRANSLATE是REPLACE所提供的功能的一個超集。如果from_str比to_str長，那么在from_str中而不在to_str中的額外字符將從string中被刪除，因為它們沒有相應的替換字符。to_str不能為空。Oracle將空字符串解釋為NULL，并且如果TRANSLATE中的任何參數為NULL，那么結果也是NULL。將translate函數用于得數據中的數字部分的例子：selecttranslate('23456中國3-00=.,45','0123456789'||'23456中國3-00=.,45','0123456789')fromdual;，其結果為2345630045。方法二是自定義函數。可以自定義一函數來獲取數據中的數字部分，如：createorreplacefunctionf_filter_str(var_strvarchar)returnvarcharisvar_str_newvarchar2(2000);beginforiin1..length(var_str)loopifascii(substr(var_str,i,1))>=48andascii(substr(var_str,i,1))<=57thenvar_str_new:=var_str_new||substr(var_str,i,1);endif;endloop;returnvar_str_new;endf_filter_str;運行selectf_filter_str('23456中國3-00=.,45')fromdual;，其結果也是2345630045。方法三是用正則表達式函數regexp_replace。Oracle正則表達式函數regexp_replace有六個參數，第一個是輸入的字符串，第二個是正則表達式，第三個是替換的字符，第四個是標識從第幾個字符開始正則表達式匹配且默認為1，第五個是標識第幾個匹配組且默認為全部都替換掉，第六個是是取值范圍。現有字符串'23456中國3-00=.,45'，想得到結果2345630045，則寫為regexp_replace('23456中國3-00=.,45'，’[0-9]+’,'23456中國3-00=.,45')。2.4powerbuilder的特點Powerbuilder是一款優秀的企業級數據庫前端應用開發工具，主要特點有：可視話的開發環境Powerbuilder是一個集成的完全可視化的開發工具。使用它可以用一種可視、直觀的方式來創建應用程序所需的用戶界面和數據庫接口。面向對象的開發工具Powerbuilder是一種面向對象編程的快速開發工具[5]。同絕大多數的windows應用程序一樣，它采用事件驅動工作方式。在這種方式下，程序的執行主要是由對象的各種事件來觸發的——事件代碼只有當與之有關的事件發生時才會被執行。它的窗口、菜單和數據窗口等都是對象，用戶也可以構造自己的對象[6]。PowerScript語言PowerScript語言是powerbuilder專用的編程語言。這種語言是一種增強型的結構化編程語言，其基本語法要素與Pascal、C等傳統結構化語言類似，而且如同BASIC語言一樣簡單易學。在PowerScript中除了可以直接引用powerbuilder提供的大量函數和系統對象外，還支持大二進制對象類型和嵌入式SQL語句[7]。獨一無二的數據窗口技術Powerbuilder最大的特點就是其采用了獨一無二的數據窗口技術。這種技術對定義和開發數據庫連接過程進行了抽象，使得程序開發人員能夠完全可視化地完成應用系統中有關數據庫的編程工作。開放的系統Powerbuilder可以訪問任何一個常用的后臺數據庫系統，如oracle、sybase、informix、DB2和SQLServer等大型數據庫[8]，以及Xbase、FoxPro和Paradox等小型數據庫。它本身也帶一個小型的個人數據庫ASA，以方便編程人員學習和使用。它提供了通用的前臺用戶界面和相關的數據庫接口，開發人員無需關心每個數據庫的特點和特性，只需集中精力進行前臺應用程序的開發。多平臺的開發環境powerbuilder現在有windows、mactintosh和unix等版本。在不同操作系統開發的應用程序只需要在另一操作系統下重新編譯即可直接運行[9]。網絡應用程序的開發使用powerbuilder，除了可以方便快捷地創建傳統的基于客戶/服務器模式的數據庫應用系統外，同時還支持基于N層體系結構分布式的應用程序和基于web的應用程序的開發[10]，完全具備開發企業級商務應用系統的能力。3港口貨運統計與決策支持系統分析與設計3.1需求分析目前連云港電子口岸關于貨運的業務主要集中于集裝箱的進出口，有網上訂艙、電子提箱、電子裝箱單、場站系統、理貨入庫單系統等諸多業務系統。這些系統的數據間存在著密切的聯系，但卻由于使用的單位不同等原因分別存在于不同的數據庫，在貨代、船代、海關需要查詢一些關系到多個系統的信息時，需要查詢多次，訪問多個數據庫。并且由于信息間相對孤立，沒有對信息進行加工和比較，不能為高層決策提供依據。所以需要先將數據抽取、轉換、加載形成底賬，再在底賬的基礎上實現聯合查詢和數據統計分析。3.1.1港口物流操作流程在連云港，集裝箱出口時主要流程按順序是：網上訂艙、船公司或船代審批、貨代電子提箱、場站放箱、運輸裝箱、返場即進場站或進碼頭、報檢、理貨、碼頭配載、裝船離港。其中網上訂艙時會有訂單相關訂單信息，在船公司審批過后會生成提單號，訂艙是處于初始狀態還是船方確認，或是船方刪除等狀態需要反映給貨代。在返程進碼頭時會有電子裝箱單信息。在報檢后會有報檢信息，其中報檢狀態是已審核還是檢驗處理中等需要反饋給貨代。在理貨時會有入庫單，記錄施封時間、箱或提單狀態等信息，這也是貨代希望知道的。連云港集裝箱進口流程按順序為：碼頭或船公司進行提箱委托、車隊提箱、車隊運輸、還箱。其中涉及到電子提箱信息，其中包括提箱狀態和提箱時間，這也是貨代關注的。綜上可見，在查詢時要將貨代需要知道的信息盡可能通過只輸入箱號或提單號就能顯示出其所有信息和狀態。3.1.2系統目標本系統將根據有關船信息、集裝箱信息、提單信息等實現以下目標：1、通過抽取、轉換、加載各個系統的數據源形成數據底賬。2、在底賬的基礎上實現聯合查詢，即在本系統中就可查詢箱或提單的所有的狀態信息，而不用登陸多個系統分別取得相應的狀態信息。3、對箱量進行統計對比，如進出口箱量、中轉箱量等。3.2數據庫設計3.2.1數據庫分析系統需要建立數據庫存放數據底賬。由于外界數據的大量性、復雜性和多樣性，所以存儲在oracle數據庫的底賬應該是經過分析處理后有用的信息，此數據庫就相當于一個大型的數據倉庫。本系統建立的數據底賬流程為：外界數據源外界數據源數據提取數據加載數據加載到數據底賬數據庫時，其中關于反映提單和箱狀態的數據應存放在一張表里，以便查詢。箱信息回執和提單信息回執是申報給海關的數據海關處理后所給的反饋，分別單獨建表。關于統計的把同一類查詢的數據應放于同一張，不同統計內容不同的數據要分表存放。3.2.2數據庫結構設計本文建立了六張表，分別是：中轉箱底賬表K_T_ZZ、出口裝箱單底賬表K_T_COSTCO、集裝箱裝卸船底賬表K_T_COARRISTATUS、集裝箱進出口信息底賬表K_ETL_MAINSTATU、箱信息回執底賬表K_ETL_CCCTNNORESPONSE、提單信息回執底賬表K_ETL_CCBLNORESPONSE，具體表見表3-1至表3-6。3-1集裝箱進出口信息底賬表K_ETL_MAINSTATU集裝箱進出口信息底賬表NameDataTypeCommentIDNUMBER主鍵CODEVESSELVARCHAR2(35)船名代碼VESSELVARCHAR2(35)英文船名VOYAGEVARCHAR2(35)航次IOVARCHAR2(1)進出口標識BLNOVARCHAR2(35)提單號BLNOSTATUSOURCEVARCHAR2(35)提單號狀態代碼BLNOCODESTATUVARCHAR2(35)暫不用BLNOSTATUVARCHAR2(250)暫不用BLNOSOURCEIDVARCHAR2(35)提單數據源關鍵字BLNOSTATUTIMEVARCHAR2(20)提單狀態時間BLNOMSGTIMEVARCHAR2(20)提單寫入時間CTNNOVARCHAR2(35)箱號CTNNOSTATUSOURCEVARCHAR2(35)箱號狀態代碼CTNNOCODESTATUVARCHAR2(35)暫不用CTNNOSTATUVARCHAR2(250)暫不用CTNNOSOURCEIDVARCHAR2(35)箱號數據源關鍵字CTNNOSTATUTIMEVARCHAR2(20)箱號狀態時間CTNNOMSGTIMEVARCHAR2(20)箱號寫入時間DATASOURCEVARCHAR2(35)數據來源CODECARGOVARCHAR2(35)貨代CODESHIPPERVARCHAR2(35)船代CODECUSTOMSBROKERVARCHAR2(35)報關行CTNMANAGERVARCHAR2(35)箱管BLNOWTNOVARCHAR2(35)提單委托號CTNNOWTNOVARCHAR2(35)箱委托號CTNYARDVARCHAR2(35)場站TRUCKNOVARCHAR2(35)車號3-2提單信息回執底賬表K_ETL_CCBLNORESPONSE提單信息回執底賬表NameDataTypeCommentCCRESPON_BLNO_INFO_IDNUMBER主鍵MSGTYPEVARCHAR2(10)報文類型FILEIDVARCHAR2(20)報文id（報文表主鍵）MESSAGEIDVARCHAR2(50)消息idEDI_USERNAMEVARCHAR2(25)用戶名IMOVARCHAR2(50)船識別碼VESSELVARCHAR2(35)英文船名VOYAGEVARCHAR2(10)航次BLNOVARCHAR2(35)提單號RESPON_TYPE_CODEVARCHAR2(10)回執類型代碼RESPON_TYPE_TEXTVARCHAR2(512)回執類型MSGTIMEVARCHAR2(14)報文時間3-3箱信息回執底賬表K_ETL_CCCTNNORESPONSE箱信息回執底賬表NameDataTypeCommentCCRESPON_CTN_INFO_IDNUMBER主鍵MSGTYPEVARCHAR2(10)報文類型FILEIDVARCHAR2(20)報文id（報文表主鍵）MESSAGEIDVARCHAR2(50)消息idEDI_USERNAMEVARCHAR2(25)用戶名IMOVARCHAR2(50)船標示號VESSELVARCHAR2(35)英文船名VOYAGEVARCHAR2(10)航次CTNNOVARCHAR2(35)箱號RESPON_TYPE_CODEVARCHAR2(10)回執類型代碼RESPON_TYPE_TEXTVARCHAR2(512)回執類型MSGTIMEVARCHAR2(14)報文時間3-4出口裝箱單表K_T_COSTCO出口裝箱單表NameDataTypeCommentSTATUVARCHAR2(10)“裝箱單-O”BLNOVARCHAR2(20)提單號CTNNOVARCHAR2(20)箱號BLNOSTATUTIMEVARCHAR2(30)操作時間CTNTYPEVARCHAR2(10)箱型（大小+類型）CARGOAGENTVARCHAR2(20)貨代CODECARGOAGENTVARCHAR2(20)貨代代碼3-5集裝箱裝卸船底賬表K_T_COARRISTATUS集裝箱裝卸船底賬表NameDataTypeCommentIMOVARCHAR2(50)船識別號CODEVESSELVARCHAR2(35)船名代碼VOYAGEVARCHAR2(35)航次BLNOVARCHAR2(35)提單號CTNNOVARCHAR2(35)箱號STATUSOURCEVARCHAR2(35)狀態代碼：裝LOAD/卸DISSOURCEIDNUMBER數據源關鍵字CTNYARDVARCHAR2(35)場站IOTIMEVARCHAR2(35)裝卸時間MSGTIMEDATE寫入/更新時間3-6中轉箱表K_T_ZZ中轉箱表NameDataTypeCommentCTNNOVARCHAR2(20)箱號CTNTYPEVARCHAR2(10)箱型（大下+類型）IOSIGNVARCHAR2(10)進出口標示BLNOVARCHAR2(20)提單號CODEDELIVERYVARCHAR2(70)中轉港代碼DELIVERYVARCHAR2(70)中轉港CTNNOSOURCEIDVARCHAR2(50)COARRI_PLCONINFO主鍵CTNNOSTATUTIMEVARCHAR2(20)操作時間3.3ETL設計3.3.1ETL分析場站進出門信息、入庫單信息、貨代報檢信息、訂艙數據信息、電子裝箱單信息、裝卸船信息等是需要抽取載入集裝箱進出口信息底賬表K_ETL_MAINSTATU表的，抽取的信息要能夠用箱號和提單號查詢各階段狀態。提單信息回執和箱信息回執分別抽取載入箱信息回執底賬表K_ETL_CCCTNNORESPONSE和提單信息回執底賬表K_ETL_CCBLNORESPONSE。這些與狀態相關的信息時在提取時要注意保留每條信息在原表中的主鍵即id，以便回溯查詢。集裝箱裝卸船底賬表、中轉箱表、出口裝箱單表載入的是主要是相關的箱型，方便統計箱量，因為TEU箱量計算時20的箱型算作1箱而40的箱型算作2個。3.3.2ETL設計抽取字段設計場站進出門信息場站進出門信息涉及到的表如圖3-1，分別是進出門信息表cogate_plconinfo、船期表pub_shipinfo、班輪信息表shipinfo、船信息表tran_vessel、報文信息表。表之間的關系也如圖所示。關聯這五張表，抽取船名代碼pub_shipinfo.Codevessel、英文船名tran_vessel.vessel_sh、航次pub_shipinfo.voyage、箱號cogate_plconinfo.ctnno、進出門時間cogate_plconinfo.gateouttime和cogate_plconinfo.gateintime。除了提取的信息還有需要添加的信息，即表示這是進出門的標識信息。將場站進出門信息導入集裝箱進出口信息底賬表K_ETL_MAINSTATU表的字段對應關系如表3-7，K_ETL_MAINSTATU表中沒有場站進出門信息的字段默認為空。3-7進出門信息抽取字段與表K_ETL_MAINSTATU字段對照表K_ETL_MAINSTATUcodevesselCODEVESSELvessel_shVESSELvoyageVOYAGE‘w’IO（I:進,O:出,W:全部)‘*’BLNOctnnoCTNNOdecode(a.gateouttime,null,'IN','OUT')CTNNOSTATUSOURCEplconinfoidCTNNOSOURCEIDdecode(a.gateouttime,null,gateintime)CTNNOSTATUTIMEto_char(sysdate,'YYYYMMDDHH24MISS')CTNNOMSGTIME‘COGATE'||decode(a.gateouttime,null,'IN','OUT')DATASOURCEcodesenderCTNYARDtrademarkTRUCKNO圖3-1場站進出門涉及的表及其關聯貨代報檢信息貨代報檢信息涉及到的表有報檢表new_agency_apply和船信息表tran_vessel如表3-8。將貨代報檢信息導入集裝箱進出口信息底賬表K_ETL_MAINSTATU表的字段對應關系如表3-9，K_ETL_MAINSTATU表中沒有貨代報檢信息的字段默認為空。表3-8報檢表new_agency_applyNameCommentDataTypeCHECKIDNUMBERCHECKNO報檢號VARCHAR2(20)CODEVESSEL船名碼VARCHAR2(20)VOYAGE航次VARCHAR2(10)BLNO提單號VARCHAR2(100)CTNNUM2020‘箱量NUMBERCTNNUM4040’箱量NUMBERCARGOCODE貨名VARCHAR2(200)CARGOFROMCODE來源地/輸出國家VARCHAR2(20)APPLYCOMPCODE錄單單位VARCHAR2(20)REPORTTIME報檢日期VARCHAR2(15)COMPNO報檢單位登記號VARCHAR2(20)LINKPHONE聯系電話VARCHAR2(20)LINKMAN聯系人VARCHAR2(20)ACCCN收貨人中文VARCHAR2(100)ACCEN收貨人英文VARCHAR2(100)SENDCN發貨人中文VARCHAR2(100)SENDEN發貨人英文VARCHAR2(100)TRANTYPE0轉關1過境2非法檢3法檢VARCHAR2(1)TRANSCHECK0轉檢1不轉檢VARCHAR2(1)CX0拆箱1不拆箱VARCHAR2(1)CXPLACE拆箱地點VARCHAR2(10)TRANSPLACE0省內1省外CHARAIMPLACE目的地VARCHAR2(20)PACKTYPE0無1木質包裝2其他VARCHAR2(1)ZJCS主檢處室VARCHAR2(5)SPECIALTYPE特殊情況0無1拼箱2分票VARCHAR2(1)SPECIALNUM分幾票NUMBERAPPLYUSER錄單人VARCHAR2(20)APPLYTIME錄單時間DATESPECIALNO特殊情況報檢號VARCHAR2(100)STATE0待申報1申報2已審核3檢疫處理4可放行5已放行6歸檔-1退回12已接單13港地互通申報VARCHAR2(2)ARRPLACE集裝箱存放地VARCHAR2(10)VESSELCN中文船名VARCHAR2(20)REVIEWUSER接單人VARCHAR2(20)REVIEWTIME接單時間DATEXD1消毒通知單CHARREASON退回原因VARCHAR2(100)SHIPID船代申報IDNUMBERHSCODE貨物HS編碼VARCHAR2(100)上傳文件名稱VARCHAR2(100)XD11箱體消毒CHARXD21預防性衛生處理CHARFXUSER放行人VARCHAR2(20)FXTIME放行時間DATECOMPCODE掛靠單位VARCHAR2(20)ZJFX1先放行2分流CHARZJREMARK1移到的地點VARCHAR2(30)ZJUSER特殊放行人VARCHAR2(20)ZJTIME特殊放行時間DATEZJREMARK2場站代碼VARCHAR2(20)XDSTATE1預防性消毒狀態1、待處理2已處理CHARXDSTATE2針對性消毒狀態1、待處理2已處理CHARAGENCYTYPE報檢類型：0，集裝箱；1，鐵路箱CHARINRETURN是否退運：0，否；1，是；（用于鐵路箱法檢）VARCHAR2(1)VGTYPE轉關類型：1，直關，2，區域通關（用于鐵路箱轉關）VARCHAR2(1)ARRDATE靠泊日期VARCHAR2(15)GDHT是否港地互通：1，是；0，否VARCHAR2(2)NATIVE_CUSTOMS本地報關海事代碼，默認連云港海關321200VARCHAR2(10)ACTUAL_CUSTOMS實際轉關海事代碼，默認連云港海關321200VARCHAR2(10)GDHT_WSCH_PLACE衛生除害地點：0，目的地檢驗檢疫處理單位；1，連云港VARCHAR2(2)GDHT_STATE港地互通國檢審批意見標識：0，同意；1，不同意VARCHAR2(2)GDHT_REMARK港地互通國檢審批意見VARCHAR2(200)表3-9貨代報檢信息抽取字段與表K_ETL_MAINSTATU字段對照表K_ETL_MAINSTATUcodevesselCODEVESSELvessel_shVESSELvoyageVOYAGE‘w’IO（I:進,O:出,W:全部)blnoBLNOstateBLNOSTATUSOURCEcheckidBLNOSOURCEIDdecode(fxtime,null,(decode(reviewtime,null,applytime,reviewtime)),fxtime)BLNOSTATUTIMEto_char(sysdate,'YYYYMMDDHH24MISS')BLNOMSGTIME'CIQAGENT'DATASOURCEAPPLYCOMPCODECODECARGOCHECKNOBLNOWTNO電子提箱信息電子提箱信息涉及表如圖3-2所示，圖也體現了表間關聯。在關聯各表后要排除新東方場站申請的，因為新東方碼頭申請的不屬于電子提箱范疇。抽取字段包括船名代碼tb_ctn_tkwt.Codevessel、英文船名tran_vessel.vessel_sh、航次tb_ctn_tkwt.voyage、提單號tb_ctn_tkwt.blno、申請時間tb_ctn_tkwt.applydate、貨代名tb_ctn_tkwt.a_cargo、貨代名tb_ctn_tkwt.a_ship、場站名tb_ctn_tkwt.codectnfield、狀態代碼tb_ctn_tkwt.wt_state。除了提取的信息還有需要添加的信息，即表示這是進出門的標識信息。將電子提箱信息導入集裝箱進出口信息底賬表K_ETL_MAINSTATU表的字段對應關系如表3-10，K_ETL_MAINSTATU表中沒有電子提箱信息的字段與之對應的默認為空。圖3-2電子提箱涉及的表及其關聯表3-10電子提箱信息抽取字段與表K_ETL_MAINSTATU字段對照表K_ETL_MAINSTATUcodevesselCODEVESSELvessel_shVESSELvoyageVOYAGE‘w’IO（I:進,O:出,W:全部)blnoBLNOwt_stateBLNOSTATUSOURCEtk_idBLNOSOURCEIDapplydateBLNOSTATUTIMEto_char(sysdate,'YYYYMMDDHH24MISS')BLNOMSGTIME'TIX'DATASOURCEa_cargoCODECARGOa_shipCTNMANEGERcodectnfieldCTNYARD(4)網上訂艙信息網上訂艙信息涉及表如圖3-3所示，圖也體現了表間關聯。在關聯各表后要排除是散貨和提單號為空的訂艙信息。將網上訂艙信息導入集裝箱進出口信息底賬表K_ETL_MAINSTATU表的字段對應關系如表3-11，K_ETL_MAINSTATU表中沒有電子提箱信息的字段與之對應的默認為空。圖3-3網上訂艙信息涉及的表及其關聯表3-11網上訂艙信息抽取字段與表K_ETL_MAINSTATU字段對照表K_ETL_MAINSTATU船代碼Codevessel_xdfCODEVESSEL英文船名vessel_enVESSEL航線voyageVOYAGE提單blnoBLNO系統時間to_char(sysdate,'YYYYMMDDHH24MISS')BLNOMSGTIME貨代代碼a_cargoCODECARGO場站名codectnfieldCTNYARD申請方代碼applyerCODECARGO船公司Ship_agencyCODESHIPPER箱號ctnnoCTNNO申請日期apply_dateBLNOSTATUTIME、CTNNOSTATUTIME源表主鍵recordidRECORDID‘WEBDC’DATASOURCE(5)裝卸船信息裝卸船信息涉及表如圖3-4所示，圖也體現了表間關聯。將裝卸船信息導入集裝箱進出口信息底賬表K_ETL_MAINSTATU表的字段對應關系如表3-12，K_ETL_MAINSTATU表中沒有裝卸船信息的字段與之對應的默認為空。圖3-4裝卸船信息涉及的表及其關聯表3-12裝卸船信息抽取字段與表K_ETL_MAINSTATU字段對照表船名代碼codevesselCODEVESSEL英文船名vessel_shVESSEL航次voyageVOYAGE'W'IO提單號blnoBLNO信息提取時間sysdateBLNOMSGTIME裝卸狀態CTNNOSTATUSOURCE源表主鍵plconinfoidCTNNOSOURCEID裝卸時間LOADORDISTIMECTINNOSTATUTIME'COARRI'||decode(,'LOADREPORT','LOAD','DIS')DATASOURCE場站codesenderCTNYARD(6)中轉箱信息提取統計中轉箱量所使用的中轉箱信息涉及到裝卸船信息表coarri_plconinfo，如表3-13。提取信息有箱號ctnno、箱型ctntype、進出口標志iosign、提單號blno、中轉港代碼codedelivery、中轉港delivery、源表主鍵plconinfoid、進出口時間LOADORDISTIME。提取后載入中轉箱表K_T_ZZ。3-13裝卸船信息表coarri_plconinfoNameCommentDataTypePLCONINFOIDNUMBER(9)SHIPID船idNUMBER(9)FILEID文件id，報文VARCHAR2(20)CTNNO箱號VARCHAR2(12)CTNTYPE箱型VARCHAR2(5)CTNOPERATORCODE箱主代碼VARCHAR2(15)CTNOPERATOR箱主VARCHAR2(35)CTNSTATUS箱狀態VARCHAR2(1)BLNO提單號VARCHAR2(40)SEALNO鉛封號VARCHAR2(16)BAYNO貝位VARCHAR2(10)CODEDISCHARGEPORT卸貨港代碼VARCHAR2(10)DISCHARGEPORT卸貨港VARCHAR2(35)CODELOADPORT裝貨港代碼VARCHAR2(10)LOADPORT裝貨港VARCHAR2(35)CODEDELIVERY中轉港代碼VARCHAR2(10)DELIVERY中轉港VARCHAR2(70)CTNNETWEIGHT箱凈重，（tareweight）VARCHAR2(15)IOSIGN進出口VARCHAR2(1)TEMPSET溫度設置VARCHAR2(10)LOADORDISTIMEVARCHAR2(14)MSGTIME報文時間VARCHAR2(14)ADD1VARCHAR2(35)ADD2VARCHAR2(35)ADD3VARCHAR2(35)CODESHIPPER船公司代碼VARCHAR2(15)SHIPPER船公司VARCHAR2(350)CODECONSIGNEEVARCHAR2(15)CONSIGNEE收貨人VARCHAR2(350)CODECARGO貨物代碼VARCHAR2(20)CARGODESC貨物描述VARCHAR2(75)TRAINNO火車車皮號VARCHAR2(20)GOODSBILLNO貨物提單號VARCHAR2(20)DEPARTMENTORDERVARCHAR2(20)BOARDORDERVARCHAR2(20)(7)出口裝箱單信息出口裝箱單涉及的表如圖3-5所示。由于出口裝箱單一個提單或箱可能因為反復修改重發導致以提單查詢時出現多條，這時就要在抽取時進行篩選，如果有多條選最后發送的那條進行提取信息。提取信息有提單號blno、箱號ctnno、寫單時間msgtime、箱型ctntype、貨代名real_name、貨代代碼codecargoagent，并添加標志位'裝箱單-O'。提取后載入出口裝箱單表K_T_COSTCO圖3-5出口裝箱單信息涉及的表及其關聯(8)進出口箱量信息進出口箱量信息所涉及的表與裝卸船信息一樣，即如圖3-4，只是用途不同所以提取字段不同。從中提取船標示號imo、船名代碼codevessel、航次voyage、提單blno、裝卸標識、裝卸時間loadordistime、系統時間sysdate、源表主鍵plconinfoid、場站codesender。提取后載入集裝箱裝卸船底賬表K_T_COARRISTATUS。(9)箱信息回執箱信息回執用到的表如圖3-6，表之間關聯也如圖所示。從中提取信息為源表地址ccrespon_ctn_info_id、報文類型msgtype、報文表主鍵ileid、消息IDmessageid、用戶名edi_username、船識別碼id_code、船名vessel、航次voyage、箱號ctnno、回執信息代碼respon_type_code、回執信息respon_type_text、回執時間msgtime。提取消息載入箱信息回執底賬表K_ETL_CCCTNNORESPONSE。圖3-6箱信息回執信息涉及的表及其關聯(10)提單信息回執箱信息回執用到的表如圖3-7，表之間關聯也如圖所示。從中提取信息為源表地址ccrespon_blno_info_id、報文類型msgtype、報文表主鍵ileid、消息IDmessageid、用戶名edi_username、船識別碼id_code、船名vessel、航次voyage、提單號blno、回執信息代碼respon_type_code、回執信息respon_type_text、回執時間msgtime。提取消息載入箱信息回執底賬表K_ETL_CCBLNORESPONSE。圖3-7提單信息回執所涉及的表及其關聯ETL增量更新設計在第一次數據從外界數據源抽取、轉換、加載到底賬表后，第二次、第三次等抽取數據時該從哪條數據接著抽取便是需要考慮的ETL增量問題。在第二章的2.1.1中已介紹過增量更新的方法有使用時間戳、使用觸發器和定時執行增量更新等方法。首先，抽取的場站進出門信息中的plconinfoid、箱信息回執信息中的ccrespon_ctn_info_id、提單回執中的ccrespon_blno_info_id，都是源表中的主鍵，且都是按信息添入源表的先后遞增的，所以可以用做時間戳。所以這三種信息的提取用時間戳。先取得底賬中有關自己信息的源表主鍵最大值，在到源表中提取主鍵大于這最大值的數據進行轉換、載入。其它的表可以用觸發器，也可以用定時增量。如果用觸發器法，要添加更新刪除記錄表，然后寫觸發器用來在源表更新和刪除時將其主鍵和操作類型記錄入更新刪除記錄表。在數據載入底賬時先執行更新、再執行刪除、最后執行插入，并且在完成了更新和刪除一條記錄后，要將其在更新刪除記錄表中的記錄刪除。但這種方法有個弊端，即修改了源數據庫，對源數據影響很大。若用定時執行增量更新，由于這些源表中都有記錄插入時的時間，所以可以另用數據庫或用戶名建立視圖，利用在視圖where語句中加入to_date(X,'YYYYMMDDHH24MISS')>SYSDATE-0.1先抽取比當前系統時間少0.1天的數據，然后每隔2小時從視圖中抽取數據。這樣就可以達到定時更新的效果。但這種方法也有個弊端，即只能向底賬插入數據和更新底賬數據，當源表中某條數據刪除時，底賬中的相應數據無法刪除，導致數據不準確。選擇用觸發器還是用視圖，就是在衡量是數據準確重要，還是保持不修改源表重要的原則重要。在開發底賬時，要盡量小的造成源表的修改，即對生產環境要做到零影響，也就是對源表做到只讀。這是條很重要的原則，所以我犧牲了數據準確性，選擇用創建視圖的定時執行增量更新方法來處理剩余信息的ETL增量更新問題。3.4查詢及統計功能設計3.4.1查詢和統計模塊貨代、船代一線跑業務的人員平時在辦理業務時，最關新的就是自己負責的提單或箱進行到何種狀態了，有沒有問題。而碼頭、船公司等的高層則關心的是每個月、每個季度或每年的進出口箱量、中轉箱量等問題。所以，設計功能模塊如圖3-8。查詢箱狀態查詢箱狀態查詢提單狀態進出口箱量統計中轉箱量統計出口裝箱單統計統計與決策支持系統查詢模塊統計模塊圖3-8功能模塊設計圖功能模塊的說明：查詢箱狀態：輸入箱號，查詢到的內容包括箱基本信息、網上訂艙信息、場站進出門信息、裝卸船信息、箱信息回執信息。查詢提單狀態：輸入提單號，顯示提單所含的箱的所有狀態、電子提箱狀態、報檢狀態、提單回執信息。進出口箱量統計：輸入年份，統計出新東方場站和東泰場站分別在該年份中每季度及一年的進口和出口箱量，并統計兩場站每季度及一年的進口和出口箱量總和。統計中轉箱量統計：輸入年份，統計該年份每一季度和一年的進口中轉箱和出口中轉箱，并統計進出口中轉量總和以及出口占總和的比率。出口裝箱單統計：一是按年份統計該年份每個月的TEU箱數和自然箱箱數，二是按年份時間段統計這幾個年份的年TEU箱數和自然箱數做比較，三是統計某年或某年某月各個貨代的TEU箱數和自然箱箱數。3.4.2登錄模塊登錄信息表JM_USER包含三列，分別是用戶名、密碼和類型。類型決定這用戶是只能使用查詢模塊，還是查詢模塊和統計模塊都能使用，流程圖如圖3-9。登錄登錄用戶名、密碼正確用戶對應類型為q顯示查詢和統計只顯示查詢圖3-9登錄模塊流程圖4港口貨運統計與決策支持系統的實現4.1ETL轉換的實現ETL是通過建立視圖和使用kettle工具實現的。其中進口艙單信息、船代報檢信息在本系統中并沒有使用到，但為了使底賬完整，對這兩部分數據也進行了ETL轉換，加入到了集裝箱進出口信息底賬表K_ETL_MAINSTATU中。4.1.1建立視圖為了達到對數據源只讀的效果，也為了方便etl增量更新的實現，建立視圖幫助數據抽取。例如抽取裝箱單信息的視圖為：createorreplaceviewv_t_costcoasselect'裝箱單-O'asstatu,blno,ctnno,msgtimeasblnostatutime,ctntype,real_nameascargoagentfrom(selecte.blno,c.ctnno,e.msgtime,row_number()over(partitionbye.blno,c.ctnnoorderbye.msgtimedesc)asrn,b.ctntype,f.real_nameFromservsite.l_costco_blnoa,msg.costco_cargoinfod,servsite.l_costco_ctnb,msg.costco_partplconinfoc,msg.costco_blnoe,servsite.loginfwherea.blno=e.blnoandb.ctnno=c.ctnnoandp_code=a.codecargoagentAndc.cargoid=d.cargoidandd.blid=e.blidandto_date(e.msgtime,'YYYYMMDDHH24MISS')>sysdate-0.1)aawherern=1;其中select后的列為需要抽取的字段。最外面一層的FROM中嵌套的select語句用到了分析函數，這是按提單號、箱號分組并在組內按錄入時間倒排序，即排在第一個的是這個提單、箱號的最新數據，在最后wherern=1便是只選擇了最新一條數據，將其他舊的無用信息排除，不抽取。to_date(e.msgtime,'YYYYMMDDHH24MISS')>sysdate-0.1這句，便是選擇了錄入時間大于電腦系統時間減去2.4小時所得時間的數據。最里層的from的表便是設計的數據源的表。最終共建視圖17個。4.1.2kettle程序轉換transformation場站進出門信息、箱信息回執信息、提單回執信息、進口艙單信息、裝卸船的轉換如圖4-1和圖4-2。這五個信息是使用時間戳作為ETL增量更新的方法的，所以第一步要在底賬中獲取最大id即時間戳，以提單信息回執為例，此步驟代碼為：SELECTDECODE(MAXID,NULL,'0',MAXID)ASMAXIDFROM(selectmax(ccrespon_blno_info_id)asmaxidfromk_etl_ccblnoresponse)。第二部根據第一步傳入的最大id從視圖中抽取信息，以提單信息回執為例，此步驟代碼為select*fromv_k_ccblnoresponsewhereccrespon_blno_info_id>?，問號即上一步傳來的參數。第三部將第二部抽取的數據載入底賬表，這里分兩種情況，一種像提單信息回執和箱信息回執這種只插入不跟新的情況，另一種是像進出門信息和進口艙單信息這種不僅要能插入而且要能更新的情況。第一種直接用表輸出組件即可；第二種需要用到插入/更新組件，以裝卸船為例，組件需要填寫的信息如圖4-3。圖4-1使用時間戳（1）圖4-2使用時間戳（2）圖4-3插入/更新組件填寫的信息使用定時執行增量更新的信息，轉換分為兩步，如圖4-4和圖4-5。第一步，從視圖抽取數據。第二部將數據載入底賬數據庫。圖4-4使用定時執行增量更新（1）圖4-5使用定時執行增量更新（2）作業jobJob啟動轉換，若轉換失敗則寫錯誤日志，并將日志發送至郵箱，這樣就可從郵箱監控錯誤消息，如圖4-6。由于之前將視圖的抽取條件設置為大于當前系統時間減去2.4小時，所以job的循環執行時間必須小于2.4小時，因此定位2小時，即每兩小時執行一次。圖4-6job流程連接數據庫轉換中的每一步都需要連接其用到的數據庫。新建數據庫連接如圖4-7。表輸入和表輸出連接的數據庫是不同的。圖4-7連接數據庫步驟圖4.2界面實現（1）登錄界面登錄見面如圖4-8。圖4-8登錄界面主界面登錄后進入主界面，如圖4-9。主界面一種是菜單欄里有查詢和統計兩項，還有一種沒有統計選項。點擊查詢或統計會有會有下拉菜單，可以點擊菜單中的項進入頁面，也可以用快捷方式打開。圖4-9主界面箱狀態查詢界面點擊查詢中的箱狀態查詢進入此界面，界面如圖4-10。圖4-10箱信息查詢界面由于箱號對于每種操作并不是唯一的，所以根據箱號選擇的基本信息時日期最近的一次的，其他狀態信息則是根據基本信息中的提單號和箱號同時匹配查詢到的，因為同一提單中箱號是唯一的。例如網上訂艙的查詢語句為："selectcaseBLNOSTATUSOURCEwhen'0'then'初始'when'1'then'船方確認'when'2'then'船方刪除'when'3'then'修改狀態'when'4'then'正式報文生成'else'貨代提交撤單申請'endasa,CODECARGO,CODESHIPPER,CTNNOSTATUTIMEfromK_ETL_MAINSTATUtwherectnnostatu='網上訂艙'andctnno='"+m+"'andblno='"+sle_2.text+"'"。其中m為輸入的箱號，sle_2.text即基本信息中的提單號。提單狀態查詢界面點擊提單狀態查詢進入此界面，如圖。白色的數據框顯示了這個提單中所含箱的信息。圖4-11提單狀態查詢界面中轉箱量統計點擊統計目錄下的中轉箱量統計進入界面，如圖。圖4-12中轉箱量統計界面進口TEU統計語句為：str_sql="selectdistinct'第1季度'as時間,decode((sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20,null,0,(sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20)as進口TEUfromK_T_ZZtwhereIOSIGN='I'andcodedelivery='CNLYG'andsubstr(CTNNOSTATUTIME,5,2)IN('01','02','03')andsubstr(CTNNOSTATUTIME,1,4)='"+m+"'"str_sql=str_sql+"unionselectdistinct'第2季度'as時間,decode((sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20,null,0,(sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20)as進口TEUfromK_T_ZZtwhereIOSIGN='I'andcodedelivery='CNLYG'andsubstr(CTNNOSTATUTIME,5,2)IN('04','05','06')andsubstr(CTNNOSTATUTIME,1,4)='"+m+"'"str_sql=str_sql+"unionselectdistinct'第3季度'as時間,decode((sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20,null,0,(sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20)as進口TEUfromK_T_ZZtwhereIOSIGN='I'andcodedelivery='CNLYG'andsubstr(CTNNOSTATUTIME,5,2)IN('07','08','09')andsubstr(CTNNOSTATUTIME,1,4)='"+m+"'"str_sql=str_sql+"unionselectdistinct'第4季度'as時間,decode((sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20,null,0,(sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20)as進口TEUfromK_T_ZZtwhereIOSIGN='I'andcodedelivery='CNLYG'andsubstr(CTNNOSTATUTIME,5,2)IN('10','11','12')andsubstr(CTNNOSTATUTIME,1,4)='"+m+"'"str_sql=str_sql+"unionselectdistinct'總計'as時間,decode((sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20,null,0,(sum(translate(ctntype,'0123456789'||ctntype,'0123456789')))/20)as進口TEUfromK_T_ZZtwhereIOSIGN='I'andcodedelivery='CNLYG'andsubstr(CTNNOSTATUTIME,1,4)='"+m+"'"其中m是輸入的年份。最終的str_sql就是進口TEU統計語句。由于箱型這個屬性中箱大小和箱類型是放在一起的，但箱大小是數字40或20，而箱類型是字母代碼，所以獲得箱大小只要提取其中數字部分即translate(ctntype,'0123456789'||ctntype,'0123456789'))。集裝箱的大小有20的小箱和40的大箱。TEU箱量將40的大箱記做兩個箱子，20的小箱幾座一個；自然箱箱量是實際有幾個箱子就算幾個。所以在算TEU箱量時先把所有箱大小值相加再除以20。出口裝箱單統計界面點擊統計目錄下的出口裝箱單統計進入界面，如圖4-13。圖4-13進出口箱單統計界面出口裝箱單統計界面不僅將新東方場站和東泰場站的出口裝箱量和進口卸箱量分別做了對比，而且各場站自己的進出口箱量也做了對比，整個連云港的進出口對比也很清楚。出口裝箱單統計界面點擊統計目錄下的出口裝箱單統計進入界面，如圖4-14。界面中的統計圖有三種顯示選擇，即柱狀圖、線圖、帶圖，缺省為柱狀圖。圖4-14出口裝箱單月箱量統計界面點擊年箱量統計，如圖4-15。圖4-15出口裝箱單年箱量統計界面點擊貨代箱量，如圖4-16。圖4-16出口裝箱單貨代箱量統計界面貨代箱量中可以按年統計，則填寫年份并且不寫月份；若要按月統計則出入年份和月份；若只輸入月份沒有輸入年份則會提示“請輸入年份”；若都沒有輸入就按確定則會提示“請輸入”。5總結與展望本文在連云港電子口岸網上訂艙、電子提箱、電子裝箱單、場站系統、理貨入庫單系統等諸多業務系統產生的數據的基礎上，通過對船代、貨代等系統使用方的需求的分析，針對ETL轉換技術進行研究，形成了底賬數據庫，設計實現了港口貨運統計與決策支持系統。具體來說，本文具體工作及其特色主要體現在以下幾個方面：（1）設計底賬數據庫，用以查詢和統計。（2）設計ETL轉換。根據需求確定要提取的字段，根據字段確定涉及的表，再關聯表作為數據源以便抽取需要的數據。根據抽取字段的特點設計ETL增量更新。（3）在底賬基礎上建立聯合查詢和箱量統計。通過分析以及對數據庫查詢語句的研究，成功應用oracl