SQLGroupTableBuilder
用于对后台数据进行分组统计。
定义一个SQLGroupTableBuilder的语法如下:
New SQLGroupTableBuilder(TableName, BaseTable)
参数说明:
TableName
字符型,指定即将生成的统计表的名称
BaseTable
字符型,指定默认的要统计的数据表,简称为基表
可以是未加载的数据表,只要数据源存在此表即可。
在定义SQLGroupTableBuilder的时候,只能指定一个统计表,也就是基表。
如果要同时针对多个表进行统计,可以使用AddTable方法添加表,参与统计的表相互之间必须存在某种联系。
添加统计表的语法为:
AddTable(LeftTable ,LeftCol, RightTable, RightCol)
LeftTable:
基表,或者已经添加的数据表,称之为左表。
LeftCol: 左表通过此列和右表关联。
RightTable:要添加的关联表,称之为右表。
RightCol: 右表通过此列和左表关联。
提示:
1、左表(LeftTable)和右表(Table2)并非一定要事先建立关联,只需两者的数据可以通过指定的列关联起来即可。
3、如果两个表存在一对多的父子关系,那么建议将多方(子表)作为左表,一方(父表)作为右表。
3、参与统计的表可以是未加载的数据表,只要数据源存在此表即可。
例如:
Dim s
g As New SQLGroupTableBuilder("统计表1","订单明细")表示基表是订单明细,此外添加产品表参与统计,产品表和订单明细通过产品ID列关联。
由于产品表和订单表是一对多的父子关系,所以用AddTable添加表的时候,订单表(子表)作为左表,产品表(父表)作为右表。
如果要根据多列关联,请参考:
Dim
b As New SQLGroupTableBuilder("统计表1","产品")上面的代码,产品表作为基表,添加订单表参与统计,两个表之间通过型号、规格两列建立关联。
SQLGroupTableBuilder包括以下属性:
ConnectionName: 指定数据源名称,如果省略,表示使用内部数据表。
Caption: 字符型,指定新生成统计表的标题。
Groups: 集合,用于添加分组列。
Totals: 集合,用于添加统计列。
VerticalTotal: 逻辑型,设为True,垂直方向自动增加汇总行。
Subtotal:
逻辑型,设为True,将对分组统计结果进行二次统计,生成汇总模式。
Decimals: 整数型,用于设置统计结果要保留的小数位数,默认为4位。
SamePeriodGrowth:逻辑型,是否生成同比增长率。
CircleGrowth: 逻辑型,是否生成环比增长率。
GrandProportion: 逻辑型,是否生成总占比。
GroupProportion: 逻辑型,是否生成分组占比。
Filter:
字符型,设置统计条件。
CommandTimeOut:
整数型,用于设置统计后台数据的超时时限,默认为30秒,一般不需要设置此属性,除非需要进行非常耗时超过30秒的统计,以至于出现超时错误,此时可适当地加大CommandTimeOut属性的值。
在Groups集合中添加一个分组列的语法是:
Groups.AddDef(Name)
Groups.AddDef(Name, NewName)
Groups.AddDef(Name, NewName, NewCaption)
Groups.AddDef(Name, DateGroup)
Groups.AddDef(Name, DateGroup, NewName)
Groups.AddDef(Name, DateGroup, NewName, NewCaption)
参数说明
Name: 字符型,指定分组列。
NewName: 字符型,用于给分组列指定一个新的名称,这个名称在统计表中显示。
NewCaption: 字符型,用于给分组列设置标题。
DateGroup: DateGroupEnum型枚举,如果分组列是日期列,可用此属性指定如何根据日期进行分组,默认按月分组。
DateGroupEnum枚举的可选值:
None : 不分组
Year: 按年分组
Quarter: 按季度分组
Month: 按月分组
Week: 按周分组
Day: 按天分组,月中的某一天。
Hour: 按小时分组
Minute: 按分钟分组
Second: 按秒分组
可以向Groups增加表达式分组列,语法为:
Groups.AddExp(Name,
Expression)
Groups.AddExp(Name,
Expression,Caption)
Name:
字符型,指定列名
Expression: 字符型,指定表达式。
Caption: 字符型,指定标题
在Totals集合中添加一个统计列的语法如下:
Totals.AddDef(Name)
Totals.AddDef(Name, NewName)
Totals.AddDef(Name, NewName, NewCaption)
Totals.AddDef(Name, Aggregate)
Totals.AddDef(Name, Aggregate, NewName)
Totals.AddDef(Name, Aggregate, NewName, NewCaption)
参数说明
Name:
字符型,指定统计列。
NewName: 字符型,用于给统计列指定一个新的名称,这个名称在统计表中显示。
NewCaption: 字符型,用于给统计列设置标题。
Aggregate: AggregateEnum枚举型,用于指定统计类型,默认是求和。
AggregateEnum型枚举的可选值:
Average:平均值
Count: 记录数
Max: 最大值
Min: 最小值
Sum: 累积值
可以向Totals添加表达式统计列,语法是:
Totals.AddExp(Name, Expression)
Totals.AddExp(Name, Expression, Caption)
Totals.AddExp(Name, Expression, Aggregate)
Totals.AddExp(Name, Expression, Caption, Aggregate)
Name: 字符型,指定列名
Expression:字符型,指定表达式。
Caption: 字符型,指定列标题
Aggregate: AggregateEnum枚举型,用于指定统计类型,默认是求和。
例如:
Dim
b As New SQLGroupTableBuilder("统计表1","订单")可以看出SQLGroupTableBuilder和GroupTableBuilder的使用方法基本相似,前者只是在后者的基础上增加了:
1、可以用ConnectionName指定数据源
2、可以用AddTable方法增加多个数据表
3、可以用AddExp方法增加表达式分组列或统计列
除此之外SQLGroupTableBuilder专门统计后台数据,而GroupTableBuilder默认统计的是已经加载的数据,如果需要统计后台数据,需将其FromServer属性设为True。
提示:由于SQLGroupTableBuilder是根据用户的设置,自动生成SQL语句进行统计,所以 添加表或列时,名称必须和后台数据库保持一致,此外条件表达式的语法和函数与采用的数据源有关,我们会在《SQL相关》这章对此进行专门的介绍。
统计单个数据表
在运行下面的示例之前,请打开CaseStudy目录下的文件:统计演示.Table
示例一
普通的统计,SQLGroupTableBuilder和GroupTableBuilder用起来基本一样。
例如统计每个产品的销售数量:
Dim
b As New SQLGroupTableBuilder("统计表1","订单")示例二
SQLGroupTableBuilder可以添加表达式统计列,例如:
Dim
b As New SQLGroupTableBuilder("统计表1","订单")示例三
不仅统计列可以是表达式,分组列也可以是表达式,例如:
Dim
b
As New
SQLGroupTableBuilder("统计表1","订单")
b.Groups.AddExp("年","Year(日期)")
b.Totals.AddDef("数量")
b.Totals.AddExp("金额","数量
* 单价 * (1 - 折扣)")
b.Build
MainTable
= Tables("统计表1")
注意这样代码:
b.Groups.AddExp("年","Year(日期)")
是通过表达式列来实现按年分组的,等效于:
b.Groups.AddDef("日期",DateGroupEnum.Year,"年")
统计多个数据表
在运行下面的示例之前,请打开CaseStudy目录下的文件:多表统计.Table
这个文件的所有表都来自外部数据源,外部数据源的名称为Sale,外部数据源是一个Access文件:多表统计.mdb
整个系统包括三个表,分别是客户、产品和订单:
订单表通过产品ID和产品表建立关联,通过客户ID和客户表建立关联。
示例一
假定要统计不同产品的销售数量,由于订单表没有产品名称列,如果只根据订单表生成统计表,只能显示产品ID及对应的销售数量,显然这样的统计表太不友好了,毕竟没有人会记得每个产品ID对应的产品名称是什么。
所以我们需要从产品表提取产品名称来进行统计:
Dim
b As New SQLGroupTableBuilder("统计表1","订单")生成的统计结果为:
在使用外部数据源的时候,一定要记得设置ConnectionName属性:
b.ConnectionName = "数据源名称"
示例二
假定要统计每个产品在不同年份的销售数量:
Dim
b As New SQLGroupTableBuilder("统计表1","订单")生成的统计结果为:
示例三
统计结果可以来自任意多个表,例如要按年统计每个客户订购不同产品的数量:
Dim
b As New SQLGroupTableBuilder("统计表1","订单")生成的统计结果:
示例四
SQLGroupTableBuilder同样可以生成汇总模式,只需将Subtotal属性设置为True即可,例如:
Dim
b As
New
SQLGroupTableBuilder("统计表1","订单")
b.ConnectionName
= "Sale"
b.AddTable("订单","产品ID","产品","产品ID")
'添加统计表
b.AddTable("订单","客户ID","客户","客户ID")
'添加统计表
b.Groups.AddDef("客户名称")
'根据产品名称分组
b.Groups.AddDef("日期",
DateGroupEnum.Year,
"年") '根据日期按年分组
b.Groups.AddDef("产品名称")
'根据产品名称分组
b.Totals.AddDef("数量")
'对数量进行统计
b.Subtotal =
True
'生成汇总模式
b.Build
'生成统计表
MainTable
= Tables("统计表1")
'打开生成的统计表
得到的统计结果为:
示例五
SQLGroupTableBuilder同样可以进行占比、环比、同比等数据分析,例如:
Dim
b As New SQLGroupTableBuilder("统计表1","订单")生成的统计表: