oracle unpivot 索引_oracle 11g中的pivot和unpivot转换操作

对于pivot和unpivot这个Oracle 11g的新功能一直就想总结一下，正好赶上论坛中有很多人会提问相关的问题，于是利用空闲时间翻译了一篇相关的网络文章。链接如下：

ITPuber：LuiseDalian翻译

时间：2012.11.29

1 Oracle 11g中的pivot和unpivot查询

转换查询包括将行转换成列(pivot)和将列转换成行(unpivot)，从 而产生交叉表格式的结果。 转换是一项通用的技术，尤其对于报表来讲，正是由于该项技术使得多年来针对Oracle的不同版本，可以使用SQL语句产生转换查询的结果集。从 Oracle 11g开始，包含了显式的转换查询支持，即PIVOT和UNPIVOT关键字。这是对SELECT语句的扩展。

1.1 pivot

这里我们从pivot操作开始。大多数的开发者首先要熟悉转换数据：即多行被聚合，然后转换成列，每一列表示聚合数据的不同范围。语法：

除了新的pivot关键字外，可以看到3个新的pivot子句，描述如下：

 pivot_clause：定义被聚合的列(pivot是一种聚合操作)

 pivot_for_clause：定义被分组和转换的列。

 pivot_in_clause：定义pivot_for_clause子句中列的过滤器(例如使用值的范围来限制结果)。pivot_in_clause中每个值的聚合都将被转换成单独的列

1.1.1 一个简单的例子

这个例子是对pivot语法的简单的演示，使用scott.emp表，这里将通过department

和job来汇总salaries，然后转换对每个部门的汇总到它们各自的列上，在转换之前，先看一下基础数据

下面使用11g的语法来转换数据，对于每个job我们将sal的汇总，对每个部门使用单独一列。 可以看到对每个job按部门对sal的汇总已经变成了列，关于这个例子有几点需要注意，语法和结果：

 本例中的pivot_clause求sal列的和，如果需要可以指定多列和可选的别名。

 pivot操作执行隐式的GROUP BY，使用的列是没有出现在pivot_clause中的列(本例为job

和deptno)。大多数的转换查询都会在列的子集上进行操作，所以会使用存储视图、内联视图或子查询。

 这里的pivot_for_clause，表明我们只想转换deptno的聚合。

 这里的prvot_in_cluase，指明deptno值的范围，这里我们硬编码4个值的列表，这也是为什

么会产生4个转换列原因。如果省略别名，Oracle会使用pivot_in_clause中的值来产生转换后列的别名

如上所述，大多数的转换查询都在是列的特定的子集上来执行。像所有的聚合查询一样，额外列的出现会影响分组。这里只是通过在EMP表带有附加列的情况下进行转换查询，来确认这种影响。

在本例中，除了sal列，其余所有列都成了分组集，其中deptno为转换列。在本例中这个转换没有什么意义。

关于转换语法的非常有趣的一点是它在查询中的位置，即位于FROM和WHERE子句之间。 在下面的例子中，我们限制原来的转换查询在选择(通过添加谓词)出来的一些职位上进行。

上面的例子看起来好像是违反语感的，但是把谓词添加到pivot子句之前会产生语法错误。另外在我们的第1个例子中，我们是使用子查询部分(WITH语句)来定义基列的集合。我们可以使用内联视图(下例)或存储视图(后面的练习)来替代子查询。

1.1.2 给转换列指定别名

在前面的例子中，Oracle使用deptno列的值产生转换后列的名称，然而 我们可以给pivot_clause和pivot_in_clause中的一列或多列指定别名。通常Oracle将根据下面约定给转换列命名。 总结：就是pivot_in_clause中的列，如果指定别名就用别名，如果没有指定就用值；而pivot_cluaus中如果指定别名就连接到 pivot_in_cluase转换后的相应列的名称的后面，如果没有指定就不用。

5 / 41

