fbpx
维基百科

透视表

十一月 05, 2023

枢轴表（英語：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版本中推出了PIVOT与UNPIVOT运算。[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. ^ . [2018-08-21]. （原始内容存档于2021-01-19）. 
  7. ^ . postgresql.org. [2018-08-21]. （原始内容存档于2018-03-09）. 
  8. ^ CONNECT Table Types - PIVOT Table Type. mariadb.com. [2018-08-21]. （原始内容于2016-04-04）. 
  9. ^ . [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)

十一月 05, 2023
透视表, 此條目包含指南或教學內容, 2018年8月21日, 請藉由移除或重寫指南段落來改善條目, 或在討論頁提出討論, 此條目翻譯品質不佳, 2018年8月21日, 翻譯者可能不熟悉中文或原文語言, 也可能使用了機器翻譯, 請協助翻譯本條目或重新編寫, 并注意避免翻译腔的问题, 明顯拙劣的翻譯請改掛, href, template, html, class, redirect, title, template, href, wikipedia, html, class, redirect, title, wiki. 此條目包含指南或教學內容 2018年8月21日 請藉由移除或重寫指南段落來改善條目 或在討論頁提出討論 此條目翻譯品質不佳 2018年8月21日 翻譯者可能不熟悉中文或原文語言 也可能使用了機器翻譯 請協助翻譯本條目或重新編寫 并注意避免翻译腔的问题 明顯拙劣的翻譯請改掛 a href Template D html class mw redirect title Template D d a a href Wikipedia CSD html G13 class mw redirect title Wikipedia CSD G13 a 提交刪除 枢轴表 英語 pivot table 也翻译成透视表 是用来汇总其它表的数据 首先把源表分组 grouping 然后对各组内数据做汇总操作如排序 平均 累加 计数或字符串连接等 透视表用于数据处理 在数据可视化程序如电子表格或商业智能软件中常见 这种 枢轴 或者pivoting汇总表的概念得以命名 举例 一个平凡的例子 第一张表包含一列数 透视表仅含一行一列为源表该列的均值 稍微复杂点的例子 源表有两列分别为性别与 身高 每行给出一个人的性别与高度 透视表有两行两列 在 性别 列分别写 男性 与 女性 在 身高 列分别写对应性别的平均身高 更为复杂与更为典型的例子 源表有列 月份 销售员 产品 销售额 每行给出一个销售员在某个月度卖出的某种产品的金额 透视表第一列是 销售员 用于写其名字 其余列还有 产品名 与 总销售 用于汇总该产品在该销售人卖出的销售总额 透视表与列联表 英语 Contingency table 也称作 交叉列表 cross tabulation或 交叉表 crosstab 相关 但透视表被认为更为动态 可以在其上执行某些动作 而列联表 英语 Contingency table 是静态显示数据 微软在美国注册了复合词形式的PivotTable为商标 1 目录 1 历史 2 机制 3 实现 3 1 报表筛选 Report filter 3 2 列标签 Column labels 3 3 行标签 Row labels 3 4 数值 Summation values 4 应用程序支持 4 1 电子表格 4 2 数据库 4 2 1 PostgreSQL 4 2 2 MariaDB 4 2 3 Microsoft Access 4 2 4 Oracle database 4 2 5 Microsoft SQL Server 4 2 6 MySQL 4 3 Web应用 4 4 编程语言与库 5 参见 6 参考文献 7 进一步阅读历史 编辑 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版本中推出了PIVOT与UNPIVOT运算 5 机制 编辑典型的数据存储为扁平的 flat 表 即只包含行和列 例如下述电子表格为销货明细 nbsp 当表中包含很多列 就难以总结出表中的信息 透视表可以快速总结数据并高亮期望的信息 我正在看什么 例如 对上例 问 多少件货在各个地区各个发货日期被销售 nbsp 透视表通常包含行 列与数据 fact 在这个例子中 列是 发货日期 行是 地区 数据是销售数量的汇总 实现 编辑从SQL编程实现角度 透视表是做一个grouping by操作 对组内数据做汇总计算 下例在Excel中创建透视表的过程如下 Date of sale Sales person Item sold Color of item Units sold Per unit price Total price10 01 13 Jones Notebook Black 8 25000 20000010 02 13 Prince Laptop Red 4 35000 14000010 03 13 George Mouse Red 6 850 510010 04 13 Larry Notebook White 10 27000 27000010 05 13 Jones Mouse Black 4 700 3200各个列可用于透视表设计的布局的四个地方 报表筛选 Report filter 列标签 Column labels 行标签 Row labels 数值 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 soldJones 12Prince 4George 6Larry 10Grand total 32应用程序支持 编辑数据透视表或数据透视功能是许多电子表格应用程序 英语 List of spreadsheet software 和一些数据库软件的组成部分 也可以在其他数据可视化工具和商业智能包中找到 电子表格 编辑 Microsoft Excel Apache OpenOffice Calc LibreOffice Google Docs 6 数据库 编辑 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 lt 非旋转列 gt 第一个旋转列 AS lt 列名 gt 最后一个旋转列 AS lt 列名 gt FROM lt SELECT生成的数据查询 gt AS lt 为源查询结果指定的别名 gt PIVOT 聚合函数运算 FOR lt 被转换为列标题值的列 gt IN 第一个旋转后的列 最后一个旋转后的列 AS lt 为透视表指定的别名 gt lt 可选的Order子句 gt 反透视的语法格式为 SELECT lt 非旋转列 gt 第一个旋转列 AS lt 列名 gt 最后一个旋转列 AS lt 列名 gt FROM 透视表 AS lt 透视表的别名 gt UNPIVOT lt 值的列名 gt FOR lt 列标题的列名 gt IN 列标题1 列标题N AS lt 为反透视表指定的别名 gt SQL语言中 PIVOT函数又被称为行转列函数 该函数的作用就是将行转为列 使数据看起来更加直观明了 PIVOT 通过将参数中某一列的唯一值转换为输出中的多个列来 旋转 并在必要时对最终输出中所需的任何其余列值执行聚合操作 其语法为 SELECT lt 非透视的列 gt 第一个透视的列 第二个透视的列 最后一个透视的列 FROM 表名 PIVOT lt 聚合函数 gt lt 要聚合的列 gt FOR lt 其值要成为列标题的列 gt IN 第一个透视的列 第二个透视的列 最后一个透视的列 AS lt 透视表的名字 gt lt 可选的 ORDER BY 子句 gt 举例 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 参见 编辑列联表 英语 Contingency table 萃取 转置 加载 折叠 高阶函数 英语 Fold higher order function 关系代数 数据库 参考文献 编辑 United States Trademark Serial Number 74472929 1994 12 27 2013 02 17 原始内容存档于2013 05 11 Jelen Bill Alexander Michael Pivot table data crunching Indianapolis Que 2006 274 ISBN 0 7897 3435 4 Gartung Daniel L Edholm Yorgen H Edholm Kay Martin McNall Kristen N Lew Karl M Patent 5915257 2010 02 16 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 请检查 publication date 中的日期值 帮助 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 Docs Blog Summarize your data with pivot tables 2018 08 21 原始内容存档于2021 01 19 PostgreSQL Documentation 9 2 tablefunc postgresql org 2018 08 21 原始内容存档于2018 03 09 CONNECT Table Types PIVOT Table Type mariadb com 2018 08 21 原始内容存档于2016 04 04 MSDN TRANSFORM Statement Microsoft Access SQL 2018 08 21 原始内容存档于2014 12 26 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 取自 https zh wikipedia org w index php title 透视表 amp oldid 79303685, 维基百科，wiki，书籍，书籍，图书馆，

文章

，阅读，下载，免费，免费下载，mp3，视频，mp4，3gp， jpg，jpeg，gif，png，图片，音乐，歌曲，电影，书籍，游戏，游戏。