SQL生成器設(shè)計與實現(xiàn)

曹樹貴王玉恒盧志舟高振華劉娟

摘 要：針對高考成績統(tǒng)計分析個性化需求多、對工作人員信息處理能力要求高等問題，設(shè)計并實現(xiàn)了面向高考成績統(tǒng)計分析的SQL生成器，用統(tǒng)計對象、統(tǒng)計屬性、維度表達式、統(tǒng)計函數(shù)四元素組成統(tǒng)計單元描述用戶分析需求，并映射為數(shù)據(jù)庫對象。按照算法流程分別生成SQL語句的select子句、from子句和where子句，最終合并為可用于統(tǒng)計分析的完整SQL語句，實現(xiàn)高考成績統(tǒng)計分析可定制化。

關(guān)鍵詞關(guān)鍵詞：高考成績；統(tǒng)計分析；統(tǒng)計單元；SQL生成器

DOIDOI：10.11907/rjdk.162034

中圖分類號：TP319

文獻標識碼：A 文章編號文章編號：16727800（2016）011008803

0 引言

2010年以來，河北省教育考試院利用現(xiàn)代信息技術(shù)和獨有的數(shù)據(jù)資源優(yōu)勢，推出了以診脈高考、服務(wù)教學(xué)為目標的高考數(shù)據(jù)統(tǒng)計分析系列報告[1]，報告采用程序計算和人工生成結(jié)合的方式，對于常規(guī)統(tǒng)計分析，采用固定的計算機程序完成，對于個性化需求則采用手工方式完成，工作量較大，報告生成周期較長。例如一份學(xué)校版的報告往往需要幾天的時間才能完成，而學(xué)校對報告的需求時間比較集中，短期內(nèi)很難滿足眾多學(xué)校需求；另外教育工作者通常不具備專業(yè)信息處理能力，在高考數(shù)據(jù)的檢索、統(tǒng)計等方面存在很大困難。

為解決以上問題，提出一種根據(jù)用戶自定義需求自動生成SQL（Structured Query Language）語句的高考成績分析方案，即SQL生成器設(shè)計方案。常見的SQL生成器一般嵌入在軟件開發(fā)工具或數(shù)據(jù)庫系統(tǒng)軟件中，如軟件開發(fā)工具PowerBuilder、C++ Builder、Delphi，數(shù)據(jù)庫系統(tǒng)軟件Access、VFP 、Microsoft SQL Server、Oracle，BI設(shè)計軟件 Microstrategy、Business Object、Cognos等都內(nèi)嵌有SQL生成器[34]，獨立的SQL生成器軟件有Red Earth Technologies的Superior SQL Builder、SBS Development的Automated_SQL_Builde等[5]。這些SQL生成器都是面向軟件開發(fā)或數(shù)據(jù)庫管理等專業(yè)技術(shù)人員，而不是面向業(yè)務(wù)系統(tǒng)的最終用戶。一些學(xué)者嘗試SQL生成器研究，如李海龍[6]的通用標準SQL語法分析模型研究，程曉燕[7]的可視化編輯數(shù)據(jù)庫操作工具研究，趙丹明[8]的模擬SQL Builder創(chuàng)建SQL語句的關(guān)鍵技術(shù)研究等。

1 SQL生成器數(shù)據(jù)庫設(shè)計

1.1 相關(guān)概念

（1）統(tǒng)計對象。統(tǒng)計分析所針對的實體稱之為統(tǒng)計對象，如“考生信息”、“總成績信息”、“單科成績信息”等都屬于統(tǒng)計對象。

（2）維度。數(shù)據(jù)統(tǒng)計分析是從特定角度對特定范圍數(shù)據(jù)的觀察、對比，這些特定角度稱之為維度，例如，“考試年份”、“行政區(qū)劃”、“考試科目”、“總成績”等屬于不同的維度。

（3）統(tǒng)計屬性。統(tǒng)計屬性是指計算所針對的統(tǒng)計對象屬性。如統(tǒng)計對象“總成績信息”的“總分”屬性，可用于求平均值、標準差、最大值、最小值等計算。

（4）統(tǒng)計函數(shù)。統(tǒng)計函數(shù)是作用于統(tǒng)計屬性上的集合運算，如計數(shù)、平均值、標準差、最高分、最低分等。

（5）統(tǒng)計單元。統(tǒng)計單元是由統(tǒng)計對象、統(tǒng)計屬性、維度表達式、統(tǒng)計函數(shù)4個基本元素構(gòu)成的元組，代表一次特定計算。