下面将是这些别名选项的示例。我们已经做过没有任何别名的例子。为了简化起见，我们将输入数据集定义为视图。

给转换查询的所有元素指定别名。(Y, Y)，Oracle将对应别名连接起来产生列名。

pivot_in_clause中的列不指定，pivot_clause中的列指定别名。(N, Y)

Oracle连接pivot_in_clause中的值和聚合列的别名产生列名。

只给

pivot_in_clause中的值指定别名。(Y，N)，Oracle仅使用pivot_in_clause中的别名产生列名

总结：在所有的例子中pivot_in_clause用于所有的转换列的命名，不管我们是指定了值还是别名。因此，我们可以选择对哪些值指定别名。

1.1.3 转换多个列

目前为止，我们的例子都限制在一个聚合和一个转换列，然而我们可以定义多个。在下面的例子中将在pivot_clause中定义2个聚合，而对于deptno范围使用与前面的例子相同的值。新的聚合是对求和的薪水的计数。

这里得到的转换列是双倍(因为聚合的数量是双倍)。

转换列的数量 = 聚合列的数量 * pivot_in_clause中值的个数

下面的例子中，扩展了pivot_for_clause和pivot_in_clause。

这里限制查询30号部门中的3个职位。注意pivot_for_clause中的列(deptno, job)如何组合成一个转换维度。使用的别名应用了值域的组合(例，D30_SLS，代表30号部门的SALES)

因为我们已经知道了转换列的命名规则，所以我们可以直接引用它。

9 / 41

1.1.4 一般限制

对于理解转换查询有一些简单的陷阱。例，不能投影在 pivot_for_clause中使用的列(在前面的例子中为deptno列)，这是预料之中的。在pivot_for_clause中的列会根据我们 在pivot_in_clause中提供的值的范围进行分组。下列中试图投影deptno列，Oracle引发ORA-00904异常，在这种情况 下，deptno完全从投影中被移除，并且Oracle可以告诉我们它在作用域中不存在。

10 / 41

同样也不能投影任何出现在pivot_clause中的列。这里试图投影sal列，引发了同样的异常。这也是可以预计到的：pivot_clause定义了查询中的聚合。因此这也意味着我们必须在pivot_clause中使用聚合函数。

下例中对于pivot_clause中的列没用使用聚合函数。引发ORA-56902异常。错误消息编号随着版本变化而越来越大。

1.1.5 转换操作的执行计划

像前面讲过的一样，转换操作隐式执行一个GROUP BY操作，但不需要我们指定它。我们可以通过解析我们前面的4个例子之一来确定这一点。为了方便我们将使用autotrace(它使用EXPLAIN PLAN和DBMS_XPLAN来显示推断出的执行计划)。

上面输出的执行计划告诉我们这个查询使用了HASH GROUP BY PIVOT操作。HASH GROUP BY是10gR2的功能，而PIVOT为11g的新功能。PIVOT查询不会自动产生PIVOT计划。

然而在下面的例子中，我们将限制pivot_in_clause中的值域并使用autotrace来再次解析查询。

这次CBO开销为一个简单的聚合，是通过使用pivot分组来完成的。它已经正确地识别出对于这个查询只有一条记录被返回，所以group by操作不是必须的。

最后我们将解析我们的第一个转换查询的例子，但是会使用DBMS_XPLAN的扩展格式化选项来获取Oracle所做的工作的更多的信息。

DBMS_XPLAN选择性地显示了包含在PLAN_TABLE表中的查询的每 一步的列的投影信息。 ID = 2的投影操作表明建立在EMP基表上的PIVOT_DATE视图中我们选择的基列。最有趣味的信息却是ID = 1(这一步是转换操作)。这里清楚地显示了Oracle是如何产生转换列的。许多开

发者对于这种形式的SQL很熟悉，这是我们在11g之前我们写转换查询的方法。Oracle 选择了case表达式，但我们通常是使用 decode函数。

1.1.6 pivot性能

