透视表
枢轴表（英語：pivot table）也翻译成透视表，是用来汇总其它表的数据。首先把源表分组（grouping），然后对各组内数据做汇总操作如排序、平均、累加、计数或字符串连接等。透视表用于数据处理，在数据可视化程序如电子表格或商业智能软件中常见。这种“枢轴”或者pivoting汇总表的概念得以命名。 举例：
透视表与列联表 (也称作“交叉列表”cross tabulation或“交叉表”crosstab)相关。但透视表被认为更为动态，可以在其上执行某些动作；而列联表是静态显示数据。 微软在美国注册了复合词形式的PivotTable为商标。[1] 历史《Pivot Table Data Crunching》[2]指出Pito Salas是“透视表之父”，在开发Lotus Improv时，Salas称电子表格有数据模式，帮助用户识别出数据模式的工具有助于快速建立高级数据模型。Lotus Improv允许用户定义范畴（categories）存储集合，用鼠标拖拉范畴名字可以改变视图（view）。这种核心功能提供了透视表模型。Lotus Development1991年在NeXT平台上发布了Lotus Improv。几个月后，Brio Technology发布了在Macintosh的实现，称作DataPivot。1999年申请了专利。[3] 1992年Borland收购了DataPivot技术，用在了其电子表格软件Quattro Pro。 1993年Microsoft Windows版本的Improv发布。 1994年初Microsoft Excel 5[4]带来了新功能"PivotTable"。微软在其后版本的Excel中继续改进这个功能:
2007年，Oracle公司在Oracle数据库11g版本中推出了 机制典型的数据存储为扁平的（flat）表，即只包含行和列。 例如下述电子表格为销货明细：
当表中包含很多列，就难以总结出表中的信息。透视表可以快速总结数据并高亮期望的信息。“我正在看什么？”例如，对上例，问“多少件货在各个地区各个发货日期被销售？”
透视表通常包含行、列与数据（fact）。在这个例子中，列是“发货日期”，行是“地区”，数据是销售数量的汇总。 实现从SQL编程实现角度，透视表是做一个grouping by操作，对组内数据做汇总计算。 下例在Excel中创建透视表的过程如下：
各个列可用于透视表设计的布局的四个地方：
报表筛选 Report filter报表筛选用于过滤源表的行。例如，鼠标拖拉"Color of Item"到这个区域，则有一个下拉列表选项（Black, Red, White），可以选择某个值作为源表中行的过滤标准，例如"Color of Item = Black"。 列标签 Column labels列标签用于一个或多个源表中列，其值将作为透视表中的列名。例如，鼠标拖拉"Sales person"到这个区域中，那么透视表中将有5个列，每个销售人占一列，还有一列为Grand Total。也可以施加筛选器，选中或者反选特定的销售人。 行标签 Row labels行标签类似于列标签，用于一个或多个源表中列，其值将作为透视表中的行名。例如，鼠标拖拉"Sales person"到这个区域中，那么透视表中将有5个行，每个销售人占一行，还有一行为Grand Total。也可以施加筛选器，选中或者反选特定的销售人。 数值 Summation values通常选择一个数值型的列。可施加不同的累积计算。对文本型的列，可以做计数（count）或者连接操作。上例中，如果列标签选择了"Sales person", 数值选择了"units sold"，那么透视表将增加一个新列"Sum of units sold"，是对每位销售人的销售额汇总。
应用程序支持数据透视表或数据透视功能是许多电子表格应用程序和一些数据库软件的组成部分，也可以在其他数据可视化工具和商业智能包中找到。 电子表格数据库使用tablefunc模块[7] 使用CONNECT存储引擎[8] 使用 支持PIVOT操作 从2005版本开始支持。透视的语法格式为： SELECT <非旋转列>,[第一个旋转列] AS <列名>,...,[最后一个旋转列] AS <列名> FROM (<SELECT生成的数据查询>) AS <为源查询结果指定的别名>PIVOT({聚合函数运算}FOR[<被转换为列标题值的列>] IN ([第一个旋转后的列],...,[最后一个旋转后的列])AS <为透视表指定的别名><可选的Order子句>
反透视的语法格式为： SELECT <非旋转列>,[第一个旋转列] AS <列名>,...,[最后一个旋转列] AS <列名> FROM (透视表) AS <透视表的别名>UNPIVOT( <值的列名> FOR <列标题的列名> IN (列标题1,...,列标题N) )AS <为反透视表指定的别名>
SQL语言中，PIVOT函数又被称为行转列函数。该函数的作用就是将行转为列，使数据看起来更加直观明了。PIVOT 通过将参数中某一列的唯一值转换为输出中的多个列来“旋转”，并在必要时对最终输出中所需的任何其余列值执行聚合操作。其语法为： SELECT <非透视的列>,
[第一个透视的列],
[第二个透视的列],
...
[最后一个透视的列]
FROM
表名
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<其值要成为列标题的列>]
IN ( [第一个透视的列], [第二个透视的列],
... [最后一个透视的列])
) AS <透视表的名字>
<可选的 ORDER BY 子句>;
举例： if exists(select * from sys.databases where name='Student')
drop database Student
go
create database Student
go
use Student
create table students(
ID int not null,
Name varchar(50) not null,
Subject varchar(50) not null,
Grade int not null
)
insert into students values(1,'张三','语文',76)
insert into students values(1,'张三','数学',85)
insert into students values(1,'张三','英语',74)
insert into students values(2,'李四','语文',89)
insert into students values(2,'李四','数学',78)
insert into students values(2,'李四','英语',98)
select * from students
select Name as 姓名,[语文],[数学],[英语] from students PIVOT(sum(Grade)for [Subject] in([语文],[数学],[英语])) as p order by ID
不支持支持透视与反透视 Web应用
编程语言与库参见参考文献
进一步阅读
|
Portal di Ensiklopedia Dunia
