PL/SQL Challenge 每周一题：2022-1-22 PIVOT操作符

〇〇

newkid 发表于 2022-2-6 05:07
要写这么多代码，还不如用最老式的SUM(CASE)了。

如果有好几列group by，每列有很多不同值，数组就有优势了

newkid

我看不出任何优势，如果值很多，鸭子的写法照样要全部罗列出来做笛卡尔积，然后横向的输出列只能够用ORDER BY逻辑，不能任意排列，如果要把列名对应上又得加上一大堆代码。

〇〇

如果值很多，鸭子的写法可以从维度表取所有值，横向输出列一般就是要求有规律的总计-分项，数组比list_agg的字符串，好处就是取数方便

newkid

行转列的目的本来就是要方便数据消费者。现在要求接收数据的人访问数组，还要知道第一个是放什么数据，第二个又是放什么，那还不如ORACLE的PIVOT XML功能，同样可以不限列数，不用枚举，而且省去了笛卡尔积、外连接的开销。

newkid

答案CDE, 本期无人参与。

A:
PIVOT会执行一个隐含的GROUP BY，然后我们在其后又追加了一个显式的GROUP BY ---- 这实际上是可以的。但是即便如此，这个选项还是会出错：
ORA-00979: not a GROUP BY expression.

这是因为我们在ORDER BY里面使用的YEAR并不是来自表中的YEAR, 而是引用了YEAR这个列别名----于是GROUPING(YEAR)变成了GROUPING(CASE GROUPING... END), 这导致了错误。

B:
此处我们试图修复前一选项的错误，方法是将别名S赋予表，并且使用S来修饰对YEAR列的引用。思路是没错的，但是问题在于，表是PIVOT的输入源，而我们在SELECT里面引用的YEAR, GROUP BY 和ORDER BY是来自于输出源。

PIVOT的输出包括所生成的列AMER, ASOC 和 EMEA 以及PIVOT执行的隐式GROUP BY的列----在此处是YEAR。但是即便如此，YEAR列是从PIVOT的输入“传递”给输出的，并不是表中的YEAR，它是PIVOT的YEAR，所以它不是S.YEAR, 所以这个选项会报错：
ORA-00904: "S"."YEAR": invalid identifier.

C:
这个选项和前一选项的唯一区别在于S别名的位置----前一选项为表取了别名，本选项为PIVOT的输出取别名，这就使得所有的S.YEAR引用了正确的YEAR(PIVOT输出的那个，而不是来自表中的那个)。这使得本选项能够工作并且产生所需的输出。（甚至，对表和PIVOT输出都同时取别名也是可以的）

D:
有一种更加简单的解决A选项的问题的办法，就是在最终输出中使用一个和YEAR不同的列别名，这就去除了ORDER BY子句中引用YEAR别名的问题。但是因为我们被要求输出和表列明相同的列名，我们不能够用这个简单的解决办法。

在选项A中，YEAR是PIVOT子句输出所产生的，也是隐式GROUP BY所使用的列----因为它是隐式的，我们不能够给PIVOT子句中的YEAR一个别名。但是我们可以将查询的PIVOT部分作为一个内联视图，在这里我们就可以给PIVOT的输出列YEAR一个别名叫做YR, 这就让我们能够在外层查询的表达式中使用YR, 所以它们就不会ORDER BY的最终列名YEAR产生冲突。

E:
前一选项中的PIVOT执行了一个隐式的GROUP BY来为每个YEAR值产生一行数据，然后PIVOT中的SUM(QTY) 被用来在正确的列中放置QTY----SUM实际上并没有聚合任何东西，因为在表中每个地区每年只有一行数据(我们在这两个列上有一个主键)。接着在PIVOT之后我们实际上在YEAR上面执行了第二个GROUP BY，这使得前一选项执行了双倍的工作量。

所以在这个特例中，我们可以减少工作量，根本就不用PIVOT，而是用“老式”的手工方法执行行转列（这是在PIVOT出现在SQL语法之前我们的做法），使用的是SUM表达式中的CASE表达式。这样的话我们可以用一个GROUP BY同时在水平和垂直方向进行聚合。

我们仍然会碰到ORDER BY中引用到的YEAR列别名的潜在问题，所以我们仍然需要用别名S来修饰YEAR----但是这次就不会有到底S是表别名还是PIVOT的别名这种困扰，因为只有一种选项。