Oracle行列转换函数--Pivot 和 Unpivot

https://www.oracle.com/cn/database/articles/technology/pivot-and-unpivot.html

Pivot 和 Unpivot
使用简单的 SQL 以电子表格类型的交叉表报表显示任何关系表中的信息,并将交叉表中的所有数据存储到关系表中。

Pivot
如您所知,关系表是表格化的,即,它们以列-值对的形式出现。假设一个表名为 CUSTOMERS。

COPY
Copied to ClipboardError: Could not Copy
SQL> desc customers
Name Null? Type


CUST_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)
选定该表:
select cust_id, state_code, times_purchased
from customers
order by cust_id;
输出结果如下:
CUST_ID STATE_CODE TIMES_PURCHASED


  1 CT                       1
  2 NY                      10
  3 NJ                       2
  4 NY                       4

...

and so on ...
SQL> desc customers
Name Null? Type


CUST_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)
选定该表:
select cust_id, state_code, times_purchased
from customers
order by cust_id;
输出结果如下:
CUST_ID STATE_CODE TIMES_PURCHASED


  1 CT                       1
  2 NY                      10
  3 NJ                       2
  4 NY                       4

...

and so on ...
注意数据是如何以行值的形式显示的:针对每个客户,该记录显示了客户所在的州以及该客户在商店购物的次数。当该客户从商店购买更多物品时,列 times_purchased 会进行更新。

现在,假设您希望统计一个报表,以了解各个州的购买频率,即,各个州有多少客户只购物一次、两次、三次等等。如果使用常规 SQL,您可以执行以下语句:

COPY
Copied to ClipboardError: Could not Copy
select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_purchased;
输出如下:
ST TIMES_PURCHASED CNT


CT 0 90
CT 1 165
CT 2 179
CT 3 173
CT 4 173
CT 5 152
...

and so on ...
select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_purchased;
输出如下:
ST TIMES_PURCHASED CNT


CT 0 90
CT 1 165
CT 2 179
CT 3 173
CT 4 173
CT 5 152
...

and so on ...
这就是您所要的信息,但是看起来不太方便。使用交叉表报表可能可以更好地显示这些数据,这样,您可以垂直排列数据,水平排列各个州,就像电子表格一样:

COPY
Copied to ClipboardError: Could not Copy
Times_purchased
CT NY NJ ...

and so on ...

1 0 1 0 ...
2 23 119 37 ...
3 17 45 1 ...
...

and so on ...
Times_purchased
CT NY NJ ...

and so on ...

1 0 1 0 ...
2 23 119 37 ...
3 17 45 1 ...
...

and so on ...
在 Oracle 数据库 11g 推出之前,您需要针对每个值通过 decode 函数进行以上操作,并将每个不同的值编写为一个单独的列。但是,该方法一点也不直观。

庆幸的是,您现在可以使用一种很棒的新特性 PIVOT 通过一种新的操作符以交叉表格式显示任何查询,该操作符相应地称为 pivot。下面是查询的编写方式:

COPY
Copied to ClipboardError: Could not Copy
select * from (
select times_purchased, state_code
from customers t
)
pivot
(
count(state_code)
for state_code in (‘NY‘,‘CT‘,‘NJ‘,‘FL‘,‘MO‘)
)
order by times_purchased
/
select * from (
select times_purchased, state_code
from customers t
)
pivot
(
count(state_code)
for state_code in (‘NY‘,‘CT‘,‘NJ‘,‘FL‘,‘MO‘)
)
order by times_purchased
/
输出如下:

COPY
Copied to ClipboardError: Could not Copy
. TIMES_PURCHASED ‘NY‘ ‘CT‘ ‘NJ‘ ‘FL‘ ‘MO‘


      0      16601         90          0          0          0
      1      33048        165          0          0          0
      2      33151        179          0          0          0
      3      32978        173          0          0          0
      4      33109        173          0          1          0

... and so on ...
. TIMES_PURCHASED ‘NY‘ ‘CT‘ ‘NJ‘ ‘FL‘ ‘MO‘


      0      16601         90          0          0          0
      1      33048        165          0          0          0
      2      33151        179          0          0          0
      3      32978        173          0          0          0
      4      33109        173          0          1          0

