透視表
此條目包含指南或教學內容。 (2018年8月21日) |
此條目翻譯品質不佳。 (2018年8月21日) |
樞軸表（英語：pivot table）也翻譯成透視表，是用來匯總其它表的數據。首先把源表分組（grouping），然後對各組內數據做匯總操作如排序、平均、累加、計數或字符串連接等。透視表用於數據處理，在數據可視化程序如電子表格或商業智能軟件中常見。這種「樞軸」或者pivoting匯總表的概念得以命名。
舉例：
- 一個平凡的例子：第一張表包含一列數，透視表僅含一行一列為源表該列的均值。
- 稍微複雜點的例子，源表有兩列分別為性別與「身高」，每行給出一個人的性別與高度；透視表有兩行兩列，在「性別」列分別寫「男性」與「女性」，在「身高」列分別寫對應性別的平均身高。
- 更為複雜與更為典型的例子，源表有列「月份」、「銷售員」、「產品」、「銷售額」，每行給出一個銷售員在某個月度賣出的某種產品的金額；透視表第一列是「銷售員」用於寫其名字，其餘列還有「產品名」與「總銷售」用於匯總該產品在該銷售人賣出的銷售總額。
透視表與列聯表 (也稱作「交叉列表」cross tabulation或「交叉表」crosstab)相關。但透視表被認為更為動態，可以在其上執行某些動作；而列聯表是靜態顯示數據。
歷史
《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中繼續改進這個功能:
- Excel 97包括了新的改進版的PivotTable嚮導（Wizard），能夠創建計算字段，允許開發者寫Visual Basic for Applications小程序以創建或修改透視表；
- Excel 2000引入了"透視圖"以便可視化透視表
機制
典型的數據存儲為扁平的（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 |
各個列可用於透視表設計的佈局的四個地方：
- 報表篩選 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 sold |
|---|---|
| Jones | 12 |
| Prince | 4 |
| George | 6 |
| Larry | 10 |
| Grand total | 32 |
應用程式支持
數據透視表或數據透視功能是許多電子表格應用程式和一些數據庫軟件的組成部分，也可以在其他數據可視化工具和商業智能包中找到。
電子表格
數據庫
使用tablefunc模塊[7]
使用CONNECT存儲引擎[8]
使用TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, …]])]，aggfunction是對被選中數據的累積計算，selectstatement是select語句，pivotfield是將在透視表中展開為列表的域， value1, value2是創建列名的固定值。[9]
支持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應用
- ZK框架，允許嵌入透視表
程式語言與庫
參見
參考文獻
- ^ 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.
- ^
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)