Oracle SQL：无需UNION ALL实现单条多指标记录拆分

针对你需要将单条记录中多个带时间戳的指标拆分为按时间戳分组的多行记录，且避免使用UNION ALL导致的性能问题，推荐使用Oracle原生的UNPIVOT操作，它仅需单次扫描基表，比多次UNION ALL的多轮扫描效率高得多，尤其适合20+指标、大数据量的场景。

实现代码 #

WITH base AS (
    SELECT
        NOFFERED_COUNT,
        NOFFERED_EMIT_DATE,
        TABANDON_COUNT,
        -- 保留你原有的时间戳业务逻辑
        CASE WHEN TABANDON_EMIT_DATE > NOFFERED_EMIT_DATE THEN TABANDON_EMIT_DATE ELSE NOFFERED_EMIT_DATE END AS TABANDON_EMIT_DATE
    FROM METRIC_INTERVAL
    WHERE conversation_id = 'tk421'
),
unpivoted_metrics AS (
    SELECT
        dte,
        metric_name,
        metric_value
    FROM base
    -- 列转行：将每个指标的(时间戳, 数值)对转换为单行记录
    UNPIVOT (
        (dte, metric_value) FOR metric_name IN (
            (NOFFERED_EMIT_DATE, NOFFERED_COUNT) AS 'NOFFERED_COUNT',
            (TABANDON_EMIT_DATE, TABANDON_COUNT) AS 'TABANDON_COUNT'
            -- 扩展20+指标只需按此格式继续添加：(指标时间戳列, 指标数值列) AS '指标名称'
        )
    )
    WHERE dte IS NOT NULL
)
-- 按时间戳聚合，还原指标列结构
SELECT
    dte,
    MAX(CASE WHEN metric_name = 'NOFFERED_COUNT' THEN metric_value ELSE 0 END) AS NOFFERED_COUNT,
    MAX(CASE WHEN metric_name = 'TABANDON_COUNT' THEN metric_value ELSE 0 END) AS TABANDON_COUNT
    -- 对应添加其他指标的CASE语句，格式与上面一致
FROM unpivoted_metrics
GROUP BY dte;

方案说明 #

1. base CTE：保留你原有的业务逻辑，处理TABANDON_EMIT_DATE的取值规则
2. unpivoted_metrics CTE：通过UNPIVOT将多列的指标数据转换为行记录，仅需扫描一次基表，避免UNION ALL的多次IO开销
3. 最终聚合：按时间戳dte分组，用CASE WHEN将行记录重新转换为列结构，确保每个时间戳下的指标值正确映射

性能优势 #

• 相较于UNION ALL的多次表扫描，UNPIVOT仅需单次扫描基表，大幅降低IO消耗
• 指标扩展更简洁：新增指标只需在UNPIVOT的IN子句中添加对应列对，无需重复编写SELECT语句
• 配合METRIC_INTERVAL表上conversation_id字段的索引，可进一步提升查询效率

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