使用SQL語(yǔ)句直接高效地訪(fǎng)問(wèn)文本文件.doc

“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”使用SQL語(yǔ)句直接高效地訪(fǎng)問(wèn)文本文件馬根峰( 廣東聯(lián)合電子服務(wù)股份有限公司, 廣州 510300)摘要 用SQL語(yǔ)句來(lái)直接訪(fǎng)問(wèn)文本文件？是在做夢(mèng)嗎？本文詳細(xì)地介紹了“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”，中文版本DB 查詢(xún)分析器、英文版本DB Query Analyzer在 文本文件 處理方面非常強(qiáng)大的功能，你可以直接用SQL語(yǔ)句來(lái)訪(fǎng)問(wèn)這些文本文件，訪(fǎng)問(wèn)250萬(wàn)條記錄的文件的復(fù)雜的關(guān)聯(lián)操作，也不過(guò)用時(shí)59秒鐘。需要注意的是，文本文件的第一行需要有列名。關(guān)鍵詞 DB 查詢(xún)分析器；DB Query Analyzer 1 引言 中國(guó)本土程序員馬根峰推出的個(gè)人作品-萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器，中文版本DB 查詢(xún)分析器、英文版本DB Query Analyzer。萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器集哈希技術(shù)、鏈表等多種數(shù)據(jù)結(jié)構(gòu)于一體，使用先進(jìn)系統(tǒng)開(kāi)發(fā)技術(shù)，經(jīng)歷4年的研究、開(kāi)發(fā)、測(cè)試周期后在2006年面世。之后7年來(lái)一直在進(jìn)行不斷地完善、升級(jí)，到目前為止，最新版本為5.04 ?！叭f(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”核心部分就具有長(zhǎng)達(dá)5萬(wàn)多行代碼的工作量，使得其具有強(qiáng)大的功能、友好的操作界面、良好的操作性、跨越各種數(shù)據(jù)庫(kù)平臺(tái)乃至于EXCEL和文本文件。你可以通過(guò)它查詢(xún)ODBC數(shù)據(jù)源（包括世面上所有的數(shù)據(jù)庫(kù)、TXT/CSV文件、EXCEL文件）的數(shù)據(jù)。你可以同時(shí)執(zhí)行多條DML語(yǔ)句乃至存貯過(guò)程，結(jié)果會(huì)以你設(shè)定的表格、文本框、文件來(lái)返回。從數(shù)據(jù)庫(kù)導(dǎo)出千萬(wàn)條數(shù)據(jù)時(shí)，效率與DBMS沒(méi)有什么區(qū)別。本文將以5.04版本為例，詳細(xì)闡述“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”中文版本DB 查詢(xún)分析器在 文本文件 處理方面非常強(qiáng)大的功能，你可以直接用SQL語(yǔ)句來(lái)訪(fǎng)問(wèn)這些文本文件，訪(fǎng)問(wèn)250萬(wàn)條記錄的文件的復(fù)雜的關(guān)聯(lián)操作，也不過(guò)用時(shí)59秒鐘。要注意的是，文本文件的第一行需要有列名。2 產(chǎn)品獲得的成就及發(fā)展歷程 中文版本DB 查詢(xún)分析器在中關(guān)村在線(xiàn) 下載量超過(guò)10萬(wàn) 多次，位居整個(gè)數(shù)據(jù)庫(kù)類(lèi)排行榜中前20位。在程序員2007第2期的“新產(chǎn)品&工具點(diǎn)評(píng)”部分，編輯“特別推薦”了“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”發(fā)布。本期只點(diǎn)評(píng)了5個(gè)工具，分別是“Adobe Acrobat 8 中文版”、“迅雷搜索 1.7 新版上線(xiàn)”、“Google 桌面搜索 5.0 中文發(fā)布”、“BEA 發(fā)布 WebLogic SIP Server 3.0”和特別推薦“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”發(fā)布。前面4個(gè)都是國(guó)內(nèi)外大型軟件公司的產(chǎn)品，只有“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”是個(gè)人創(chuàng)作的軟件。截止到2013年4月17日，在Baidu上搜索關(guān)鍵字萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器，搜索結(jié)果達(dá)318萬(wàn)。在Baidu上搜索關(guān)鍵字DB 查詢(xún)分析器、DB Query Analyzer，搜索結(jié)果分別在104萬(wàn)、16萬(wàn)左右；在Google上搜索“DB 查詢(xún)分析器”、“DB Query Analyzer”，結(jié)果分別達(dá)104萬(wàn)、44萬(wàn)之多。本人撰寫(xiě)了關(guān)于“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”有關(guān)技術(shù)的64篇文章，發(fā)布在電腦編程技巧與維護(hù)、軟件、計(jì)算機(jī)時(shí)代、電腦編程技巧與維護(hù)、百度文庫(kù)、CSDN資源、和本人的四大博客上（CSDN博客、新浪博客、QQ空間和搜狐博客上）。3 為何用DB 查詢(xún)分析器來(lái)訪(fǎng)問(wèn)文本文件來(lái)解決實(shí)際問(wèn)題在廣東聯(lián)合電子服務(wù)股份有限公司實(shí)施一張網(wǎng)的過(guò)程中，由于路段上傳的流水存在大量的異常，導(dǎo)致按照正常的結(jié)算流程無(wú)法快速地進(jìn)行路段的結(jié)算。因此，為了進(jìn)行及時(shí)的結(jié)算，廣東省高速公路公司授權(quán)先將異常流水進(jìn)行忽略，后期再將這些流水進(jìn)行上傳、修改、拆分結(jié)算。最近，為了實(shí)施忽略流水的回傳，本人做的第一步就是從忽略的流水中按照（流水只有在管理點(diǎn)存在、中心和管理點(diǎn)都存在）這兩種情況，再按照流水金額小于0、等于0、大于0三類(lèi)進(jìn)行統(tǒng)計(jì)成臺(tái)賬（6個(gè)文件），然后業(yè)務(wù)組，再?gòu)倪@些分類(lèi)批次（6個(gè)文件）中確認(rèn)哪些批次需要回傳（形成2 個(gè)文件， 流水大于0.csv 和 流水小于0.csv ）。最終，本人還要根據(jù)這些這兩個(gè)文件中是否“回傳”，再?gòu)慕Y(jié)算系統(tǒng)中根據(jù)復(fù)雜的邏輯，找出要回傳的流水號(hào)并按照區(qū)域分成4個(gè)文件。在本次處理中，源處理文件：流水大于0.csv 、 流水小于0.csv 、tb_road、 管理點(diǎn)存在中心不存在的流水_所有.txt 和 管理點(diǎn)與中心都存在的部分_所有.txt 這5個(gè)文件。后兩個(gè)文件是從結(jié)算系統(tǒng)數(shù)據(jù)庫(kù)服務(wù)器中通過(guò)復(fù)雜的邏輯處理生成的中間結(jié)果文件。輸出結(jié)果：要回傳的流水號(hào)并按照區(qū)域分成4個(gè)文件。解決方案：因此，輸入條件決定要么在結(jié)算數(shù)據(jù)庫(kù)系統(tǒng)中建立 數(shù)據(jù)表 來(lái)完成條件的判斷；要么將結(jié)果全部忽略的流水、需要關(guān)聯(lián)的數(shù)據(jù)表（tb_road）導(dǎo)出成 .CSV/.TXT 文件，再使用DB 查詢(xún)分析器強(qiáng)大、高效的 .CSV/.TXT 訪(fǎng)問(wèn)功能來(lái)實(shí)施整個(gè)回傳流水文件的生成。 tb_road 表只是從生產(chǎn)機(jī)數(shù)據(jù)庫(kù)中導(dǎo)出tb_road表就行了。圖2 數(shù)據(jù)挖掘生成文本文件“管理點(diǎn)與中心都存在部分_所有.txt”4 5.04中文版本DB 查詢(xún)分析器為例 下面我們就以“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”的中文版本DB 查詢(xún)分析器 5.04為例，以Windows 2000 Server操作系統(tǒng)為平臺(tái)，先創(chuàng)建基于目錄 “D:ODBC_TXT_CSV”中 .TXT/.CSV 文件的ODBC數(shù)據(jù)源 “odbc_txt_csv”，然后再通過(guò) DB 查詢(xún)分析器 5.04來(lái)訪(fǎng)問(wèn)這些目錄下的文件。 操作系統(tǒng)： Windows 2000 Server操作系統(tǒng) CPU：2.8 GHZ 單核內(nèi)存：1GB 前臺(tái)程序： DB 查詢(xún)分析器 5.04 后臺(tái)文件： .TXT/.CSV圖3 創(chuàng)建基于 .CSV/.TXT 文件的ODBC數(shù)據(jù)源（一）圖4 創(chuàng)建基于 .CSV/.TXT 文件的ODBC數(shù)據(jù)源（二）圖5 登錄odbc_txt_csv，不用輸入用戶(hù)名和口令圖6 對(duì)象瀏覽器來(lái)查看數(shù)據(jù)源 odbc_txt_csv圖7 Windows資源管理器中，ODBC數(shù)據(jù)源odbc_txt_csv對(duì)應(yīng)的目錄下所有的文件圖8 用SQL語(yǔ)句來(lái)訪(fǎng)問(wèn) .txt和 .csv文件，來(lái)生成各區(qū)域需要回傳的流水圖8中的復(fù)雜的SQL語(yǔ)句如下：select listno,2014-06-16from ( select distinct b.LISTNO,b.roadno -select count(*) as rec_num,sum(b.CASHMONEY)/100 as CASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY from ( select w.roadno,w.squaddate,w.roadname,w.outvehclass, sum(w.rec_count) as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) as etcmoney1 from ( select * FROM 流水大于0.csv where (備注 is null) and 回送標(biāo)志=回送 and (cashmoney+etcmoney)0 union all select * FROM 流水小于0.csv where (備注 is null) and 回送標(biāo)志=回送 and (cashmoney+etcmoney)0 ) w group by w.roadno,w.squaddate,w.roadname,w.outvehclass ) a,管理點(diǎn)與中心都存在部分_所有.txt b where ( (b.CASHMONEY+b.ETCMONEY)0 union all select * FROM 流水小于0.csv where (備注 is null) and 回送標(biāo)志=回送 and (cashmoney+etcmoney)0 ) w group by w.roadno,w.squaddate,w.roadname,w.outvehclass ) a,管理點(diǎn)與中心都存在部分_所有.txt b where ( (b.CASHMONEY+b.ETCMONEY)0 ) and a.roadno=b.roadno and a.SQUADDATE=b.SQUADDATE and a.OUTVEHCLASS=b.OUTVEHCLASSNAME union select distinct b.LISTNO,b.roadno -select count(*) as rec_num,sum(b.CASHMONEY)/100 as CASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY from ( select w.roadno,w.squaddate,w.roadname,w.outvehclass, sum(w.rec_count) as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) as etcmoney1 from ( select * FROM 流水大于0.csv where (備注=只管理點(diǎn)存在) and 回送標(biāo)志=回送 and (cashmoney+etcmoney)0 union all select * FROM 流水小于0.csv where (備注=只管理點(diǎn)存在) and 回送標(biāo)志=回送 and (cashmoney+etcmoney)0 ) w group by w.roadno,w.squaddate,w.roadname,w.outvehclass ) a,管理點(diǎn)存在中心不存在的流水_所有.txt b where ( (b.CASHMONEY+b.ETCMONEY)0 union all select * FROM 流水小于0.csv where (備注=只管理點(diǎn)存在) and 回送標(biāo)志=回送 and (cashmoney+etcmoney)0 ) w group by w.roadno,w.squaddate,w.roadname,w.outvehclass ) a,管理點(diǎn)存在中心不存在的流水_所有.txt b where ( (b.CASHMONEY+b.ETCMONEY)0 ) and a.roadno=b.roadno and a.SQUADDATE=b.SQUADDATE and a.OUTVEHCLASS=b.OUTVEHCLASSNAME ) www where roadno in (select roadno from tb_road.txt where areano=4407) 圖9 一開(kāi)始執(zhí)行圖8中的SQL語(yǔ)句時(shí)，本機(jī)CPU使用率立刻攀升至97%圖10 生成中片區(qū)需要回傳的流水文件，共有6萬(wàn)多條記錄圖11 從結(jié)算中心數(shù)據(jù)庫(kù)中用挖掘生成的所有忽略的流水，兩個(gè)文件共250多萬(wàn)條記錄結(jié)論： 對(duì)于“交互式”這種數(shù)據(jù)處理的方式來(lái)說(shuō)，將結(jié)果導(dǎo)出，用DB 查詢(xún)分析器的強(qiáng)大、高效的文本文件的處理功能來(lái)分析處理，也是一種不錯(cuò)的選擇。并且DB 查詢(xún)分析器的效率非常之高、處理非常方便，可以將源文件當(dāng)成一個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)表一樣用標(biāo)準(zhǔn)SQL語(yǔ)句來(lái)進(jìn)行訪(fǎng)問(wèn)。 本人的PC機(jī)只不過(guò)內(nèi)存1GB的2005年的DELL 臺(tái)式PC機(jī)，對(duì)250萬(wàn)條記錄的文件進(jìn)行關(guān)聯(lián)訪(fǎng)問(wèn)的時(shí)候，也只是用不到59秒的時(shí)間就生成了一個(gè)區(qū)域的結(jié)果文件，期間CPU使用率高達(dá)97% 。5 本人撰寫(xiě)的關(guān)于“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”的64 篇技術(shù)文章 目前以下6篇文章發(fā)布幾個(gè)國(guó)內(nèi)計(jì)算機(jī)刊物上：1 馬根峰 DB Query Analyzer中斷SQL語(yǔ)句的執(zhí)行 杭州：計(jì)算機(jī)時(shí)代，2011年第12期2 馬根峰 DB 查詢(xún)分析器 批量執(zhí)行DML語(yǔ)句并返回更詳細(xì)的信息 北京：電腦編程技巧與維護(hù)，2011年第24期3 馬根峰 DB Query Analyzer中的事務(wù)管理在DB2中的應(yīng)用 北京：電腦編程技巧與維護(hù)，2011年第22期4 馬根峰 DB 查詢(xún)分析器中斷SQL語(yǔ)句的執(zhí)行 天津：軟件，2011年第6期5 馬根峰 萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器中的事務(wù)管理在Oracle中的應(yīng)用 上海：微型電腦應(yīng)用，2011年第11期 6 馬根峰 新產(chǎn)品&工具點(diǎn)評(píng) 特別推薦：“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”發(fā)布 程序員，2007年2期以下58篇文章發(fā)布在百度文庫(kù)、CSDN資源、和本人的四大博客上：萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器使用技巧之（一）直到萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器使用技巧之（十三）共13篇The 1st tip of DB Query Analyze直到The 13th skills of DB Query Analyzer共13篇如何在客戶(hù)端配置ODBC來(lái)訪(fǎng)問(wèn)遠(yuǎn)程DB2 for Windows服務(wù)器How to configure ODBC DSN in Client to access remote DB2 for Windows如何在服務(wù)器上配置ODBC來(lái)訪(fǎng)問(wèn)本機(jī)DB2 for Windows服務(wù)器How to configure ODBC DSN to access local DB2 for Windows軟件開(kāi)發(fā)頂尖高手的殺手锏SQL語(yǔ)句Which SQL statement is the trump card to the senior software developerDB 查詢(xún)分析器批量執(zhí)行DML語(yǔ)句并返回更詳細(xì)的信息用“萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器”測(cè)試SQLite對(duì)4大SQL精髓語(yǔ)句的支持用“DB查詢(xún)分析器”的對(duì)象瀏覽器來(lái)展現(xiàn)數(shù)據(jù)庫(kù)的數(shù)據(jù)字典萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器用戶(hù)已基本涵蓋當(dāng)前所有數(shù)據(jù)庫(kù)系統(tǒng)The DBMS that DB Query Analyzer Users often use cover all kinds of DBMS查詢(xún)分析器應(yīng)必須具備的功能-中斷查詢(xún)的執(zhí)行萬(wàn)能數(shù)據(jù)庫(kù)查詢(xún)分析器的EXE文件加殼技術(shù)Exe packer prevent DB Query Ana