sql - 当行丢失时，用 Oracle SQL PIVOT 结果中的自定义值替换 NULL

Question

枢轴查询返回 NULL当没有要旋转的行时。
在以下示例中，id=2 缺少 COLOR 属性。

with src_data (id, attr_name, attr_id, attr_type) as (
    select 1, 'ITALY', 'IT', 'COUNTRY' FROM DUAL UNION ALL --
    select 1, 'GREEN', 'G', 'COLOR' FROM DUAL UNION ALL --
    select 1, 'BIG', 'B', 'SIZE' FROM DUAL UNION ALL --
    select 2, 'FRANCE', 'FR', 'COUNTRY' FROM DUAL UNION ALL --
    select 2, 'SMALL', 'S', 'SIZE' FROM DUAL  --
)
select * from src_data
    PIVOT (MAX(ATTR_NAME) AS NAME, MAX(ATTR_ID) AS ID --
    FOR attr_type IN ('COUNTRY' AS "COUNTRY", 'COLOR' AS "COLOR", 'SIZE' AS "SIZE"));

结果是


ID
国家的名字
COUNTRY_ID
COLOR_NAME
COLOR_ID
SIZE_NAME
SIZE_ID


1
意大利
它
绿色
G
大
乙

2
法国
阻燃剂
空值
空值
小的
秒


see dbfiddle
我想用特定值替换这些空值(例如，使用 N/D 作为名称，使用 -1 作为 ID)。
天真的尝试不起作用

PIVOT (NVL(MAX(ATTR_NAME), 'N/D') AS NAME ... 给 ORA-56902: expect aggregate function inside pivot operation

PIVOT (MAX(NVL(ATTR_NAME, 'N/D')) AS NAME ...仍然给出空结果。我的解释是，甚至从未调用过 NVL，因为根本没有要调用的行(其中 ATTR_TYPE = 'COLOR' AND ID = 2 )

我看到的非常丑陋的解决方案是

添加具体 NVL PIVOT 生成的所有列上的逻辑.我的真实案例有 14 个这样的列。

向 PIVOT 添加假行输入以涵盖此类情况

有什么更好的想法吗？
- - 编辑 - -
貌似原生 pivot不能这样做。我能做的最好的事情是使用外连接添加缺失的行并使用 nvl上真 NULL s 由外连接返回

with src_data (id, attr_name, attr_id, attr_type) as (
    select 1, 'ITALY', 'IT', 'COUNTRY' FROM DUAL UNION ALL
    select 1, 'GREEN', 'G', 'COLOR' FROM DUAL UNION ALL
    select 1, 'BIG', 'B', 'SIZE' FROM DUAL UNION ALL
    select 2, 'FRANCE', 'FR', 'COUNTRY' FROM DUAL UNION ALL
    select 2, 'SMALL', 'S', 'SIZE' FROM DUAL
),
     src_ids_types as (
         select src_ids.id, src_types.attr_type
         from (select distinct id from src_data) src_ids
                  cross join (select distinct attr_type from src_data) src_types
     ),
     full_data as (
         select sit.id, sit.attr_type, d.attr_name, d.attr_id
         from src_ids_types sit
                  left outer join src_data d on d.id = sit.id and d.attr_type = sit.attr_type
     )
select *
from full_data d
    PIVOT (MAX(NVL(ATTR_NAME, 'N/D')) AS NAME, MAX(NVL(ATTR_ID, -1)) AS ID --
    FOR attr_type IN ('COUNTRY' AS "COUNTRY", 'COLOR' AS "COLOR", 'SIZE' AS "SIZE"))

db-fiddle

Answer 1

您可以通过以下方式执行此操作:

生成所有 attr_types 的列表(如果你有这些表格，效果最好。你可以从源数据生成一个 distinct 列表，但这在大型数据集上可能会很慢)

使用 partitioned outer join 将数据外部连接到此在身份证上。这将为每个 id

为上面列表中的每个属性提供一行。

转换 null名称/ID 为 N/A，-1 在子查询中视情况而定

将输出传递给 pivot

这使:

with src_data (id, attr_name, attr_id, attr_type) as (
  select 1, 'ITALY', 'IT', 'COUNTRY' FROM DUAL UNION ALL --
  select 1, 'GREEN', 'G', 'COLOR' FROM DUAL UNION ALL --
  select 1, 'BIG', 'B', 'SIZE' FROM DUAL UNION ALL --
  select 2, 'FRANCE', 'FR', 'COUNTRY' FROM DUAL UNION ALL --
  select 2, 'SMALL', 'S', 'SIZE' FROM DUAL  --
), attrs as (
  select distinct attr_type from src_data
), id_attrs as (
  select id, attr_type,
         nvl ( attr_name, 'N/A' ) attr_name,
         nvl ( attr_id, -1 ) attr_id
  from   attrs a
  left   join src_data d
    partition by ( id ) 
  using ( attr_type ) 
)
select * from id_attrs
pivot (
  max(attr_name) as name, max(attr_id) as id --
  for attr_type in (
    'COUNTRY' AS "COUNTRY", 'COLOR' AS "COLOR", 'SIZE' AS "SIZE"
  )
);

ID    COUNTRY_NAME   COUNTRY_ID   COLOR_NAME   COLOR_ID   SIZE_NAME   SIZE_ID   
    1 ITALY          IT           GREEN        G          BIG         B          
    2 FRANCE         FR           N/A          -1         SMALL       S