透视表

(重定向自Pivot table

枢轴表(英語:pivot table)也翻译成透视表,是用来汇总其它表的数据。首先把源表分组(grouping),然后对各组内数据做汇总操作如排序、平均、累加、计数或字符串连接等。透视表用于数据处理,在数据可视化程序如电子表格商业智能软件中常见。这种“枢轴”或者pivoting汇总表的概念得以命名。

举例:

  • 一个平凡的例子:第一张表包含一列数,透视表仅含一行一列为源表该列的均值。
  • 稍微复杂点的例子,源表有两列分别为性别与“身高”,每行给出一个人的性别与高度;透视表有两行两列,在“性别”列分别写“男性”与“女性”,在“身高”列分别写对应性别的平均身高。
  • 更为复杂与更为典型的例子,源表有列“月份”、“销售员”、“产品”、“销售额”,每行给出一个销售员在某个月度卖出的某种产品的金额;透视表第一列是“销售员”用于写其名字,其余列还有“产品名”与“总销售”用于汇总该产品在该销售人卖出的销售总额。

透视表与列联表英语Contingency table (也称作“交叉列表”cross tabulation或“交叉表”crosstab)相关。但透视表被认为更为动态,可以在其上执行某些动作;而列联表英语Contingency table是静态显示数据。

微软在美国注册了复合词形式的PivotTable商标[1]

历史 编辑

《Pivot Table Data Crunching》[2]指出Pito Salas英语Pito Salas是“透视表之父”,在开发Lotus Improv时,Salas称电子表格有数据模式,帮助用户识别出数据模式的工具有助于快速建立高级数据模型。Lotus Improv允许用户定义范畴(categories)存储集合,用鼠标拖拉范畴名字可以改变视图(view)。这种核心功能提供了透视表模型。Lotus Development1991年在NeXT平台上发布了Lotus Improv。几个月后,Brio Technology英语Brio Technology发布了在Macintosh的实现,称作DataPivot。1999年申请了专利。[3] 1992年Borland收购了DataPivot技术,用在了其电子表格软件Quattro Pro英语Quattro Pro

1993年Microsoft Windows版本的Improv发布。

1994年初Microsoft Excel 5[4]带来了新功能"PivotTable"。微软在其后版本的Excel中继续改进这个功能:

  • Excel 97包括了新的改进版的PivotTable向导(Wizard),能够创建计算字段,允许开发者写Visual Basic for Applications小程序以创建或修改透视表;
  • Excel 2000引入了"透视图"以便可视化透视表

2007年,Oracle公司在Oracle数据库11g版本中推出了PIVOTUNPIVOT运算。[5]

机制 编辑

典型的数据存储为扁平的(flat)表,即只包含行和列。 例如下述电子表格为销货明细:

 

当表中包含很多列,就难以总结出表中的信息。透视表可以快速总结数据并高亮期望的信息。“我正在看什么?”例如,对上例,问“多少件货在各个地区各个发货日期被销售?”

 

透视表通常包含行、列与数据(fact)。在这个例子中,列是“发货日期”,行是“地区”,数据是销售数量的汇总。

实现 编辑

从SQL编程实现角度,透视表是做一个grouping by操作,对组内数据做汇总计算。

下例在Excel中创建透视表的过程如下:

Date of sale Sales person Item sold Color of item Units sold Per unit price Total price
10/01/13 Jones Notebook Black 8 25000 200000
10/02/13 Prince Laptop Red 4 35000 140000
10/03/13 George Mouse Red 6 850 5100
10/04/13 Larry Notebook White 10 27000 270000
10/05/13 Jones Mouse Black 4 700 3200

各个列可用于透视表设计的布局的四个地方:

  1. 报表筛选 Report filter
  2. 列标签 Column labels
  3. 行标签 Row labels
  4. 数值 Summation values

报表筛选 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",是对每位销售人的销售额汇总。

Row labels Sum of units sold
Jones 12
Prince 4
George 6
Larry 10
Grand total 32

应用程序支持 编辑

数据透视表或数据透视功能是许多电子表格应用程序英语List of spreadsheet software和一些数据库软件的组成部分,也可以在其他数据可视化工具和商业智能包中找到。

电子表格 编辑

数据库 编辑

PostgreSQL 编辑

使用tablefunc模块[7]

MariaDB 编辑

使用CONNECT存储引擎[8]

Microsoft Access 编辑

使用TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, …]])],aggfunction是对被选中数据的累积计算,selectstatement是select语句,pivotfield是将在透视表中展开为列表的域, value1, value2是创建列名的固定值。[9]

Oracle database 编辑

支持PIVOT操作

Microsoft SQL Server 编辑

从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

MySQL 编辑

不支持支持透视与反透视

Web应用 编辑

  • ZK框架,允许嵌入透视表

编程语言与库 编辑

  • Python数据分析工具库pandas的函数pivot_table, 以及用于获取透视表部分的xs方法.[10]

参见 编辑

参考文献 编辑

  1. ^ United States Trademark Serial Number 74472929. 1994-12-27 [2013-02-17]. (原始内容存档于2013-05-11). 
  2. ^ Jelen, Bill; Alexander, Michael. Pivot table data crunching. Indianapolis: Que. 2006: 274. ISBN 0-7897-3435-4. 
  3. ^ Gartung, Daniel L.; Edholm, Yorgen H.; Edholm, Kay-Martin; McNall, Kristen N.; Lew, Karl M., Patent #5915257, [2010-02-16] 
  4. ^ Darlington, Keith. VBA For Excel Made Simple. Routledge. 2012-08-06: 19 (2012) [2014-09-10]. ISBN 9781136349775. [...] Excel 5, released in early 1994, included the first version of VBA. 
  5. ^ Shah, Sharanam; Shah, Vaishali. Oracle for Professionals - Covers Oracle 9i, 10g and 11g. Shroff Publishing Series. Navi Mumbai: Shroff Publishers. 2008: 549July 2008 [2014-09-10]. ISBN 9788184045260. One of the most useful new features of the Oracle Database 11g from the SQL perspective is the introduction of Pivot and Unpivot operators. 
  6. ^ Docs Blog: Summarize your data with pivot tables. [2018-08-21]. (原始内容存档于2021-01-19). 
  7. ^ PostgreSQL: Documentation: 9.2: tablefunc. postgresql.org. [2018-08-21]. (原始内容存档于2018-03-09). 
  8. ^ CONNECT Table Types - PIVOT Table Type. mariadb.com. [2018-08-21]. (原始内容存档于2016-04-04). 
  9. ^ MSDN:TRANSFORM Statement (Microsoft Access SQL). [2018-08-21]. (原始内容存档于2014-12-26). 
  10. ^ Pandas pivot_table. [2018-08-21]. (原始内容存档于2014-04-01). 

进一步阅读 编辑

  • A Complete Guide to PivotTables: A Visual Approach (ISBN 1-59059-432-0) (in-depth review at slashdot.org Archive.is存檔,存档日期2013-01-13)
  • Excel 2007 PivotTables and PivotCharts: Visual blueprint (ISBN 978-0-470-13231-9)
  • Pivot Table Data Crunching (Business Solutions) (ISBN 0-7897-3435-4)
  • Beginning Pivot Tables in Excel 2007 (ISBN 1-59059-890-3)