如何高效检测Oracle表多列离散值并统计其数量？

现有一张Oracle SQL表，其中product_id为101和103的记录分别在attr1和attr3列存在离散值：

原始数据表（data） #

期望输出结果 #

需要生成统计结果，列出各列的离散值及其出现数量：

现有单列查询语句 #

目前仅实现了针对单列的查询逻辑，但实际场景需要检测20+列，重复编写代码工作量极大：

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, 'zzz'   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
   
), d1 AS (

   SELECT product_id, 'Issue with attr1' descr 
   FROM data
   GROUP BY product_id 
   HAVING COUNT(DISTINCT attr1) > 1

), d2 AS (
      
   SELECT DISTINCT d1.product_id, d1.descr, data.attr1, COUNT(attr1) OVER (PARTITION BY attr1) cnt
   FROM d1
   INNER JOIN data
      ON d1.product_id = data.product_id
   
)

SELECT product_id, descr, LISTAGG(attr1 || '(' || cnt || ')', ', ') WITHIN GROUP (ORDER BY product_id) value_count
FROM d2
GROUP BY product_id, descr
;

高效实现方案 #

利用Oracle的UNPIVOT操作将多列转置为行，一次性处理所有需要检测的列，避免重复代码：

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, 'zzz' 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
),
-- 转置列：将所有需要检测的attr列转为行数据
unpivoted_data AS (
   SELECT product_id, attr_name, attr_value
   FROM data
   UNPIVOT (
      attr_value FOR attr_name IN (attr1, attr2, attr3) -- 这里列出所有需要检测的列，20+列只需在这里添加
   )
),
-- 统计每个product_id+attr_name组合的离散值及数量
value_counts AS (
   SELECT 
      product_id,
      'Issue with ' || attr_name AS descr,
      attr_value || '(' || COUNT(*) || ')' AS value_count_item
   FROM unpivoted_data
   GROUP BY product_id, attr_name, attr_value
),
-- 筛选出存在离散值的组合（不同值数量>1）
discrete_groups AS (
   SELECT product_id, attr_name
   FROM unpivoted_data
   GROUP BY product_id, attr_name
   HAVING COUNT(DISTINCT attr_value) > 1
)
-- 聚合离散值统计结果
SELECT 
   vc.product_id,
   vc.descr,
   LISTAGG(vc.value_count_item, ', ') WITHIN GROUP (ORDER BY vc.attr_value) AS value_count
FROM value_counts vc
JOIN discrete_groups dg 
   ON vc.product_id = dg.product_id AND vc.attr_name = dg.attr_name
GROUP BY vc.product_id, vc.descr
ORDER BY vc.product_id, vc.descr;

方案优势 #

• 只需在UNPIVOT子句中列出所有需要检测的列，无需为每列编写重复的统计逻辑
• 逻辑统一，后续新增检测列仅需修改UNPIVOT中的列列表，维护成本极低
• 一次性处理所有列，执行效率优于多段重复代码

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