... and so on ...
这表明了 pivot 操作符的威力。state_codes 作为标题行而不是列显示。下面是传统的表格化格式的图示:

Times Purchased

图 1 传统的表格化显示

在交叉表报表中,您希望将 Times Purchased 列的位置掉换到标题行,如图 2 所示。该列变为行,就好像该列逆时针旋转 90 度而变为标题行一样。该象征性的旋转需要有一个支点 (pivot point),在本例中,该支点为 count(state_code) 表达式。

Times Purchased2

图 2 执行了 Pivot 操作的显示

该表达式需要采用以下查询语法:

COPY
Copied to ClipboardError: Could not Copy
...
pivot
(
count(state_code)
for state_code in (‘NY‘,‘CT‘,‘NJ‘,‘FL‘,‘MO‘)
)
...
...
pivot
(
count(state_code)
for state_code in (‘NY‘,‘CT‘,‘NJ‘,‘FL‘,‘MO‘)
)
...
第二行“for state_code ...”限制查询对象仅为这些值。该行是必需的,因此不幸的是,您需要预先知道可能的值。该限制在 XML 格式的查询将有所放宽,如本文后面部分所述。

注意输出中的标题行:

COPY
Copied to ClipboardError: Could not Copy
. TIMES_PURCHASED ‘NY‘ ‘CT‘ ‘NJ‘ ‘FL‘ ‘MO‘
--------------- ---------- ---------- ---------- ---------- ----------
列标题是来自表本身的数据:州代码。缩写可能已经相当清楚无需更多解释,但是假设您希望显示州名而非缩写(“Connecticut”而非“CT”),那又该如何呢?如果是这样,您需要在查询的 FOR 子句中进行一些调整,如下所示:
select * from (
select times_purchased as "Puchase Frequency", state_code
from customers t
)
pivot
(
count(state_code)
for state_code in (‘NY‘ as "New York",‘CT‘ "Connecticut",
‘NJ‘ "New Jersey",‘FL‘ "Florida",‘MO‘ as "Missouri")
)
order by 1
/

Puchase Frequency New York Connecticut New Jersey Florida Missouri


      0      16601         90           0          0          0
      1      33048        165           0          0          0
      2      33151        179           0          0          0
      3      32978        173           0          0          0
      4      33109        173           0          1          0

...

and so on ...
. TIMES_PURCHASED ‘NY‘ ‘CT‘ ‘NJ‘ ‘FL‘ ‘MO‘
--------------- ---------- ---------- ---------- ---------- ----------
列标题是来自表本身的数据:州代码。缩写可能已经相当清楚无需更多解释,但是假设您希望显示州名而非缩写(“Connecticut”而非“CT”),那又该如何呢?如果是这样,您需要在查询的 FOR 子句中进行一些调整,如下所示:
select * from (
select times_purchased as "Puchase Frequency", state_code
from customers t
)
pivot
(
count(state_code)
for state_code in (‘NY‘ as "New York",‘CT‘ "Connecticut",
‘NJ‘ "New Jersey",‘FL‘ "Florida",‘MO‘ as "Missouri")
)
order by 1
/

Puchase Frequency New York Connecticut New Jersey Florida Missouri


      0      16601         90           0          0          0
      1      33048        165           0          0          0
      2      33151        179           0          0          0
      3      32978        173           0          0          0
      4      33109        173           0          1          0

...

and so on ...
FOR 子句可以提供其中的值(这些值将成为列标题)的别名。

Unpivot
就像有物质就有反物质一样,有 pivot 就应该有“unpivot”,对吧?

好了,不开玩笑,但 pivot 的反向操作确实需要。假设您有一个显示交叉表报表的电子表格,如下所示:

Purchase Frequency New York Connecticut New Jersey Florida Missouri
0 12 11 1 0 0
1 900 14 22 98 78
2 866 78 13 3 9
... .
现在,您希望将这些数据加载到一个名为 CUSTOMERS 的关系表中:

COPY
Copied to ClipboardError: Could not Copy
SQL> desc customers
Name Null? Type


CUST_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)
SQL> desc customers
Name Null? Type


