Oracle中按元素分组生成SUFFIX2/SUFFIX1计算结果条目

需求说明 #

现有表格数据：

ELEMENTO            VALOR
-------------------------
ELEMENT1_SUFFIX1    2
ELEMENT1_SUFFIX2    4
ELEMENT2_SUFFIX1    5
ELEMENT2_SUFFIX2    15

需求：按ELEMENTX分组，计算每组内SUFFIX2对应的VALOR除以SUFFIX1对应的VALOR，生成以ELEMENTX_RESULT为ELEMENTO的新条目，最终结果保留原表数据并新增计算后的条目，示例输出：

ELEMENTO            VALOR
-------------------------
ELEMENT1_SUFFIX1    2
ELEMENT1_SUFFIX2    4
ELEMENT2_SUFFIX1    5
ELEMENT2_SUFFIX2    15
ELEMENT1_RESULT     2
ELEMENT2_RESULT     3

原表生成SQL #

原表可通过以下代码生成：

with aux (elemento, valor) as
  ( select 'ELEMENT1_SUFFIX1', 2 from dual
  UNION ALL
  select 'ELEMENT1_SUFFIX2', 4 from dual
  UNION ALL
  select 'ELEMENT2_SUFFIX1', 5 from dual
  UNION ALL
  select 'ELEMENT2_SUFFIX2', 10 from dual
  )
select aux.* from aux;

现有实现代码 #

本人已尝试以下实现，希望获得更优方案：

with aux (elemento, valor) as
  ( select 'ELEMENT1_SUFFIX1', 2 from dual
  UNION ALL
  select 'ELEMENT1_SUFFIX2', 4 from dual
  UNION ALL
  select 'ELEMENT2_SUFFIX1', 5 from dual
  UNION ALL
  select 'ELEMENT2_SUFFIX2', 15 from dual
  ),
  aux2 as
  (select aux.*,
    valor/max(
    case
      when regexp_substr(elemento, '[^_]+', 1, 2) = 'SUFFIX1'
      then valor
    end) over (partition by REGEXP_SUBSTR(elemento,'[^_]+',1,1)) new_value
  from aux
  )
select * from aux
union all
select REGEXP_SUBSTR(elemento,'[^_]+',1,1)
  || '_RESULT',
  new_value valor
from aux2
where regexp_substr(elemento, '[^_]+', 1, 2) = 'SUFFIX2';

更优解决方案 #

方案1：分组聚合实现 #

该方案通过提前提取分组键和后缀值，用GROUP BY直接计算每组比值，逻辑清晰且减少正则匹配次数：

WITH aux (elemento, valor) AS (
    SELECT 'ELEMENT1_SUFFIX1', 2 FROM dual
    UNION ALL
    SELECT 'ELEMENT1_SUFFIX2', 4 FROM dual
    UNION ALL
    SELECT 'ELEMENT2_SUFFIX1', 5 FROM dual
    UNION ALL
    SELECT 'ELEMENT2_SUFFIX2', 15 FROM dual
),
grouped_data AS (
    SELECT
        REGEXP_SUBSTR(elemento, '[^_]+', 1, 1) AS element_group,
        MAX(CASE WHEN REGEXP_SUBSTR(elemento, '[^_]+', 1, 2) = 'SUFFIX1' THEN valor END) AS suffix1_val,
        MAX(CASE WHEN REGEXP_SUBSTR(elemento, '[^_]+', 1, 2) = 'SUFFIX2' THEN valor END) AS suffix2_val
    FROM aux
    GROUP BY REGEXP_SUBSTR(elemento, '[^_]+', 1, 1)
)
-- 保留原数据
SELECT elemento, valor FROM aux
UNION ALL
-- 添加计算结果条目
SELECT element_group || '_RESULT', suffix2_val / suffix1_val AS valor FROM grouped_data
ORDER BY elemento;

方案2：窗口函数优化版 #

该方案仅做一次正则提取，用窗口函数获取同组SUFFIX1的值，避免重复正则计算，性能更优：

WITH aux (elemento, valor) AS (
    SELECT 'ELEMENT1_SUFFIX1', 2 FROM dual
    UNION ALL
    SELECT 'ELEMENT1_SUFFIX2', 4 FROM dual
    UNION ALL
    SELECT 'ELEMENT2_SUFFIX1', 5 FROM dual
    UNION ALL
    SELECT 'ELEMENT2_SUFFIX2', 15 FROM dual
),
processed_data AS (
    SELECT
        elemento,
        valor,
        REGEXP_SUBSTR(elemento, '[^_]+', 1, 1) AS element_group,
        -- 一次提取后缀类型
        REGEXP_SUBSTR(elemento, '[^_]+', 1, 2) AS suffix_type,
        -- 窗口函数获取同组SUFFIX1的数值
        MAX(CASE WHEN suffix_type = 'SUFFIX1' THEN valor END) OVER (PARTITION BY element_group) AS suffix1_val
    FROM aux
)
-- 原数据
SELECT elemento, valor FROM processed_data
UNION ALL
-- 生成计算结果，仅基于SUFFIX2行计算
SELECT element_group || '_RESULT', valor / suffix1_val AS valor
FROM processed_data
WHERE suffix_type = 'SUFFIX2'
ORDER BY elemento;

优化亮点 #

• 减少正则函数调用次数：原实现多次调用REGEXP_SUBSTR，优化方案仅在CTE中提取1-2次，提升查询性能
• 逻辑更直观：分组聚合方案直接按组计算比值，窗口函数版清晰分离数据提取与计算逻辑
• 可维护性提升：分组键和后缀类型的提取逻辑集中在一处，便于后续修改

内容的提问来源于stack exchange，提问作者Javi Torre