第十章 运用TRANSFORM创建交叉表
交叉表(Cross
Tabulations)是一种常用的分类汇总表格。在统计学中,交叉表是矩阵格式的一种表格,显示变量的(多变量)频率分布。交叉表被广泛用于调查研究,商业智能,工程和科学研究。它们提供了两个变量之间的相互关系的基本画面,可以帮助他们发现它们之间的相互作用。卡尔·皮尔逊(Karl Pearson)首先在“关于应变的理论及其关联理论与正常相关性”中使用了交叉表。
多元统计学的一个关键问题是找到高维应变表中包含的变量的(直接)依赖结构。如果某些有条件的独立性被揭示,那么甚至可以以更智能的方式来完成数据的存储。为了做到这一点,可以使用信息理论概念,它只能从概率分布中获得信息,这可以通过相对频率从交叉表中容易地表示。
利用交叉表查询数据非常直观明了,被广泛应用。交叉表查询也是数据库的一个特点。
第一节 TRANSFORM语句的完整语法
交叉表查询是将来源于某个表中的字段进行分组,一组列在交叉表左侧,一组列在交叉表上部。交叉表查询是将来源于某个表中的字段进行分组,一组列在交叉表左侧,一组列在交叉表上部,并在交叉表行与列交叉处显示表中某个字段的各种计算值。
交叉表分两种:静态交叉表和动态交叉表。
使用交叉表查询来计算和重构数据,可以简化数据分析。交叉表查询计算数据的总和,平均值,计数及其他类型的统计,并将它们分组,一组列在数据表左侧作为交叉表的行字段,另一组列在数据表的顶端作为交叉表的列字段。在ACCSS SQL中创建交叉表的语句是TRANSFORM,其完整的语法结构如下:
一、TRANSFORM语句的基本语法
TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, ...]])];
TRANSFORM语句包含以下部分:
部分
|
说明
|
aggfunction
|
对所选数据进行计算的 SQL 聚合函数。
|
selectstatement
|
Select 语句。
|
pivotfield
|
希望用于创建查询结果集中列标题的字段或表达式。
|
value1、value2
|
用于创建列标题的固定值。
|
二、TRANSFORM语法的几点说明
使用交叉表查询汇总数据时,将从作为列标题的指定字段或表达式中选择值,以便能够以一种比使用选择查询更紧凑的方式来查看数据。
TRANSFORM是可选的,但如果包括它,则应为SQL字符串中的第一个语句。它在指定作为行标题的字段的Select语句之前,在指定行分组方法的GROUP BY子句之前。您也可以包含其他子句(例如,指定其他选择或排序条件的Where)。还可以在交叉表查询中使用子查询作为谓词,特别是在Where子句中。
pivotfield中返回的值作为查询结果集中的列标题。例如,在交叉表查询中如果依据月销售量来透视销售数据,将会创建12个列。可以约束pivotfield以便从可选IN子句中所列出的固定值(value1,value2)内选择标题。也可以包含固定值用于没有数据来创建其他列的情况。
三、TRANSFORM语句在VBA编程中的应用实例
以下示例使用SQL
TRANSFORM子句创建交叉表查询,以显示每个雇员在1994年的每个日历季度完成的订单数。运行此过程需要使用SQLTRANSFORMOutput 函数。
VBA
Sub
TransformX1()
Dim dbs As Database
Dim strSQL As String
Dim qdfTRANSFORM As QueryDef
strSQL = "PARAMETERS prmYear SHORT;
TRANSFORM " _
& "Count(OrderID) " _
& "Select FirstName &
"" "" & LastName AS " _
& "FullName FROM Employees
INNER JOIN orders " _
& "ON Employees.EmployeeID =
" _
& "Orders.EmployeeID Where
DatePart " _
&
"(""yyyy"", orderDate) = [prmYear] "
strSQL = strSQL & "GROUP BY
FirstName & " _
& """ ""
& LastName " _
& "ORDER BY FirstName &
"" "" & LastName " _
& "PIVOT
DatePart(""q"", orderDate)"
' Modify this line to include the path to
Northwind
' on your computer.
Set dbs =
OpenDatabase("Northwind.mdb")
Set qdfTRANSFORM = dbs.CreateQueryDef _
("", strSQL)
SQLTRANSFORMOutput qdfTRANSFORM, 1994
dbs.Close
End Sub
以下示例使用SQL
TRANSFORM子句创建一个稍微复杂的交叉表查询,以显示每个雇员在1994年的每个日历季度所完成的订单的总金额(美元)。运行此过程需要使用SQLTRANSFORMOutput函数。
Sub
TransformX2()
Dim dbs As Database
Dim strSQL As String
Dim qdfTRANSFORM As QueryDef
strSQL = "PARAMETERS prmYear SMALLINT;
TRANSFORM " _
& "Sum(Subtotal) Select
FirstName & "" """ _
& "& LastName AS FullName
" _
& "FROM Employees INNER JOIN
" _
& "(Orders INNER JOIN [Order Subtotals] " _
& "ON orders.OrderID = "
_
& "[Order Subtotals].OrderID)
" _
& "ON Employees.EmployeeID =
" _
& "Orders.EmployeeID Where
DatePart" _
&
"(""yyyy"", orderDate) = [prmYear] "
strSQL = strSQL & "GROUP BY
FirstName & "" """ _
& "& LastName " _
& "ORDER BY FirstName &
"" "" & LastName " _
& "PIVOT
DatePart(""q"",OrderDate)"
' Modify this line to include the path to
Northwind
' on your computer.
Set dbs =
OpenDatabase("Northwind.mdb")
Set qdfTRANSFORM = dbs.CreateQueryDef _
("", strSQL)
SQLTRANSFORMOutput qdfTRANSFORM, 1994
dbs.Close
End Sub
Function
SQLTRANSFORMOutput(qdfTemp As QueryDef, _
intYear As Integer)
Dim rstTRANSFORM As Recordset
Dim fldLoop As Field
Dim booFirst As Boolean
qdfTemp.PARAMETERS!prmYear = intYear
Set rstTRANSFORM = qdfTemp.OpenRecordset()
Debug.Print qdfTemp.SQL
Debug.Print
Debug.Print , , "Quarter"
With rstTRANSFORM
booFirst = True
For Each fldLoop In .Fields
If booFirst = True Then
Debug.Print fldLoop.Name
Debug.Print , ;
booFirst = False
Else
Debug.Print , fldLoop.Name;
End If
Next fldLoop
Debug.Print
Do While Not .EOF
booFirst = True
For Each fldLoop In .Fields
If booFirst = True Then
Debug.Print fldLoop
Debug.Print , ;
booFirst = False
Else
Debug.Print , fldLoop;
End If
Next fldLoop
Debug.Print
.MoveNext
Loop
End With
End Function