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

本文介绍了Oracle 11g中的pivot和unpivot查询,这两种转换查询技术能够将行转换成列或反之,用于报表和数据分析。文中详细讲解了pivot的语法、操作示例、别名指定、转换多个列、限制和性能,以及unpivot的用法、处理空数据、别名选项和执行计划。通过实例展示了这两种操作的灵活性和效率,并与早期转换查询技术进行了比较。
摘要由CSDN通过智能技术生成

对于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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值