統(tǒng)計單元及組成元素需要映射為機器世界的對象，才能進一步采用程序進行處理。這里采用關(guān)系型數(shù)據(jù)庫的對象——表（Table）對統(tǒng)計單元及組成元素進行描述及數(shù)據(jù)存儲，實現(xiàn)概念到機器的映射，映射關(guān)系如圖1所示。

1.2 相關(guān)數(shù)據(jù)庫表

（1） 統(tǒng)計對象數(shù)據(jù)表。每個統(tǒng)計對象可映射為一個數(shù)據(jù)庫表（Table），數(shù)據(jù)庫表存儲統(tǒng)計對象數(shù)據(jù)。例如“KSXX”表存儲統(tǒng)計對象——“考生信息”數(shù)據(jù)，“ZCJ”表存儲統(tǒng)計對象——“總成績信息”數(shù)據(jù)。

（2） 統(tǒng)計對象定義表。統(tǒng)計對象定義表用于描述統(tǒng)計對象和數(shù)據(jù)庫表之間的對應(yīng)關(guān)系，由此表可以把統(tǒng)計對象名稱轉(zhuǎn)換為數(shù)據(jù)庫表名稱，表1為該表數(shù)據(jù)示例。

（3） 統(tǒng)計對象關(guān)系表。各統(tǒng)計對象數(shù)據(jù)表之間存在一定的關(guān)聯(lián)關(guān)系，如KSXX（考生信息表）、ZCJ（總成績信息表）和kh（考號）等為關(guān)聯(lián)條件，即KSXX.kh=ZCJ.kh。系統(tǒng)在生成SQL語句時需要根據(jù)表之間的關(guān)系構(gòu)建連接字符串，從而形成多表查詢語句，因此需要對表之間的關(guān)聯(lián)關(guān)系進行定義。表2為統(tǒng)計對象關(guān)系表記錄示例。

（4） 統(tǒng)計屬性定義表。每個統(tǒng)計屬性都對應(yīng)統(tǒng)計對象數(shù)據(jù)表中的一個可計算字段，在數(shù)據(jù)庫中用一張數(shù)據(jù)表描述各統(tǒng)計屬性與統(tǒng)計對象及表中字段的對應(yīng)關(guān)系，稱之為統(tǒng)計屬性定義表。例如，統(tǒng)計屬性“ZF”（總分）對應(yīng)數(shù)據(jù)庫表“ZCJ”的“ZF”字段，如表3中的第一條記錄。

（5） 維度定義表。同屬性一樣，維度也和統(tǒng)計對象表中的字段有著對應(yīng)關(guān)系，系統(tǒng)在生成SQL語句時，需要根據(jù)維度與統(tǒng)計對象的對應(yīng)關(guān)系轉(zhuǎn)換為數(shù)據(jù)庫表中的字段。描述維度與統(tǒng)計對象表及字段的對應(yīng)關(guān)系表，稱為維度定義表。維度定義表的結(jié)構(gòu)與統(tǒng)計屬性定義表類似，不再贅述。

（6） 統(tǒng)計單元定義表。系統(tǒng)將用戶的統(tǒng)計需求轉(zhuǎn)換為一個或多個統(tǒng)計單元，統(tǒng)計單元定義表主要對每個統(tǒng)計單元的四元素進行描述，表中主要字段包括統(tǒng)計對象、計算列、維度表達式、計算結(jié)果等。例如，“求500分以上理工類考生的總分平均成績”可以描述為一個統(tǒng)計單元記錄，如表4所示。

2 SQL生成器處理流程設(shè)計

SQL生成器設(shè)計思路是把用戶的分析需求轉(zhuǎn)換為統(tǒng)計單元定義，系統(tǒng)根據(jù)統(tǒng)計單元定義構(gòu)造SQL語句，批量執(zhí)行SQL語句即可獲得統(tǒng)計分析結(jié)果。

由系統(tǒng)自動構(gòu)造SQL語句，首先需要對SQL語句結(jié)構(gòu)進行分解，形成多個SQL子句，最后合成為完整的SQL語句。SQL語句整體結(jié)構(gòu)比較復(fù)雜，根據(jù)高考成績的常規(guī)統(tǒng)計需求，對所生成的SQL語句簡化，簡化后的SQL語句由3部分構(gòu)成，如圖2所示。

（1）形如“select 統(tǒng)計函數(shù)（表.字段）”，簡稱select子句，用于指定針對哪個字段進行什么類型的聚集計算。其中統(tǒng)計函數(shù)直接來自于統(tǒng)計單元定義，“表.字段”可由統(tǒng)計單元查找統(tǒng)計屬性定義表確定。

