如何高效查找Oracle SQL表中列的不一致值（7000行30列）

问题场景 #

现有Oracle表数据如下，其中product_id为101的记录在attr1列有不一致值，product_id为103的记录在attr3列有不一致值：

| PRODUCT_ID | ATTR1 | ATTR2 | ATTR3 |
|------------|-------|-------|-------|
| 101        | a     | x     | z     |
| 101        | a     | x     | z     |
| 101        | aa    | x     | z     |
| 102        | b     | y     | z     |
| 102        | b     | y     | z     |
| 103        | c     | z     | z     |
| 103        | c     | z     | zz    |

现在要在7000行、30列的表中，快速查出每个product_id下存在不一致值的列，输出格式要求如下：

| PRODUCT_ID | DESCR            |
|------------|------------------|
| 101        | Issue with attr1 |
| 103        | Issue with attr3 |

原示例用多次UNION加分组的方式实现，但30列的场景下要写30段重复代码，还会多次扫描表，效率太低。下面给两个更高效的方案：

方案1：一次分组+条件筛选（适合列数固定的场景） #

先通过一次分组计算出每个product_id下各列的不同值数量，再筛选出有问题的列。全程只扫描一次表，性能比原示例好很多：

WITH data AS (
   SELECT 101 product_id, 'a' attr1, 'x' attr2, 'z' attr3 FROM dual
   UNION ALL
   SELECT 101 product_id, 'a' attr1, 'x' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 101 product_id, 'aa' attr1, 'x' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 102 product_id, 'b' attr1, 'y' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 102 product_id, 'b' attr1, 'y' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 103 product_id, 'c' attr1, 'z' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 103 product_id, 'c' attr1, 'z' attr2, 'zz' attr3  FROM dual
),
product_attr_stats AS (
    SELECT product_id,
           COUNT(DISTINCT attr1) cnt_attr1,
           COUNT(DISTINCT attr2) cnt_attr2,
           COUNT(DISTINCT attr3) cnt_attr3
           -- 按这个格式把剩下27列的COUNT(DISTINCT)都加上
    FROM data
    GROUP BY product_id
)
SELECT product_id, 'Issue with attr1' descr
FROM product_attr_stats
WHERE cnt_attr1 > 1
UNION ALL
SELECT product_id, 'Issue with attr2' descr
FROM product_attr_stats
WHERE cnt_attr2 > 1
UNION ALL
SELECT product_id, 'Issue with attr3' descr
FROM product_attr_stats
WHERE cnt_attr3 > 1;

方案2：用UNPIVOT转置列（代码更简洁，多列场景首选） #

利用Oracle的UNPIVOT把列转成行，再分组判断，代码更简洁，添加新列只需要在转置列表里追加，不用重复写SELECT：

WITH data AS (
   SELECT 101 product_id, 'a' attr1, 'x' attr2, 'z' attr3 FROM dual
   UNION ALL
   SELECT 101 product_id, 'a' attr1, 'x' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 101 product_id, 'aa' attr1, 'x' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 102 product_id, 'b' attr1, 'y' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 102 product_id, 'b' attr1, 'y' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 103 product_id, 'c' attr1, 'z' attr2, 'z' attr3  FROM dual
   UNION ALL
   SELECT 103 product_id, 'c' attr1, 'z' attr2, 'zz' attr3  FROM dual
),
unpivoted_data AS (
    SELECT product_id, attr_name, attr_value
    FROM data
    UNPIVOT (
        attr_value FOR attr_name IN (
            attr1 AS 'attr1',
            attr2 AS 'attr2',
            attr3 AS 'attr3'
            -- 剩下27列按这个格式加进去：列名 AS '列名'
        )
    )
)
SELECT product_id, 'Issue with ' || attr_name descr
FROM unpivoted_data
GROUP BY product_id, attr_name
HAVING COUNT(DISTINCT attr_value) > 1;

额外优化提示 #

• 如果product_id有索引，分组操作会更快；
• 要是列允许空值，COUNT(DISTINCT)会忽略NULL，如果需要把NULL也算作不一致值，可以改成COUNT(DISTINCT NVL(列名, 'NULL_MARKER'))；
• 7000行数据量很小，两种方案都能秒出结果，但30列的场景下方案2的代码维护起来更省心。

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