透視表

維基百科，自由的百科全書

樞軸表（英語：pivot table）也翻譯成透視表，是用來匯總其它表的數據。首先把源表分組（grouping），然後對各組內數據做匯總操作如排序、平均、累加、計數或字符串連接等。透視表用於數據處理，在數據可視化程序如電子表格或商業智能軟件中常見。這種「樞軸」或者pivoting匯總表的概念得以命名。

舉例：

  • 一個平凡的例子：第一張表包含一列數，透視表僅含一行一列為源表該列的均值。
  • 稍微複雜點的例子，源表有兩列分別為性別與「身高」，每行給出一個人的性別與高度；透視表有兩行兩列，在「性別」列分別寫「男性」與「女性」，在「身高」列分別寫對應性別的平均身高。
  • 更為複雜與更為典型的例子，源表有列「月份」、「銷售員」、「產品」、「銷售額」，每行給出一個銷售員在某個月度賣出的某種產品的金額；透視表第一列是「銷售員」用於寫其名字，其餘列還有「產品名」與「總銷售」用於匯總該產品在該銷售人賣出的銷售總額。

透視表與列聯表 (也稱作「交叉列表」cross tabulation或「交叉表」crosstab)相關。但透視表被認為更為動態，可以在其上執行某些動作；而列聯表是靜態顯示數據。

微軟在美國註冊了複合詞形式的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. ^ 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)
取自 "https://zh.100ke.info/w/index.php?title=透视表&oldid=82619903"