从我们看到的种种迹象表明，好像Oracle使用已经被大家认可的SQL形式实 现了新的PIVOT语法。我们期望我们的转换查询有相同的性能，而不管我们使用的是哪种技术。(即11g pivot语法和sum(decode..)转换技术将执行相同的操作。我们将使用autotrace(通常的I/O模式)和wall-clock(花费 的时间)对大的数据集来测试我们的假设。首先，我们创建一个有100万行记录的表。

我们将比较这2个转换查询技术(在我们对表million_rows进行几次全扫描之后，避免结果具有随机性)。下面从Oracle 11g的新语法开始。

最重要的输出已经被用红色下划线标出。即整个查询用时为4.06

秒，产生大约

14000 的PIO和LIO。更有趣的是CBO针对这个volume的查询采用了基于HASH GROUP BY的SORT GROUP BY，计算了大约120万的记录(1115K)。

作为比较，我们将运行如下的11g之前的转换查询。

如果没有异常情况的话，该查询的时间和资源消耗的结果与新的pivot语法差不多。这就是我们所期望的。事实上，这种查询 的新的转换版本会产生更多的递归SQL，和更多的内存排序，但我们可以通过这个简单的测试得出结论，新的技术没有性能上的损失。下面我们将对转换更多的行 来验证这个结论。

SQL>set timing on

SQL> set autotrace traceonly statistics

WITH pivot_data AS

(

SELECT pivoting_col, grouping_col, summing_col

FROM million_rows

)

SELECT *

FROM pivot_data

PIVOT

(

SUM(summing_col) AS sum,

COUNT(summing_col) AS cnt,

AVG(summing_col) AS av,

MIN(summing_col) AS mn,

MAX(summing_col) AS mx

FOR pivoting_col IN (0,1,2,3)

)

ORDER BY grouping_col;

18 / 41

这个例子中会产生20个转换列，注意，上面的输出是例子第3次或第4次运行的输出，这样做是为了避免结果的交叉。最终I/O模式和消耗的时间与前面的4个例子是相同的，除了转换了额外的16列之外。下面将与使用SUM(DECODE…)技术进行比较。

SELECT grouping_col, SUM(DECODE(pivoting_col,0,summing_col)) AS "0_SUM", COUNT(DECODE(pivoting_col,0,summing_col)) AS "0_CNT", AVG(DECODE(pivoting_col,0,summing_col)) AS "0_AV",

MIN(DECODE(pivoting_col,0,summing_col)) AS "0_MN",

MAX(DECODE(pivoting_col,0,summing_col)) AS "0_MX",

SUM(DECODE(pivoting_col,1,summing_col)) AS "1_SUM", COUNT(DECODE(pivoting_col,1,summing_col)) AS "1_CNT", AVG(DECODE(pivoting_col,1,summing_col)) AS "1_AV",

MIN(DECODE(pivoting_col,1,summing_col)) AS "1_MN",

MAX(DECODE(pivoting_col,1,summing_col)) AS "1_MX",

SUM(DECODE(pivoting_col,2,summing_col)) AS "2_SUM", COUNT(DECODE(pivoting_col,2,summing_col)) AS "2_CNT", AVG(DECODE(pivoting_col,2,summing_col)) AS "2_AV",

MIN(DECODE(pivoting_col,2,summing_col)) AS "2_MN",

MAX(DECODE(pivoting_col,2,summing_col)) AS "2_MX",

SUM(DECODE(pivoting_col,3,summing_col)) AS "3_SUM", COUNT(DECODE(pivoting_col,3,summing_col)) AS "3_CNT", AVG(DECODE(pivoting_col,3,summing_col)) AS "3_AV", MIN(DECODE(pivoting_col,3,summing_col)) AS "3_MN",

MAX(DECODE(pivoting_col,3,summing_col)) AS "3_MX"

FROM million_rows

GROUP BY grouping_col

ORDER BY grouping_col;

19 / 41

我们开始看到了新的PIVOT语法是多么地方便，而且，尽管2个方法的工作量相差无几，但老的人工转换技术要比新技术慢很多啊(3.22<>11.75)(对于本例的多次运行，此外也运行了另外的使用CASE替代DECODE的版本)

1.1.7 转换值的未知域

到目前为止我们的例子都是转换值的已知的域，即我们在 pivot_in_clause中使用的是硬编码。我们使用的pivot语法，即默认的语法，不支持pivot_in_clause中动态的值列表。如果 我们在pivot_in_clause中使用一个子查询而不是一个列表，Oracle会产生异常。

许多开发者可能会认为这是一个主要的限制(而没有考虑到，事实上在11g之前的转换技术也是需要我们显式地编码值的集合)。这有可能产生未知的转换值的集合。记得在前面的简单的语法浏览中PIVOT支持XML这个可选关键字。就像关键字暗示的一样，这允许我们产生转换的结

20 / 41

果集，但是以XML格式提供的。作为它的扩展，我们可以为任何数量的转换列产生XML结果集，就像定义动态的pivot_in_clause一样。

使用XML扩展，我们有3个选项来产生pivot_in_clause：

 可以使用值的显式列表。(前面的例子一直是这样做的)

 在pivot_in_clause中使用ANY关键字，这意味着我们希望转换pivot_in_clause中列的所有

值。

 可以在pivot_in_clause中使用子查询来获取值的列表。

我们将集中精力于这些动态方法。下例中将使用ANY对数据库中DEPTNO列的所有值产生转换结果集。

XML结果集为XMLType类型，这意味着我们可以轻松地使用XPath或 Xquery表达式来操作它。可以看到产生的转换列的命名是根据pivot_clause而不是pivot_in_clause(记得在非XML查询 中，pivot_in_clause值或者是别名在所有的转换列的命名中起主要作用)。我们也可以看到XML列名本身是由 pivot_for_clause产生的：Oracle在DEPTNO后面附加了_XML。

下面我们重复前面的练习，但给privot_clause添加了一个别名。如果想将改变列名DEPTNO_XML，可以使用SQL标准别名。

建议pivot_clause别名定义了转换的XML元素的名称，而XML列名本身是通过投影别名来定义的。

对于ANY的一个替代的方法是使用子句。下面的例子中使用在DEPT表表上的查询来替代ANY，获取DEPTNO值的列表。

可以看到和使用ANY的关键区别是：当使用子查询方法时，Oracle对于子查 询返回的每个值产生一个转换后的XML元素(对于每个分组)。例如，ANALYST员工只工作于20号部门，因此ANY方法对于该部门返回一个转换XML 元素。然而子查询方法会产生4个XML元素(对于10, 20, 30, 40号部门)，但只有20号部门是非空的。如果我们从2种pivot_in_cluase使用方法的结果中抽取薪水元素，将会看得更清楚。

使用ANY时只对20号部门产生一个XML元素，下面是使用子查询。

这里不管3个部门没有薪水的汇总，Oracle对每个产生一个空元素。只有20部门有薪水汇总。开发者选择哪种方法，依赖于实际的需求，但是要认识到使用XML会产生过多的数据集或结果集。在这方面，子查询方法可能潜在会产生比需要的结果集多得多的额外的数据。

1.2 unpivot

在前面已经详细浏览了11g 的pivot转换的功能。下面看一看新的unpivot操作符。像名称一样unpivot是pivot操作的逆操作(虽然不能反聚合数据)。简单的想法就 是它能将转换后的列转换成行(对于反转换的每一列产生一行数据)。下面是一些例子，在例子之前先看一下语法。

除了一些细微的差别外，基本上与pivot的语法类似，包括各个子句的含义。

 unpivot_clause：为表示反转换的计算值的列指定列的名称，在前面的转换的例子中，计算列

为对于每个job和department分组的汇总。

 unpivot_for_clause：指定反转换查询的结果列的名称。该列中的数据描述了unpivot_clause

中列的计算值。

 unpivot_in_clause：包含了即将被反转换的pivot操作后列的列表(不是值)

unpivot子句很难被描述清楚，最好通过一些例子来解释。

24 / 41

1.2.1 简单unpivot示例

在进行unpivot查询之前，先创建一个pivot操作后的结果集用于后面的练习。这里使用前面练习过的一个pivot查询。

pivoted_data视图中包含通过job进行分组的部门的薪水的和，记住这些数据的特征，后面将查询该视图。

下面使用11g的新语法进行unpivot操作

可以看到Oracle已经转换了每个在unpivot_in_cluase中的 列(它们是pivot操作的结果)，把它们转换成了数据行，这些数据描述了我们的度量(例, D10_SAL， D20_SAL等等)。unpivot_for_clause给unpivovit操作后的结果列一个名称 (SALDESC)；unpivot_clause本身定义了计算的数据，这里就是每个职位的部门薪水汇总。

注意：unpivot查询可以作用于任何例(不仅仅是聚合后的列或pivot操作后的列。这里使用pivot操作的结果集，目的是为了举例的连续性，实际上可以容易地对任何表任何视图的列进行unpivot操作。

1.2.2 处理空数据

一个unpivot查询可以返回的最大行数为：分组数 * 转换操作列(本文的示例中为5(jobs) * 4(转换列)。然而第一个unpivot练习只返回了9行。如果查看源pivot数据本身，在pivot列中只有9个非空数值。换言之，11个分组为空。 unpivot操作默认会排除null值。当然也有选项让结果包含这些空值。

26 / 41

通过包含NULL值，返回了可能从结果集获取的最大行数。当然，现在有11个空值，但这可能是为了报表或使数据更加严密的目的。

1.2.3 unpivot别名选项

在本文的pivot部分介绍了各种不同的别名选项。unpivot语法中也允许使用别名，但使用上更严格。事实上，只能给unpivot_in_clause中定义的列指定别名。

27 / 41

通过包含NULL值，返回了可能从结果集获取的最大行数。当然，现在有11个空值，但这可能是为了报表或使数据更加严密的目的。

1.2.3 unpivot别名选项

在本文的pivot部分介绍了各种不同的别名选项。unpivot语法中也允许使用别名，但使用上更严格。事实上，只能给unpivot_in_clause中定义的列指定别名。

27 / 41

这个练习表明在unpivot_in_cluase中使用类型转换也不是有效的语法，Oracle会相应地引发异常。因此一个变通的方案是使用内联视图、子查询或存储视图预先对列进行转换，下列中就是使用子查询方法。

29 / 41

由于篇幅原因，结果没有全部输出，

但可以看到对emp表中数据的unpivot操作的效果。(例如，有3个列进行unpivot操作，原始数据的行数为12行，因此输出36行记录)。

另一个限制是在unpivot_in_clause中的列，是不可以在unpivot_clause外进行投影操作的，下列是试图投影deptno列。

Oracle产生标识符无效的异常。

当我们投影出所有有效的列时就可以看出为什么会这样。

可以看出unpivot操作的列是不能不作为投影的一部分来使用的。

1.2.5 unpivot操作的执行计划

在前面的pivot查询的执行计划中，看到有GROUP BY PIVOT操作。在下面的例子中，使用autotrace来产生最后一个unpivot查询的执行计划。

感兴趣的点已经被加上了红色的下划线。首先看到了新的unpivot步骤 (ID=3)；第2是一个Filter谓词移除了deptsal所有的NULL值，这是默认的EXCLUDING NULLS子句的结果。如果使用INCLUDING NULLS子句，则该过滤器被移除；注意，位于PIVOTED_DATE视图下面的GROUP BY PIVOT操作(ID=5)是由pivot查询产生的。

下面会使用DBMS_XPLAN的格式化选项来抽取更为详细的关于该执行计划的信息。特别要查看一下别名和投影的细节，看看是否提供了unpivot操作线索。

unpivot操作的列的投影是上面的1-3步操作。这并没有提供Oracle如何实现unpivot的细节。注意：10046跟踪也没有提供细节，所以本文并未提及10046跟踪。

别名的信息有些意思，但也没有告诉关于unpivot的信息。It might be a red herring。当Oracle转换一个简单查询的时，产生的查询块的别名通常跟一个后缀，例如"SEL$1", "SEL$2"等，在本例中的unpivot查询中别名非常大，像SEL$17，因此这是一个有很少组件的非常简单的查询。这可能是暗示在优化之前进行了 许多查询重写，只是不能从上面的细节得到确认。

1.2.6 unpivot的其它使用

unpivot查询不仅限于转置pivot操作后的数据。可以转换表的任何列的 集合(但数据类型必须满足约束)。Tom Kyte的print_table过程就是一个很好的例子。这个工具unpivot宽的记录，这样就可以读数据一直到页的底部，而不用交叉来读。新的 unpivot可以用来实现同样的目的。下例中写的是一个静态的unpivot查询，它与print_table工具类似。

34 / 41

1.2.7 11g之前的unpivot查询

在结束本文之前，将阐述几中11g之前的unpivot查询的技术并比较它们的性能。

第1种方法使用产生虚拟rowsource的笛卡尔乘积。rowsource具有进行unpivot操作的列数相同的行数。下面将使用前面unpivot示例相同的数据集进行说明。

这个结果与使用INCLUDING NULLS的unpivot操作的结果是一样的。

第2种技术是将pivot操作后的结果集与想转换的列的集合进行连接操作。

下例中使用普通的NUMBER_NTT嵌套表类型来保存被转换的部门薪水列。可以使用数据类型，因为所有的被转换的列都为NUMBER类型。

这里已经转换了部门的薪水，但却丢失了每个值班的描述性的标签。没有简单的方法来decode行号(像在笛卡尔乘积方法中一样)。然而却可以改变集合的类型来包含一个描述标签。为此，先创建一个通用对象类型来定义单个的数据转换数据行。

37 / 41

第37 / 41页

可以看出11g的unpivot语法比11g之前的替代方法列容易，下面将使用autotrace、花费的时间和million_rows测试表来比较这些方法的性能。下面从11g的开始，转换测试表中的3个数值列。 38 / 41

第38 / 41页

11g的unpivot方法用时1分32秒，产生300万行记录，比前面的pivot操作的逻辑I/O稍微多一些。下面将与笛卡尔乘积方法进行比较，但使用了rowsource技术不会产生额外的I/O(这里不是使用all_object视图)

39 / 41

第39 / 41页

笛卡尔乘积方法明显慢于11g的unpivot方法。它产生了更多的I/O，耗 时2倍多。然而研究SQL跟踪表明，额外的I/O是由于对临时表空间的直接读和直接写造成的，来支持大规模的内存排序。(例如，伴随着MERGE JOIN CARTESIAN操作的排序)。在大多数的商业系统中，这种内存排序可能完全运行于内存或访问更快的临时表空间。因为小的系统通常是慢速的磁盘访问，这 对于性能有很大影响。这里可以通过整体上强制嵌套循环连接或避免磁盘排序来进行优化。 40 / 41

第40 / 41页

这里已经在同一数据库上显著地降低了消耗的时间和I/O

，但却引入了100万的微排序。可以反向嵌套循环排序或使用NO_USE_MERGE指令，但这会加倍I/O并增加大约10%的时间消耗。 最后比较集合方法。

这个方法与unpivot相比，I/O方面差不多，但却慢了35%-40%。进一步研究SQL跟踪表明，增加的CPU时间花费在了集合的迭代获取上。因此unpivot操作是即容易编码，也比其它SQL替代方法更快。

41 / 41