（2）形如“from 表1 join 表2 on 表1.字段1=表2.字段2 join 表3 on…”，簡稱from子句，該子句表達了數(shù)據(jù)從哪些數(shù)據(jù)表中篩選，以及這些數(shù)據(jù)表之間的關(guān)聯(lián)關(guān)系。這里的“表1”稱為主數(shù)據(jù)表，由統(tǒng)計單元中的統(tǒng)計對象決定；“表2、表3”等稱為輔數(shù)據(jù)表，由統(tǒng)計單元中的維度名稱決定；表之間的關(guān)聯(lián)條件，即“on 表1.字段1=表2.字段2”部分，可根據(jù)統(tǒng)計對象關(guān)系表中的連接關(guān)系字段確定。

（3）形如“where 維度表達式1 and 維度表達式2 and 維度表達式3……”，簡稱where子句，可由統(tǒng)計單元中的各維度表達式確定。

3個子句生成后，合成為一個完整的SQL語句，具體操作流程如圖3所示。

3 SQL生成器程序?qū)崿F(xiàn)

根據(jù)以上設(shè)計方案，采用開源數(shù)據(jù)庫系統(tǒng)MYSQL建立相應(yīng)數(shù)據(jù)庫表。為提高處理效率，采用MYSQL存儲過程實現(xiàn)處理流程。由于MYSQL存儲過程尚不支持面向?qū)ο缶幊?，所以在程序?qū)崿F(xiàn)上主要采用結(jié)構(gòu)化方法，并遵照低耦合、高凝聚的模塊化設(shè)計原則。主要存儲過程如表5所示。

4 結(jié)語

本文設(shè)計并實現(xiàn)了適用于高考成績定制分析的SQL生成器，通過統(tǒng)計單元定義統(tǒng)計需求，每個統(tǒng)計單元包括統(tǒng)計對象、統(tǒng)計屬性、維度、統(tǒng)計函數(shù)4個元素，系統(tǒng)根據(jù)統(tǒng)計單元中的4元素以及相應(yīng)定義生成SQL語句，將手工操作的個性化分析需求由計算機快速自動完成，提高了統(tǒng)計分析的效率及靈活性，降低了對用戶數(shù)據(jù)操作能力的要求。

需要說明的是，雖然所設(shè)計的SQL生成器可滿足大部分個性化高考成績統(tǒng)計分析需求，但個別需求尚不能滿足，這是因為所生成的SQL語句結(jié)構(gòu)是對完整的SQL語言結(jié)構(gòu)的簡化，相對簡單。例如不能生成包含子查詢的SQL語句，也不能生成多個SQL語句組成的程序段。因此，該設(shè)計方案還處在初級階段，尚需在此基礎(chǔ)上進一步改進和完善。另外，限于篇幅，本文主要討論了SQL生成器的總體處理流程，對于from子句、where子句的實現(xiàn)細節(jié)未進行詳細討論，后續(xù)筆者將進行專題研究。

參考文獻：

[1] 劉娟，高振華.化枯燥為神奇——高考數(shù)據(jù)統(tǒng)計分析報告“把脈”中學(xué)教學(xué)[J].考試與招生，2010（12）：5354.

[2] 胡宏銀，何成萬.基于VBA的SQL生成系統(tǒng)設(shè)計與實現(xiàn)[J].武漢化工學(xué)院學(xué)報，2006（3）：5154.

[3] 胡仁強，杜曉峰，張笑燕.基于BI工具的OLAP SQL語句生成系統(tǒng)研究與實現(xiàn)[J].互聯(lián)網(wǎng)天地，2014（12）：3135.

[4] 胡宏銀，何成萬，姚峰.SQL生成器的設(shè)計與實現(xiàn)[J].計算機工程與設(shè)計，2006（11）：20242027.

[5] 李海龍，張維明，肖衛(wèi)東，等.通用標準SQL語法分析模型[J].小型微型計算機系統(tǒng)，2003（11）：19691972.

[6] 程小燕.數(shù)據(jù)庫SQL語句生成工具的設(shè)計[J].山西師范大學(xué)學(xué)報：自然科學(xué)版，2014（2）：3133.

[7] 趙丹明.模擬SQL Builder創(chuàng)建SQL語句的關(guān)鍵技術(shù)[J].軟件導(dǎo)刊，2011，10（10）：4244.

（責任編輯：杜能鋼）