Oracle SQL：统计子集列重复记录并展示差异列值

核心思路 #

你需要的是**行转列（PIVOT）**而非反向的UNPIVOT，这就是之前尝试UNPIVOT失败的原因。核心步骤：

1. 给每个重复分组（PROTOCOL、SURVEY、DATE、ENTITY）内的记录分配序号，同时统计组内总条数。
2. 用PIVOT把同一分组内不同序号的CREATION_TIME转为横向列。
3. 过滤出组内条数≥2的结果。

静态列实现（适用于已知最大重复数的场景） #

假设每组最多有3条重复记录，直接写固定列的查询即可：

WITH ranked_records AS (
    SELECT 
        PROTOCOL,
        SURVEY,
        "DATE", -- DATE是Oracle关键字，需用双引号包裹或修改字段名
        ENTITY,
        CREATION_TIME,
        -- 给组内记录按CREATION_TIME排序分配序号
        ROW_NUMBER() OVER (PARTITION BY PROTOCOL, SURVEY, "DATE", ENTITY ORDER BY CREATION_TIME) AS rn,
        -- 统计组内总条数
        COUNT(*) OVER (PARTITION BY PROTOCOL, SURVEY, "DATE", ENTITY) AS COUNTER
    FROM your_table -- 替换为你的表名
)
SELECT 
    PROTOCOL,
    SURVEY,
    "DATE",
    ENTITY,
    COUNTER,
    "1" AS CREATION_TIME_1,
    "2" AS CREATION_TIME_2,
    "3" AS CREATION_TIME_3 -- 可根据实际最大重复数增加更多列
FROM ranked_records
PIVOT (
    -- 取对应序号的CREATION_TIME，用MAX是因为每个rn对应唯一值
    MAX(CREATION_TIME) FOR rn IN (1, 2, 3)
)
WHERE COUNTER >= 2
ORDER BY PROTOCOL, SURVEY, "DATE", ENTITY;

动态列实现（适用于未知最大重复数的场景） #

如果每组重复数量不固定，用动态SQL自动生成所有需要的列：

DECLARE
    pivot_col_list VARCHAR2(4000);
    pivot_val_list VARCHAR2(4000);
BEGIN
    -- 生成PIVOT需要的列名和值列表
    SELECT 
        LISTAGG('''' || rn || ''' AS CREATION_TIME_' || rn, ', ') WITHIN GROUP (ORDER BY rn),
        LISTAGG(rn, ', ') WITHIN GROUP (ORDER BY rn)
    INTO pivot_col_list, pivot_val_list
    FROM (
        SELECT DISTINCT 
            ROW_NUMBER() OVER (PARTITION BY PROTOCOL, SURVEY, "DATE", ENTITY ORDER BY CREATION_TIME) AS rn
        FROM your_table
        HAVING COUNT(*) >= 2
    );

    -- 执行动态拼接的SQL
    EXECUTE IMMEDIATE '
        WITH ranked_records AS (
            SELECT 
                PROTOCOL,
                SURVEY,
                "DATE",
                ENTITY,
                CREATION_TIME,
                ROW_NUMBER() OVER (PARTITION BY PROTOCOL, SURVEY, "DATE", ENTITY ORDER BY CREATION_TIME) AS rn,
                COUNT(*) OVER (PARTITION BY PROTOCOL, SURVEY, "DATE", ENTITY) AS COUNTER
            FROM your_table
        )
        SELECT 
            PROTOCOL,
            SURVEY,
            "DATE",
            ENTITY,
            COUNTER,
            ' || pivot_col_list || '
        FROM ranked_records
        PIVOT (
            MAX(CREATION_TIME) FOR rn IN (' || pivot_val_list || ')
        )
        WHERE COUNTER >= 2
        ORDER BY PROTOCOL, SURVEY, "DATE", ENTITY';
END;
/

关键说明 #

• DATE是Oracle保留关键字，查询时需用双引号包裹，或建议修改表字段名避免冲突。
• ROW_NUMBER()用于给组内记录排序，确保每个CREATION_TIME对应唯一序号，方便PIVOT转换。
• 动态SQL会根据数据中实际的最大重复数，自动生成CREATION_TIME_1、CREATION_TIME_2等对应列。

内容的提问来源于stack exchange，提问作者lucia de finetti