Oracle PIVOT下实现同组重复值计数后拼接的查询需求

现有查询基础逻辑 #

业务表核心字段包含ID、LOTYP、PROID、LOCKR，存储不同ID下各业务类型、产品类别的锁状态明细记录。
原查询将LOTYP与PROID的拼接组合值作为透视列维度，按ID分组聚合展示对应LOCKR的拼接结果，通过LISTAGG将同组LOCKR值用逗号拼接完成行转列，具体语句如下：

SELECT *
  FROM
  (
    SELECT id,lotyp||proid As title,
           LISTAGG(lockr,',') WITHIN GROUP (ORDER BY lotyp||proid) AS value
      FROM t
     GROUP BY id,lotyp||proid )
 PIVOT (
         MAX(value) FOR title IN ('0201' AS "0201",
                                  '0202' AS "0202",
                                  '0601' AS "0601",
                                  '0602' AS "0602"))   
 ORDER BY id;

现存问题 #

原查询输出会逐一枚举同组内所有重复的LOCKR值：例如某ID的0201组合下存在4个重复的R值，输出结果为R, R, R, R, Y，内容冗余度高。

优化规则 #

• 同组内重复的LOCKR值提前统计出现次数
• 单值出现次数大于1时，将次数标注在LOCKR值前
• 仅出现1次的LOCKR值保持原有展示形式
  优化后上述示例场景的列展示效果为4R, Y。

优化后可执行SQL #

SELECT *
  FROM
  (
    SELECT 
      id,
      title,
      LISTAGG(
        CASE WHEN cnt > 1 THEN cnt || lockr ELSE lockr END,
        ','
      ) WITHIN GROUP (ORDER BY lockr) AS value
    FROM
      (
        -- 内层预统计：同id、同title维度下各锁状态的出现次数
        SELECT
          id,
          lotyp || proid AS title,
          lockr,
          COUNT(*) AS cnt
        FROM t
        GROUP BY id, lotyp || proid, lockr
      ) t_stat
    GROUP BY id, title
  )
 PIVOT (
         MAX(value) FOR title IN ('0201' AS "0201",
                                  '0202' AS "0202",
                                  '0601' AS "0601",
                                  '0602' AS "0602"))   
 ORDER BY id;

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