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

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