Oracle PIVOT-未使用的列会影响汇总

Question

我有一个数据集，它由几个关键字段、一个状态和一个计数组成。PIVOT做得很好，为我提供了每个关键字段一行的组合，每个状态一列。下面的示例：

with dataquery (field1, field2, field3, my_status, statcount) as (
select 'AAA', 'BBB', 'CCC', 'XX', 11 from dual
union
select 'AAA', 'BBB', 'CCC', 'YY', 22 from dual
union
select 'AAA', 'BBB', 'CCC', 'ZZ', 33 from dual
), 
pivotquery as (
SELECT field1,
field2,
field3,
xx_stat_count     AS xx,
yy_stat_count     AS yy,
zz_stat_count     AS zz
FROM dataquery
PIVOT (
SUM (statcount) AS stat_count
FOR (my_status)
IN ('XX' AS XX,
'YY' AS yy,
'ZZ' AS zz
)
)
)
select * from pivotquery;

这给出了预期的结果：

FIELD1   FIELD2  FIELD3  XX  YY  ZZ
AAA      BBB     CCC     11  22  33

然而，如果我在数据中有额外的列，它不会像我预期的那样忽略它们并将其汇总。它似乎试图将未使用的列用作分组逻辑的一部分，而不是忽略它们。如果我将输入更改为：

with dataquery (field1, field2, field3, my_status, statcount, cnt2, cnt3, cnt4) as (
select 'AAA', 'BBB', 'CCC', 'XX', 11, 12, 13, 14 as cnt4 from dual
union
select 'AAA', 'BBB', 'CCC', 'YY', 22, 22, 23, 24 as cnt4  from dual
union
select 'AAA', 'BBB', 'CCC', 'ZZ', 33, 32, 33, 34 as cnt4  from dual
union
select 'AAA', 'BBB', 'CCC', 'YY', 122, 32, 33, 34 as cnt4  from dual
)

我得到这个：

FIELD1 FIELD2 FIELD3  XX  YY   ZZ
AAA    BBB    CCC         22
AAA    BBB    CCC         122  33
AAA    BBB    CCC     11

而不是预期的

FIELD1 FIELD2 FIELD3  XX  YY   ZZ
AAA    BBB    CCC     11  144  33

如果我进一步更改输入数据，这样就不会重复其他字段，它显示为4行：

with dataquery (field1, field2, field3, my_status, statcount, cnt2, cnt3, cnt4) as (
select 'AAA', 'BBB', 'CCC', 'XX', 11, 12, 13, 14 as cnt4 from dual
union
select 'AAA', 'BBB', 'CCC', 'YY', 22, 22, 23, 24 as cnt4  from dual
union
select 'AAA', 'BBB', 'CCC', 'ZZ', 33, 32, 33, 34 as cnt4  from dual
union
select 'AAA', 'BBB', 'CCC', 'YY', 122, 132, 33, 34 as cnt4  from dual
)

给出

FIELD1 FIELD2 FIELD3  XX  YY   ZZ
AAA    BBB    CCC         22
AAA    BBB    CCC              33
AAA    BBB    CCC         122  
AAA    BBB    CCC     11

这是预期的行为吗？

我可以绕过它，要么事先添加一个子查询，只选择PIVOT处理的字段1、字段2、字段3、mystatus和statcount，要么在之后进行选择/分组，将事情汇总起来。

Answer 1

这是预期行为吗？

是，来自SELECT文档：

pivot_clause计算在子句开头指定的聚合函数。聚合函数必须指定GROUP BY子句才能返回多个值，但pivot_clause不包含显式GROUP BY子句。相反，pivot_clause执行隐式GROUP BY。隐式分组基于pivot_clause中未引用的所有列，以及pivot_in_clause中指定的值集。(。如果指定了多个聚合函数，则必须至少为除一个聚合函数之外的所有聚合函数提供别名。

---

若要解决此问题，请仅对SELECT所需列使用子查询，以便其他列不会隐式用作GROUP BY子句：

with dataquery (field1, field2, field3, my_status, statcount, cnt2, cnt3, cnt4) as (
select 'AAA', 'BBB', 'CCC', 'XX', 11, 12, 13, 14 as cnt4 from dual
union
select 'AAA', 'BBB', 'CCC', 'YY', 22, 22, 23, 24 as cnt4  from dual
union
select 'AAA', 'BBB', 'CCC', 'ZZ', 33, 32, 33, 34 as cnt4  from dual
union
select 'AAA', 'BBB', 'CCC', 'YY', 122, 132, 33, 34 as cnt4  from dual
)
SELECT field1,
field2,
field3,
xx_stat_count     AS xx,
yy_stat_count     AS yy,
zz_stat_count     AS zz
FROM   (
SELECT field1,
field2,
field3,
my_status,
statcount
FROM   dataquery
)
PIVOT (
SUM (statcount) AS stat_count
FOR (my_status) IN (
'XX' AS XX,
'YY' AS yy,
'ZZ' AS zz
)
);

输出：

YYZZCCC>

FIELD1	FIELD2		XX
AAA	BBB	11	144	33