CUST_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)
必须将电子表格数据去规范化为关系格式,然后再进行存储。当然,您可以使用 DECODE 编写一个复杂的 SQL*:Loader 或 SQL 脚本,以将数据加载到 CUSTOMERS 表中。或者,您可以使用 pivot 的反向操作 UNPIVOT,将列打乱变为行,这在 Oracle 数据库 11g 中可以实现。

通过一个示例对此进行演示可能更简单。让我们首先使用 pivot 操作创建一个交叉表:

COPY
Copied to ClipboardError: Could not Copy
1 create table cust_matrix
2 as
3 select * from (
4 select times_purchased as "Puchase Frequency", state_code
5 from customers t
6 )
7 pivot
8 (
9 count(state_code)
10 for state_code in (‘NY‘ as "New York",‘CT‘ "Conn",
‘NJ‘ "New Jersey",‘FL‘ "Florida",
‘MO‘ as "Missouri")
11 )
12* order by 1
1 create table cust_matrix
2 as
3 select * from (
4 select times_purchased as "Puchase Frequency", state_code
5 from customers t
6 )
7 pivot
8 (
9 count(state_code)
10 for state_code in (‘NY‘ as "New York",‘CT‘ "Conn",
‘NJ‘ "New Jersey",‘FL‘ "Florida",
‘MO‘ as "Missouri")
11 )
12* order by 1
您可以查看数据在表中的存储方式:

COPY
Copied to ClipboardError: Could not Copy
SQL> select * from cust_matrix
2 /

Puchase Frequency New York Conn New Jersey Florida Missouri


            1      33048        165          0          0          0
            2      33151        179          0          0          0
            3      32978        173          0          0          0
            4      33109        173          0          1          0

... and so on ...
SQL> select * from cust_matrix
2 /

Puchase Frequency New York Conn New Jersey Florida Missouri


            1      33048        165          0          0          0
            2      33151        179          0          0          0
            3      32978        173          0          0          0
            4      33109        173          0          1          0

... and so on ...
这是数据在电子表格中的存储方式:每个州是表中的一个列(“New York”、“Conn”等等)。

COPY
Copied to ClipboardError: Could not Copy
SQL> desc cust_matrix
Name Null? Type


Puchase Frequency NUMBER(3)
New York NUMBER
Conn NUMBER
New Jersey NUMBER
Florida NUMBER
Missouri NUMBER
SQL> desc cust_matrix
Name Null? Type


Puchase Frequency NUMBER(3)
New York NUMBER
Conn NUMBER
New Jersey NUMBER
Florida NUMBER
Missouri NUMBER
您需要将该表打乱,使行仅显示州代码和该州的购物人数。通过 unpivot 操作可以达到此目的,如下所示:

COPY
Copied to ClipboardError: Could not Copy
select *
from cust_matrix
unpivot
(
state_counts
for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
)
order by "Puchase Frequency", state_code
/
select *
from cust_matrix
unpivot
(
state_counts
for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
)
order by "Puchase Frequency", state_code
/
输出如下:

输出如下:

COPY
Copied to ClipboardError: Could not Copy
Puchase Frequency STATE_CODE STATE_COUNTS


            1 Conn                165
            1 Florida               0
            1 Missouri              0
            1 New Jersey            0
            1 New York          33048
            2 Conn                179
            2 Florida               0
            2 Missouri              0

...

and so on ...
Puchase Frequency STATE_CODE STATE_COUNTS


            1 Conn                165
            1 Florida               0
            1 Missouri              0
            1 New Jersey            0
            1 New York          33048
            2 Conn                179
            2 Florida               0
            2 Missouri              0

...

and so on ...
注意每个列名如何变为 STATE_CODE 列中的一个值。Oracle 如何知道 state_code 是一个列名?它是通过查询中的子句知道的,如下所示:

for state_code in ("New York","Conn","New Jersey","Florida","Missouri")

这里,您指定“New York”、“Conn”等值是您要对其执行 unpivot 操作的 state_code 新列的值。我们来看看部分原始数据:

Oracle行列转换函数--Pivot 和 Unpivot

上一篇:链接mysql建库建表


下一篇:MySQL 服务无法启动解决途径