Oracle 18c日志表分组填充缺失行以实现数据透视

要实现每个GROUP_ID分组都包含TYPE 1-5的所有行，缺失类型的log_tags为空，核心思路是先生成所有GROUP_ID与TYPE 1-5的完整组合，再和原表左连接补全缺失数据。

方案一：生成补全后的数据集（用于后续透视） #

直接查询得到包含完整TYPE的分组数据，无需修改原表：

WITH all_required_rows AS (
    -- 生成所有分组ID与TYPE 1-5的笛卡尔积
    SELECT 
        g.GROUP_ID,
        t.TYPE
    FROM (SELECT DISTINCT GROUP_ID FROM LOGS) g
    -- 快速生成TYPE 1到5的集合
    CROSS JOIN (SELECT LEVEL AS TYPE FROM DUAL CONNECT BY LEVEL <= 5) t
)
SELECT 
    arr.GROUP_ID,
    arr.TYPE,
    -- 原表存在则取对应log_tags，否则为空
    COALESCE(l.log_tags, NULL) AS log_tags
FROM all_required_rows arr
LEFT JOIN LOGS l 
    ON arr.GROUP_ID = l.GROUP_ID 
    AND arr.TYPE = l.TYPE
-- 按分组和TYPE排序，方便查看
ORDER BY arr.GROUP_ID, arr.TYPE;

方案二：直接向原表插入缺失的行 #

如果需要永久补全原表数据，用INSERT语句插入缺失的GROUP_ID-TYPE组合，log_tags设为NULL：

INSERT INTO LOGS (GROUP_ID, TYPE, log_tags)
SELECT 
    g.GROUP_ID,
    t.TYPE,
    NULL AS log_tags
FROM (SELECT DISTINCT GROUP_ID FROM LOGS) g
CROSS JOIN (SELECT LEVEL AS TYPE FROM DUAL CONNECT BY LEVEL <= 5) t
-- 仅插入原表中不存在的组合
WHERE NOT EXISTS (
    SELECT 1 FROM LOGS l 
    WHERE l.GROUP_ID = g.GROUP_ID 
    AND l.TYPE = t.TYPE
);

关键逻辑说明 #

1. 生成完整组合：通过CROSS JOIN将所有分组ID与TYPE 1-5进行全量匹配，确保每个分组都有5个TYPE条目。
2. 左连接补空：左连接原表后，原表中不存在的条目会自动用NULL填充log_tags，正好满足需求。
3. 高效生成TYPE集合：用CONNECT BY LEVEL <=5替代多次UNION ALL，更简洁高效。

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