1711g新特性SQL 操作Pivot 和 Unpivot.docx
- 文档编号:10297278
- 上传时间:2023-05-24
- 格式:DOCX
- 页数:15
- 大小:58.91KB
1711g新特性SQL 操作Pivot 和 Unpivot.docx
《1711g新特性SQL 操作Pivot 和 Unpivot.docx》由会员分享,可在线阅读,更多相关《1711g新特性SQL 操作Pivot 和 Unpivot.docx(15页珍藏版)》请在冰点文库上搜索。
1711g新特性SQL操作Pivot和Unpivot
Oracle数据库11g:
面向DBA和开发人员的重要新特性
Pivot和Unpivot
使用简单的SQL以电子表格类型的交叉表报表显示任何关系表中的信息,并将交叉表中的所有数据存储到关系表中。
下载Oracle数据库11g
Pivot
如您所知,关系表是表格化的,即,它们以列-值对的形式出现。
假设一个表名为CUSTOMERS。
SQL>desccustomers
NameNull?
Type
----------------------------------------------------------------------------
CUST_IDNUMBER(10)
CUST_NAMEVARCHAR2(20)
STATE_CODEVARCHAR2
(2)
TIMES_PURCHASEDNUMBER(3)
选定该表:
selectcust_id,state_code,times_purchased
fromcustomers
orderbycust_id;
输出结果如下:
CUST_IDSTATE_CODETIMES_PURCHASED
--------------------------------
1CT1
2NY10
3NJ2
4NY4
...
andsoon...
注意数据是如何以行值的形式显示的:
针对每个客户,该记录显示了客户所在的州以及该客户在商店购物的次数。
当该客户从商店购买更多物品时,列times_purchased会进行更新。
现在,假设您希望统计一个报表,以了解各个州的购买频率,即,各个州有多少客户只购物一次、两次、三次等等。
如果使用常规SQL,您可以执行以下语句:
selectstate_code,times_purchased,count
(1)cnt
fromcustomers
groupbystate_code,times_purchased;
输出如下:
STTIMES_PURCHASEDCNT
---------------------------
CT090
CT1165
CT2179
CT3173
CT4173
CT5152
...
andsoon...
这就是您所要的信息,但是看起来不太方便。
使用交叉表报表可能可以更好地显示这些数据,这样,您可以垂直排列数据,水平排列各个州,就像电子表格一样:
Times_purchased
CTNYNJ...
andsoon...
1010...
22311937...
317451...
...
andsoon...
在Oracle数据库11g推出之前,您需要针对每个值通过decode函数进行以上操作,并将每个不同的值编写为一个单独的列。
但是,该方法一点也不直观。
庆幸的是,您现在可以使用一种很棒的新特性PIVOT通过一种新的操作符以交叉表格式显示任何查询,该操作符相应地称为pivot。
下面是查询的编写方式:
select*from(
selecttimes_purchased,state_code
fromcustomerst
)
pivot
(
count(state_code)
forstate_codein('NY','CT','NJ','FL','MO')
)
orderbytimes_purchased
/
输出如下:
.TIMES_PURCHASED'NY''CT''NJ''FL''MO'
-----------------------------------------------------------------
01660190000
133048165000
233151179000
332978173000
433109173010
...andsoon...
这表明了pivot操作符的威力。
state_codes作为标题行而不是列显示。
下面是传统的表格化格式的图示:
图1传统的表格化显示
在交叉表报表中,您希望将TimesPurchased列的位置掉换到标题行,如图2所示。
该列变为行,就好像该列逆时针旋转90度而变为标题行一样。
该象征性的旋转需要有一个支点(pivotpoint),在本例中,该支点为count(state_code)表达式。
图2执行了Pivot操作的显示
该表达式需要采用以下查询语法:
...
pivot
(
count(state_code)
forstate_codein('NY','CT','NJ','FL','MO')
)
...
第二行“forstate_code...”限制查询对象仅为这些值。
该行是必需的,因此不幸的是,您需要预先知道可能的值。
该限制在XML格式的查询将有所放宽,如本文后面部分所述。
注意输出中的标题行:
.TIMES_PURCHASED'NY''CT''NJ''FL''MO'
-----------------------------------------------------------------
列标题是来自表本身的数据:
州代码。
缩写可能已经相当清楚无需更多解释,但是假设您希望显示州名而非缩写(“Connecticut”而非“CT”),那又该如何呢?
如果是这样,您需要在查询的FOR子句中进行一些调整,如下所示:
select*from(
selecttimes_purchasedas"PuchaseFrequency",state_code
fromcustomerst
)
pivot
(
count(state_code)
forstate_codein('NY'as"NewYork",'CT'"Connecticut",
'NJ'"NewJersey",'FL'"Florida",'MO'as"Missouri")
)
orderby1
/
PuchaseFrequencyNewYorkConnecticutNewJerseyFloridaMissouri
--------------------------------------------------------------------
01660190000
133048165000
233151179000
332978173000
433109173010
...
andsoon...
FOR子句可以提供其中的值(这些值将成为列标题)的别名。
Unpivot
就像有物质就有反物质一样,有pivot就应该有“unpivot”,对吧?
好了,不开玩笑,但pivot的反向操作确实需要。
假设您有一个显示交叉表报表的电子表格,如下所示:
PurchaseFrequency
NewYork
Connecticut
NewJersey
Florida
Missouri
0
12
11
1
0
0
1
900
14
22
98
78
2
866
78
13
3
9
...
.
现在,您希望将这些数据加载到一个名为CUSTOMERS的关系表中:
SQL>desccustomers
NameNull?
Type
----------------------------------------------------------------------------
CUST_IDNUMBER(10)
CUST_NAMEVARCHAR2(20)
STATE_CODEVARCHAR2
(2)
TIMES_PURCHASEDNUMBER(3)
必须将电子表格数据去规范化为关系格式,然后再进行存储。
当然,您可以使用DECODE编写一个复杂的SQL*:
Loader或SQL脚本,以将数据加载到CUSTOMERS表中。
或者,您可以使用pivot的反向操作UNPIVOT,将列打乱变为行,这在Oracle数据库11g中可以实现。
通过一个示例对此进行演示可能更简单。
让我们首先使用pivot操作创建一个交叉表:
1createtablecust_matrix
2as
3select*from(
4selecttimes_purchasedas"PuchaseFrequency",state_code
5fromcustomerst
6)
7pivot
8(
9count(state_code)
10forstate_codein('NY'as"NewYork",'CT'"Conn",
'NJ'"NewJersey",'FL'"Florida",
'MO'as"Missouri")
11)
12*orderby1
您可以查看数据在表中的存储方式:
SQL>select*fromcust_matrix
2/
PuchaseFrequencyNewYorkConnNewJerseyFloridaMissouri
-------------------------------------------------------------------
133048165000
233151179000
332978173000
433109173010
...andsoon...
这是数据在电子表格中的存储方式:
每个州是表中的一个列(“NewYork”、“Conn”等等)。
SQL>desccust_matrix
NameNull?
Type
----------------------------------------------------------------------------
PuchaseFrequencyNUMBER(3)
NewYorkNUMBER
ConnNUMBER
NewJerseyNUMBER
FloridaNUMBER
MissouriNUMBER
您需要将该表打乱,使行仅显示州代码和该州的购物人数。
通过unpivot操作可以达到此目的,如下所示:
select*
fromcust_matrix
unpivot
(
state_counts
forstate_codein("NewYork","Conn","NewJersey","Florida","Missouri")
)
orderby"PuchaseFrequency",state_code
/
输出如下:
PuchaseFrequencySTATE_CODESTATE_COUNTS
---------------------------------------
1Conn165
1Florida0
1Missouri0
1NewJersey0
1NewYork33048
2Conn179
2Florida0
2Missouri0
...
andsoon...
注意每个列名如何变为STATE_CODE列中的一个值。
Oracle如何知道state_code是一个列名?
它是通过查询中的子句知道的,如下所示:
forstate_codein("NewYork","Conn","NewJersey","Florida","Missouri")
这里,您指定“NewYork”、“Conn”等值是您要对其执行unpivot操作的state_code新列的值。
我们来看看部分原始数据:
PuchaseFrequencyNewYorkConnNewJerseyFloridaMissouri
-------------------------------------------------------------------
133048165000
当列“纽约”突然变为一个行中的值时,您会怎样显示值33048呢?
该值应该显示在哪一列下呢?
上述查询中unpivot操作符内的for子句上面的子句对此进行了解答。
您指定了state_counts,它就是在生成的输出中创建的新列的名称。
Unpivot可以是pivot的反向操作,但不要以为前者可以对后者所进行的任何操作进行反向操作。
例如,在上述示例中,您对CUSTOMERS表使用pivot操作创建了一个新表CUST_MATRIX。
然后,您对CUST_MATRIX表使用了unpivot,但这并没有取回原始表CUSTOMERS的详细信息。
相反,交叉表报表以便于您将数据加载到关系表中的不同方式显示。
因此unpivot并不是为了取消pivot所进行的操作。
在使用pivot创建一个表然后删除原始表之前,您应该慎重考虑。
unpivot的某些很有趣的用法超出了通常的强大数据操作功能范围(如上面的示例)。
AmisTechnologies的OracleACE总监LucasJellema介绍了如何生成若干行特定数据用于测试。
在此,我将对他的原始代码稍加修改,以显示英语字母表中的元音:
selectvalue
from
(
(
select
'a'v1,
'e'v2,
'i'v3,
'o'v4,
'u'v5
fromdual
)
unpivot
(
value
forvalue_typein
(v1,v2,v3,v4,v5)
)
)
/
输出如下:
V
-
a
e
i
o
u
该模型可以扩展为包含任何类型的行生成器。
感谢Lucas为我们提供了这一巧妙招术。
XML类型
在上述示例中,注意您指定有效的state_codes的方式:
forstate_codein('NY','CT','NJ','FL','MO')
该要求假设您知道state_code列中显示的值。
如果您不知道都有哪些值,您怎么构建查询呢?
pivot操作中的另一个子句XML可用于解决此问题。
该子句允许您以XML格式创建执行了pivot操作的输出,在此输出中,您可以指定一个特殊的子句ANY而非文字值。
示例如下:
select*from(
selecttimes_purchasedas"PurchaseFrequency",state_code
fromcustomerst
)
pivotxml
(
count(state_code)
forstate_codein(any)
)
orderby1
/
输出恢复为CLOB以确保LONGSIZE在查询运行之前设置为大值。
SQL>setlong99999
较之原始的pivot操作,该查询有两处明显不同(用粗体显示)。
首先,您指定了一个子句pivotxml而不只是pivot。
该子句生成XML格式的输出。
其次,for子句显示forstate_codein(any)而非长列表的state_code值。
该XML表示法允许您使用ANY关键字,您不必输入state_code值。
输出如下:
PurchaseFrequencySTATE_CODE_XML
--------------------------------------------------------------------
1 lumn> n> umn> mn>
2 lumn> n> umn> mn>
...andsoon...
如您所见,列STATE_CODE_XML是XMLTYPE,其中根元素是
每个值以名称-值元素对的形式表示。
您可以使用任何XML分析器中的输出生成更有用的输出。
除了ANY子句外,您还可以编写一个子查询。
假设您有一个优先州列表并希望仅选择这些州的行。
您将优先州放在一个名为preferred_states的新表中:
SQL>createtablepreferred_states
2(
3state_codevarchar2
(2)
4)
5/
Tablecreated.
SQL>insertintopreferred_statesvalues('FL')
2>/
1rowcreated.
SQL>commit;
Commitcomplete.
现在pivot操作如下所示:
select*from(
selecttimes_purchasedas"PuchaseFrequency",state_code
fromcustomerst
)
pivotxml
(
count(state_code)
forstate_codein(selectstate_codefrompreferred_states)
)
orderby1
/
for子句中的子查询可以是您需要的任何内容。
例如,如果希望选择所有记录而不限于任何优先州,您可以使用以下内容作为for子句:
forstate_codein(selectdistinctstate_codefromcustomers)
子查询必须返回不同的值,否则查询将失败。
这就是我们指定上述DISTINCT子句的原因。
结论
Pivot为SQL语言增添了一个非常重要且实用的功能。
您可以使用pivot函数针对任何关系表创建一个交叉表报表,而不必编写包含大量decode函数的令人费解的、不直观的代码。
同样,您可以使用unpivot操作转换任何交叉表报表,以常规关系表的形式对其进行存储。
Pivot可以生成常规文本或XML格式的输出。
如果是XML格式的输出,您不必指定pivot操作需要搜索的值域。
有关pivot和unpivot操作的详细信息,请参考Oracle数据库11gSQL语言参考。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 1711g新特性SQL 操作Pivot Unpivot 1711 特性 SQL 操作 Pivot
![提示](https://static.bingdoc.com/images/bang_tan.gif)