跳至內容

透視表

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

樞軸表(英語: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版本中推出了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和一些數據庫軟件的組成部分,也可以在其他數據可視化工具和商業智能包中找到。

電子表格

[編輯]

數據庫

[編輯]

使用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框架,允許嵌入透視表

程式語言與庫

[編輯]
  • 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)