sql - Oracle SQL Pivot 命令不起作用——“标识符无效”

Question

我正在尝试使用 Pivot 在 Oracle SQL 中进行简单查询。我的表中有 12 列，但只有前四列是有意义的。列是“FACILITY_ID”（唯一标识符）、“REPORTING_YEAR”（报告的年份数据）、“SUBPART”（希望以 A 和 Z 之间可接受的值作为数据透视的数据类别）和“CO2E”（报告的浮点数）。我只想从子部分 C 和 K（第三列）中获取数据并将它们添加到自己的列中。

例如：
如果我的原始表格如下所示：

FACILITY_ID --- REPORTING_YEAR --- SUBPART --- CO2E  
10    -------------- 2020 ---------------------- C  -------- 10  
11    -------------- 2020 ---------------------- K  -------- 20  
10    -------------- 2020 ---------------------- K  -------- 40  
10    -------------- 2020 ---------------------- K  -------- 40  
11    -------------- 2020 ---------------------- C  -------- 30

我想得到类似的东西：

FACILITY_ID --- REPORTING_YEAR ----- C ----- K    
10    -------------- 2020 ----------------- 10  ----- 80  
11    -------------- 2020 ----------------- 30  ----- 20  

我现在的代码如下：

Create Table CandK_emissions as
  Select
    FACILITY_ID,
    REPORTING_YEAR,
    SUBPART,
    CO2E
  From
    facilityReport
  Pivot
  (
     Sum(CO2E)
     For SUBPART
     in ('C',
     'K')
  )

我得到的错误是'Ora-00904: "CO2e": Invalid Identifier'。我仔细检查了所有内容是否拼写正确。我不确定我哪里出错了。

标签： sqloraclepivot

Answer 1

该SELECT子句必须显示输出中将存在哪些列，而不是使用基表中的哪些列。在您的查询中，您正在汇总 CO2E 值，分别针对不同的 SUBPART 值。旋转的结果不再有 CO2E 值或 SUBPART 值；相反，您感兴趣的两个 SUBPART 值 C 和 K 在输出中成为两个不同的列，并且 CO2E 的总和出现在这两个新列中。

您的查询应选择列 C 和 K；错误消息告诉您旋转后，没有可供选择的 CO2E 列。（在你改变它之后，它会告诉你关于 SUBPART 的同样的事情。）

这应该是这样的。注意一些事情：在“测试数据”中，我模拟了另外一个列，一个名为 PK 的主键，以证明您必须首先在内联视图中排除它（FROM 子句中的子 SELECT）。另请注意，为了使 PIVOT 尽可能高效，在子查询中我只选择我们需要的行 - SUBPART 为 C 或 K 的行。我们不需要查看其他行。

WITH 子句仅用于测试 - 删除它，并在查询中使用您的实际表名和列名。

with
  test_data (pk, facility_id, reporting_year, subpart, co2e) as (
    select 335, 10, 2020, 'C', 10 from dual union all
    select 440, 11, 2020, 'K', 20 from dual union all
    select 482, 10, 2020, 'K', 40 from dual union all
    select 106, 10, 2020, 'K', 40 from dual union all
    select 476, 11, 2020, 'C', 30 from dual
  )
-- end of test data; actual query begins below this line
select facility_id, reporting_year, c, k
from   (
         select facility_id, reporting_year, subpart, co2e
         from   test_data
         where  subpart in ('C', 'K')
       )
pivot  (sum(co2e) for subpart in ('C' as c, 'K' as k))
order  by facility_id, reporting_year  --  or whatever is needed
;

FACILITY_ID REPORTING_YEAR          C          K
----------- -------------- ---------- ----------
         10           2020         10         80
         11           2020         30         20

注意 - 完全取消 PIVOT 并使用条件聚合（在引入 PIVOT 运算符之前完成旋转的方式）“透视”旧方式可能更简单（也更有效）。像这样的东西：

select facility_id, reporting_year,
       sum(case subpart when 'C' then co2e end) as c,
       sum(case subpart when 'K' then co2e end) as k
from   test_data
group  by facility_id, reporting_year
order  by facility_id, reporting_year
;