SQLGroupTableBuilder

用于对后台数据进行分组统计。

定义一个SQLGroupTableBuilder的语法如下: 

New SQLGroupTableBuilder(TableName, BaseTable)

参数说明:

在定义SQLGroupTableBuilder的时候,只能指定一个统计表,也就是基表。
如果要同时针对多个表进行统计,可以使用AddTable方法添加表,参与统计的表相互之间必须存在某种联系。

添加统计表的语法为:

AddTable(LeftTable ,LeftCol, RightTable, RightCol)

LeftTable: 基表,或者已经添加的数据表,称之为左表。
LeftCol:   左表通过此列和右表关联。
RightTable:要添加的关联表,称之为右表。
RightCol:  右表通过此列和左表关联。

提示:

1、左表(LeftTable)和右表(Table2)并非一定要事先建立关联,只需两者的数据可以通过指定的列关联起来即可。
3、如果两个表存在一对多的父子关系,那么建议将多方(子表)作为左表,一方(父表)作为右表。
3、参与统计的表可以是未加载的数据表,只要数据源存在此表即可。

例如:

Dim sg As New SQLGroupTableBuilder("统计表1","订单明细")
sg.AddTable(
"订单明细","产品ID","产品","产品ID")

表示基表是订单明细,此外添加产品表参与统计,产品表和订单明细通过产品ID列关联。
由于产品表和订单表是一对多的父子关系,所以用AddTable添加表的时候,订单表(子表)作为左表,产品表(父表)作为右表。

如果要根据多列关联,请参考:

Dim b As New SQLGroupTableBuilder("统计表1","产品")
Dim
Cols1() As String = {"型号","规格"}
Dim
Cols2() As String = {"型号","规格"}
b.AddTable(
"产品",Cols1,"订单"
,Cols2)

上面的代码,产品表作为基表,添加订单表参与统计,两个表之间通过型号、规格两列建立关联。

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","订单")
b.
Totals.AddExp("金额","数量 * 单价 * (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","订单")
b
.Groups.AddDef("产品") '根据产品分组
b
.Totals.AddDef("数量") '对数量进行统计
b.Build
'生成统计表
Maintable = Tables(
"统计表1")
'打开生成的统计表

示例二

SQLGroupTableBuilder可以添加表达式统计列,例如:

Dim b As New SQLGroupTableBuilder("统计表1","订单")
b
.Groups.AddDef("产品")
b
.Totals.AddDef("数量")
b.Totals.AddExp("金额","数量 * 单价 * (1 - 折扣)")
b
.Build
Maintable = Tables(
"统计表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","订单")
b.ConnectionName =
"Sale"
b.AddTable(
"订单","产品ID","产品","产品ID") '添加统计表
b.Groups.AddDef(
"产品名称") '根据产品名称分组
b
.Totals.AddDef("数量") '对数量进行统计
b
.Build '生成统计表
Maintable = Tables(
"统计表1")
'打开生成的统计表

生成的统计结果为:

在使用外部数据源的时候,一定要记得设置ConnectionName属性:

b.ConnectionName = "数据源名称"

示例二

假定要统计每个产品在不同年份的销售数量:

Dim b As New SQLGroupTableBuilder("统计表1","订单")
b.ConnectionName =
"Sale"
b.AddTable(
"订单","产品ID","产品","产品ID") '添加统计表
b.Groups.AddDef(
"产品名称") '根据产品名称分组
b.Groups.AddDef(
"日期", DateGroupEnum.Year, "年") '根据日期按年分组
b.Totals.AddDef(
"数量") '对数量进行统计
b.Build
'生成统计表
Maintable
= Tables("统计表1") '打开生成的统计表

生成的统计结果为:

示例三

统计结果可以来自任意多个表,例如要按年统计每个客户订购不同产品的数量:

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.Build
'生成统计表
Maintable
= Tables("统计表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","订单")
b.ConnectionName =
"Sale"
b.AddTable(
"订单","产品ID","产品","产品ID") '添加统计表
b.Groups.AddDef(
"产品名称") '根据产品名称分组
b.Groups.AddDef(
"日期", DateGroupEnum.Year, "年") '根据日期按年分组
b.Totals.AddDef(
"数量") '对数量进行统计
b.CircleGrowth =
True '生成环比增长率
b.GrandProportion =
True '生成总站比
b.Build
'生成统计表
Maintable
= Tables("统计表1") '打开生成的统计表

生成的统计表:


本页地址:http://www.foxtable.com/webhelp/topics/1626.htm