SQL生成器设计与实现

曹树贵王玉恒卢志舟高振华刘娟

摘 要：针对高考成绩统计分析个性化需求多、对工作人员信息处理能力要求高等问题，设计并实现了面向高考成绩统计分析的SQL生成器，用统计对象、统计属性、维度表达式、统计函数四元素组成统计单元描述用户分析需求，并映射为数据库对象。按照算法流程分别生成SQL语句的select子句、from子句和where子句，最终合并为可用于统计分析的完整SQL语句，实现高考成绩统计分析可定制化。

关键词关键词：高考成绩；统计分析；统计单元；SQL生成器

DOIDOI：10.11907/rjdk.162034

中图分类号：TP319

文献标识码：A 文章编号文章编号：16727800（2016）011008803

0 引言

2010年以来，河北省教育考试院利用现代信息技术和独有的数据资源优势，推出了以诊脉高考、服务教学为目标的高考数据统计分析系列报告[1]，报告采用程序计算和人工生成结合的方式，对于常规统计分析，采用固定的计算机程序完成，对于个性化需求则采用手工方式完成，工作量较大，报告生成周期较长。例如一份学校版的报告往往需要几天的时间才能完成，而学校对报告的需求时间比较集中，短期内很难满足众多学校需求；另外教育工作者通常不具备专业信息处理能力，在高考数据的检索、统计等方面存在很大困难。

为解决以上问题，提出一种根据用户自定义需求自动生成SQL（Structured Query Language）语句的高考成绩分析方案，即SQL生成器设计方案。常见的SQL生成器一般嵌入在软件开发工具或数据库系统软件中，如软件开发工具PowerBuilder、C++ Builder、Delphi，数据库系统软件Access、VFP 、Microsoft SQL Server、Oracle，BI设计软件 Microstrategy、Business Object、Cognos等都内嵌有SQL生成器[34]，独立的SQL生成器软件有Red Earth Technologies的Superior SQL Builder、SBS Development的Automated_SQL_Builde等[5]。这些SQL生成器都是面向软件开发或数据库管理等专业技术人员，而不是面向业务系统的最终用户。一些学者尝试SQL生成器研究，如李海龙[6]的通用标准SQL语法分析模型研究，程晓燕[7]的可视化编辑数据库操作工具研究，赵丹明[8]的模拟SQL Builder创建SQL语句的关键技术研究等。

1 SQL生成器数据库设计

1.1 相关概念

（1）统计对象。统计分析所针对的实体称之为统计对象，如“考生信息”、“总成绩信息”、“单科成绩信息”等都属于统计对象。

（2）维度。数据统计分析是从特定角度对特定范围数据的观察、对比，这些特定角度称之为维度，例如，“考试年份”、“行政区划”、“考试科目”、“总成绩”等属于不同的维度。

（3）统计属性。统计属性是指计算所针对的统计对象属性。如统计对象“总成绩信息”的“总分”属性，可用于求平均值、标准差、最大值、最小值等计算。

（4）统计函数。统计函数是作用于统计属性上的集合运算，如计数、平均值、标准差、最高分、最低分等。

（5）统计单元。统计单元是由统计对象、统计属性、维度表达式、统计函数4个基本元素构成的元组，代表一次特定计算。

统计单元及组成元素需要映射为机器世界的对象，才能进一步采用程序进行处理。这里采用关系型数据库的对象——表（Table）对统计单元及组成元素进行描述及数据存储，实现概念到机器的映射，映射关系如图1所示。

1.2 相关数据库表

（1） 统计对象数据表。每个统计对象可映射为一个数据库表（Table），数据库表存储统计对象数据。例如“KSXX”表存储统计对象——“考生信息”数据，“ZCJ”表存储统计对象——“总成绩信息”数据。

（2） 统计对象定义表。统计对象定义表用于描述统计对象和数据库表之间的对应关系，由此表可以把统计对象名称转换为数据库表名称，表1为该表数据示例。

（3） 统计对象关系表。各统计对象数据表之间存在一定的关联关系，如KSXX（考生信息表）、ZCJ（总成绩信息表）和kh（考号）等为关联条件，即KSXX.kh=ZCJ.kh。系统在生成SQL语句时需要根据表之间的关系构建连接字符串，从而形成多表查询语句，因此需要对表之间的关联关系进行定义。表2为统计对象关系表记录示例。

（4） 统计属性定义表。每个统计属性都对应统计对象数据表中的一个可计算字段，在数据库中用一张数据表描述各统计属性与统计对象及表中字段的对应关系，称之为统计属性定义表。例如，统计属性“ZF”（总分）对应数据库表“ZCJ”的“ZF”字段，如表3中的第一条记录。

（5） 维度定义表。同属性一样，维度也和统计对象表中的字段有着对应关系，系统在生成SQL语句时，需要根据维度与统计对象的对应关系转换为数据库表中的字段。描述维度与统计对象表及字段的对应关系表，称为维度定义表。维度定义表的结构与统计属性定义表类似，不再赘述。

（6） 统计单元定义表。系统将用户的统计需求转换为一个或多个统计单元，统计单元定义表主要对每个统计单元的四元素进行描述，表中主要字段包括统计对象、计算列、维度表达式、计算结果等。例如，“求500分以上理工类考生的总分平均成绩”可以描述为一个统计单元记录，如表4所示。

2 SQL生成器处理流程设计

SQL生成器设计思路是把用户的分析需求转换为统计单元定义，系统根据统计单元定义构造SQL语句，批量执行SQL语句即可获得统计分析结果。

由系统自动构造SQL语句，首先需要对SQL语句结构进行分解，形成多个SQL子句，最后合成为完整的SQL语句。SQL语句整体结构比较复杂，根据高考成绩的常规统计需求，对所生成的SQL语句简化，简化后的SQL语句由3部分构成，如图2所示。

（1）形如“select 统计函数（表.字段）”，简称select子句，用于指定针对哪个字段进行什么类型的聚集计算。其中统计函数直接来自于统计单元定义，“表.字段”可由统计单元查找统计属性定义表确定。

（2）形如“from 表1 join 表2 on 表1.字段1=表2.字段2 join 表3 on…”，简称from子句，该子句表达了数据从哪些数据表中筛选，以及这些数据表之间的关联关系。这里的“表1”称为主数据表，由统计单元中的统计对象决定；“表2、表3”等称为辅数据表，由统计单元中的维度名称决定；表之间的关联条件，即“on 表1.字段1=表2.字段2”部分，可根据统计对象关系表中的连接关系字段确定。

（3）形如“where 维度表达式1 and 维度表达式2 and 维度表达式3……”，简称where子句，可由统计单元中的各维度表达式确定。

3个子句生成后，合成为一个完整的SQL语句，具体操作流程如图3所示。

3 SQL生成器程序实现

根据以上设计方案，采用开源数据库系统MYSQL建立相应数据库表。为提高处理效率，采用MYSQL存储过程实现处理流程。由于MYSQL存储过程尚不支持面向对象编程，所以在程序实现上主要采用结构化方法，并遵照低耦合、高凝聚的模块化设计原则。主要存储过程如表5所示。

4 结语

本文设计并实现了适用于高考成绩定制分析的SQL生成器，通过统计单元定义统计需求，每个统计单元包括统计对象、统计属性、维度、统计函数4个元素，系统根据统计单元中的4元素以及相应定义生成SQL语句，将手工操作的个性化分析需求由计算机快速自动完成，提高了统计分析的效率及灵活性，降低了对用户数据操作能力的要求。

需要说明的是，虽然所设计的SQL生成器可满足大部分个性化高考成绩统计分析需求，但个别需求尚不能满足，这是因为所生成的SQL语句结构是对完整的SQL语言结构的简化，相对简单。例如不能生成包含子查询的SQL语句，也不能生成多个SQL语句组成的程序段。因此，该设计方案还处在初级阶段，尚需在此基础上进一步改进和完善。另外，限于篇幅，本文主要讨论了SQL生成器的总体处理流程，对于from子句、where子句的实现细节未进行详细讨论，后续笔者将进行专题研究。

参考文献：

[1] 刘娟，高振华.化枯燥为神奇——高考数据统计分析报告“把脉”中学教学[J].考试与招生，2010（12）：5354.

[2] 胡宏银，何成万.基于VBA的SQL生成系统设计与实现[J].武汉化工学院学报，2006（3）：5154.

[3] 胡仁强，杜晓峰，张笑燕.基于BI工具的OLAP SQL语句生成系统研究与实现[J].互联网天地，2014（12）：3135.

[4] 胡宏银，何成万，姚峰.SQL生成器的设计与实现[J].计算机工程与设计，2006（11）：20242027.

[5] 李海龙，张维明，肖卫东，等.通用标准SQL语法分析模型[J].小型微型计算机系统，2003（11）：19691972.

[6] 程小燕.数据库SQL语句生成工具的设计[J].山西师范大学学报：自然科学版，2014（2）：3133.

[7] 赵丹明.模拟SQL Builder创建SQL语句的关键技术[J].软件导刊，2011，10（10）：4244.

（责任编辑：杜能钢）