短信調(diào)用系統(tǒng)的設(shè)計和實現(xiàn)

/短信調(diào)用系統(tǒng)的設(shè)計和實現(xiàn)摘要：隨著公司短信業(yè)務(wù)的增加，基于數(shù)據(jù)管理人員手工調(diào)用短信數(shù)據(jù)的模式已不再適用。因此公司確定開發(fā)一套《短信調(diào)用系統(tǒng)》，由業(yè)務(wù)人員自己操作，便利、快捷地進(jìn)行短信調(diào)用?！抖绦耪{(diào)用系統(tǒng)》的實施，成功地解決了公司面臨的問題。關(guān)鍵詞：短信調(diào)用；SQL性能優(yōu)化；大批量數(shù)據(jù)快速導(dǎo)出；快速去重中圖分類號：TP393文獻(xiàn)標(biāo)識碼：A文章編號：1009-3044(2011)20-4805-03隨著公司規(guī)模的不斷擴大，作為公司產(chǎn)品的數(shù)據(jù)的規(guī)模、信息的深度及廣度也隨之?dāng)U張，公司的數(shù)據(jù)庫管理人員擔(dān)當(dāng)著日益繁重的工作。1問題描述短信調(diào)用原來的工作方式是由短信運營人員將調(diào)用要求提交給數(shù)據(jù)管理人員，再由數(shù)據(jù)管理人員在PL/SQL中寫SQL代碼，將滿足條件的短信數(shù)據(jù)調(diào)出，交給短信運營人員。這種工作方式所產(chǎn)生的問題是：1）由于項目多，數(shù)據(jù)管理人員須要經(jīng)常加班。2）調(diào)用條件多，不規(guī)范，只要數(shù)據(jù)管理人員有一個條件漏掉，就會將數(shù)據(jù)調(diào)用條件弄錯，從而產(chǎn)生客戶投訴。在這種背景下為了削減數(shù)據(jù)管理人員工作量，滿足公司的大批量的短信項目調(diào)用需求，自主開發(fā)了短信調(diào)用系統(tǒng)。2系統(tǒng)總體設(shè)計系統(tǒng)流程如圖1所示。1）相關(guān)技術(shù)后臺數(shù)據(jù)庫：Oracle10g；前臺軟件：Delphi6.0；體系結(jié)構(gòu)：C/S；計算機語言：Sql、ObjectPascal。2）功能設(shè)置基本信息：記錄項目的基本信息，項目負(fù)責(zé)人，調(diào)用條件運作狀況：一個項目可能調(diào)幾批數(shù)據(jù)，按城市或條件，記錄每個批次的調(diào)條件和數(shù)量。去重功能：和以前運作過的項目數(shù)據(jù)進(jìn)行去重，以前已經(jīng)發(fā)過的數(shù)據(jù)要濾掉，由于是短信數(shù)據(jù)，所以手機號是沒有重復(fù)的，唯一的，自身沒重復(fù)。查詢數(shù)量：確定條件，查詢數(shù)據(jù)量保存數(shù)據(jù)：查詢好的數(shù)據(jù)保存到項目臨時表數(shù)據(jù)導(dǎo)出：項目數(shù)據(jù)導(dǎo)出為CSV文件，交給負(fù)責(zé)運營的人員數(shù)據(jù)回庫：將調(diào)出的數(shù)據(jù)寫入到項目數(shù)據(jù)表op_smsdata_auto中樣本提取：提取已運作好的項目樣本數(shù)據(jù)給客戶。3）實現(xiàn)要點調(diào)用條件的確定；調(diào)用小表的生成；查詢數(shù)據(jù)量的函數(shù)；保存數(shù)據(jù)的存儲過程；數(shù)據(jù)導(dǎo)出程序；寫入項目數(shù)據(jù)表的存儲過程。3系統(tǒng)功能實現(xiàn)1）后臺表設(shè)計短信調(diào)用項目表：op_projinfo(projid項目流水號，projname項目名稱，projdate項目日期,principalid項目負(fù)責(zé)人ID，princalname項目負(fù)責(zé)人，princalname希望數(shù)量，deliverdesire調(diào)用條件，realsendqty實際調(diào)出數(shù)量)；一次數(shù)據(jù)調(diào)用對應(yīng)一個項目。項目批次表：op_smsproj(smsprojid批次流水號，sendbatch批次序號，projid項目ID，createdate創(chuàng)建日期，removeproj去重ID，realcustomercount實際調(diào)用數(shù)量，transferflag導(dǎo)出標(biāo)記)；一個項目可能對應(yīng)幾個批次數(shù)據(jù)。調(diào)表條件表：op_smsremark(smsremarkid記錄流水號，projid項目ID，batchid調(diào)用批次序號，parameters查詢參數(shù)，querystring查詢SQL，demand查詢要求，lastqty調(diào)出數(shù)量，removedupprojlist去重項目，fieldorrelation或關(guān)系字符串)；記錄每次查詢和調(diào)用的條件，以備查詢。項目數(shù)據(jù)表：op_smsdata(projid項目ID，customerid客戶ID，mobile手機，importbatch數(shù)據(jù)批次)；記錄調(diào)出數(shù)據(jù)。2）確定調(diào)用條件為基本信息，車主信息，樓盤信息三大類，具體條件如圖2所示。3）調(diào)用小表的生成公司數(shù)據(jù)的狀況是全部手機數(shù)據(jù)都在客戶數(shù)據(jù)表b2c_customer中，這張表很大，有1億多條記錄，另外還有兩個相關(guān)聯(lián)的樓盤表b2c_building和車主表b2c_carexpens?？蛻舯硎前闯鞘袇^(qū)號進(jìn)行分區(qū)的，人工調(diào)取數(shù)據(jù)時會依據(jù)分區(qū)進(jìn)行查詢和調(diào)用，會依據(jù)條件和樓盤表、車主表連查，會做很多臨時表以便提高速度，削減調(diào)用時間。這三張表都會經(jīng)常做增加，更新操作。依據(jù)這種狀況，將客戶數(shù)據(jù)表按區(qū)號生成328個調(diào)用小表，同時將車主信息整合到查詢小表中，因為樓盤表數(shù)據(jù)量不大，兩表連查時并不耗費時間，所以就沒將樓盤表整到調(diào)用小表中。生成調(diào)用小表的的存儲過程是B2c_Query_Recreate，其功能按區(qū)號循環(huán)生成調(diào)用基礎(chǔ)表，其關(guān)鍵代碼如下：executeimmediate'createtable'||v_tablename||'as(select*from(selecta.customerid,marrydate,name,idcard,a.areacode,mobile,expenseability,sex,birthyear,educationlevel,buildingid,district,mobilerank,invalid,reach,incorrect,road,mobilecity,address,postcode,location,bizpost,bizbuildingid,email,blacklist,addressstatus,mobilestatus,addressrank,salary,duty,adddate,lastcleandate,casewhenb.price>=0then1elsenullendis_car,b.carbrand,b.smallbrand,b.cartype,b.expensedate,b.price,b.licenseplatefromb2c_customera,b2c_carexpensebwherea.areacode='''||v_areacode||'''anda.customerid=b.customerid(+)orderbydbms_random.value))';v_tablename須要生成的調(diào)用小表表名b2c_customer客戶數(shù)據(jù)表b2c_carexpense車主表v_areacode城市區(qū)號變量基礎(chǔ)表生成好之后，按mobile手機進(jìn)行去重將調(diào)用小表中符合調(diào)用短信條件的customerid寫入到表B2C_QUERY_TEMP中executeimmediate'createtableB2C_QUERY_TEMPas(selectcustomeridfrom(selecta.*,ROW_NUMBER()OVER(PARTITIONBYmobileORDERBYcasewhenmobilerank=''R''then1whenmobilerank=''R-''then2whenmobilerank=''O''then3whenmobilerank=''O-''then4whenmobilerank=''A''then5whenmobilerank=''D''then6whenmobilerankisnullthen7else99endasc)ASrrrfrom(selectcustomerid,mobile,mobilerankfrom'||v_tablename||'awherenotexists(select''x''fromdm2.b2c_backlist_sms@rwdatawheremobile=a.mobile)andmobileisnotnulland(invalidin(''A'',''B'',''C'')orinvalidisnull)andblacklistisnullandmobilecity='''||v_city||''')a)whererrr=1)';將表v_tablename中的customerid存在于表B2C_QUERY_TEMP表中的記錄的sms設(shè)置為1，這樣經(jīng)過更新后的數(shù)據(jù)就是能夠進(jìn)行短信的去重過的全部數(shù)據(jù)了。調(diào)用小表建立好之后，在經(jīng)常查詢的字段增加索引，因為小表不做插入，更新動作，并且消費實力只有幾種類型，適合建立BITMAP索引，例如在EXPENSEABILITY上建立BITMAP索引，代碼如下：executeimmediate'createbitmapindexIDX_B2C_'||v_areacode||'_EXPENSEABILITYon'||v_tablename||'(EXPENSEABILITY)tablespaceTBS_INDEX_CDMQ'；年齡，性別，行政區(qū)也適合建立BITMAP索引，而在手機字段上則建立一般索引。建立調(diào)用小表的優(yōu)點是數(shù)量小，查詢、調(diào)用響應(yīng)快，時間短！缺點是由客戶數(shù)據(jù)表b2c_customer這張大表生成的，信息有一個延遲，大約一個星期左右，短信業(yè)務(wù)要求數(shù)據(jù)量大，快速，信息延遲一個星期，這對業(yè)務(wù)沒有影響。4）查詢數(shù)據(jù)量函數(shù)Sms_QuerySms_Query有兩個輸入?yún)?shù)Condition和P_projid，Condition是前臺生成的條件字符串，P_projid是存放須要去重的projid，之前的調(diào)用小表按sms=1查詢就可以達(dá)到項目數(shù)據(jù)的手機不重復(fù)的效果。條件字符串簡潔實例：Condition：198440,23|2|廣州|||奢侈品消費,高消費|||越秀區(qū),天河區(qū)|||||||||||||||||||198440代表本次項目的ID23代表當(dāng)前操作用戶ID2表示是批次數(shù)據(jù)廣州表示數(shù)據(jù)的城市奢侈品消費,高消費要求的消費實力越秀區(qū),天河區(qū)要求的行政區(qū)P_projid：198590去重項目ID為198590，將項目198590的數(shù)據(jù)寫入到去重表SMS_QC_198440_23后臺生成的查詢SQL為57296|selectcount(*)fromb2c_query_020awhere1=1and(expenseabilityin('奢侈品消費','高消費'))and(a.districtin('越秀區(qū)','天河區(qū)'))and(sms=1)andnotexists(select'x'fromSMS_198440_23wheremobile=a.mobile)andnotexists(select'x'fromSMS_QC_198440_23wheremobile=a.mobile)從而得到符合條件的數(shù)據(jù)量。顯示界面如圖3所示。圖35）保存符合查詢條件數(shù)據(jù)的存儲過程Sms_ArrangeSms_Arrange有3個參數(shù)Condition和P_projid，和查詢函數(shù)不同的是，它還多了一個Want_num參數(shù)，這個參數(shù)限制想到保存的數(shù)量，當(dāng)然它的值不超過查詢的最大值，假如超過則認(rèn)為是查詢最大值。和Sms_Query相同的是它也生成一個SQL代碼并執(zhí)行，例如上例條件執(zhí)行，并希望保存10000條，所得到的關(guān)鍵代碼為：InsertintoSMS_198440_23selectcustomerid,mobile,’2’databatchfromb2