南大通用GBase 8a 语法改造oracle12c字符串聚合函数LISTAGG WITHIN GROUP方法说明

问题描述

在oracle迁移至8a数据库时，遇到以下SQL语法，不能直接迁移：

SELECT T.DATA_DATE,
       T.CONT_NO,
       SUBSTR(LISTAGG(TO_CHAR(T.NVOICE_NO), ';' ON OVERFLOW TRUNCATE '...' WITH COUNT) WITHIN GROUP(ORDER BY T.NVOICE_NO),1,200) AS NVOICE_NO,
	   T.INVOICE_TYPE,
       SUM(T.NVOICE_AMT)
    FROM ODS_IQP_NVOICE T
	WHERE T.DATA_DATE = CURDAY
		GROUP BY T.DATA_DATE, T.CONT_NO, T.INVOICE_TYPE;

 改造方式

1. 开始尝试这种方式改造：
SELECT 
    T.DATA_DATE,
    T.CONT_NO,
    CASE 
        WHEN CHAR_LENGTH(T.NVOICE_NO_LIST) <= 200 THEN
            T.NVOICE_NO_LIST
        ELSE
            CONCAT(
                SUBSTRING(T.NVOICE_NO_LIST, 1, 196),
                '...(',
                CAST((T.TOTAL_COUNT - ( 
                    LENGTH(SUBSTRING(T.NVOICE_NO_LIST, 1, 196)) - 
                    LENGTH(REPLACE(SUBSTRING(T.NVOICE_NO_LIST, 1, 196), ';', '')) 
                ) - 1) AS CHAR),  
                ')'
            )
    END AS NVOICE_NO,
    T.INVOICE_TYPE,
    T.NVOICE_AMT_SUM
FROM (
    SELECT 
        DATA_DATE,
        CONT_NO,
        INVOICE_TYPE,
        GROUP_CONCAT(NVOICE_NO ORDER BY NVOICE_NO SEPARATOR ';') AS NVOICE_NO_LIST,
        SUM(NVOICE_AMT) AS NVOICE_AMT_SUM,
        COUNT(*) AS TOTAL_COUNT
    FROM ODS_IQP_NVOICE
    WHERE DATA_DATE = CURDATE()
    GROUP BY DATA_DATE, CONT_NO, INVOICE_TYPE
) T;
但该方式容易存在GROUP_CONCAT聚合后内存越界的问题。

2. 后尝试通过以下方式改造：

SELECT T.DATA_DATE,
       T.CONT_NO, 
       GROUP_CONCAT(T.NVOICE_NO ORDER BY T.NVOICE_NO topN 200 SEPARATOR ';'  ) AS NVOICE_NO,
       T.INVOICE_TYPE, 
       SUM(T.NVOICE_AMT) AS NVOICE_AMT_SUM
  FROM ODS_IQP_NVOICE T 
 WHERE T.DATA_DATE = CURDATE()    
 GROUP BY T.DATA_DATE, T.CONT_NO, T.INVOICE_TYPE;
 
 即，取分组内前200的方式

 原理说明

（1）sql要实现什么功能

• TO_CHAR(T.NVOICE_NO)

       将发票号字段NVOICE_NO转换为字符类型（避免拼接时类型异常）

• LISTAGG(..., ';') WITHIN GROUP(ORDER BY T.NVOICE_NO)

       按T.NVOICE_NO排序后，将同组的发票号用;拼接。核心是 “分组内字符串拼接 ”  

• ON OVERFLOW TRUNCATE '...' WITH COUNT

       LISTAGG 的溢出处理参数：

      ON OVERFLOW TRUNCATE：当拼接结果超长时截断

      '...'：截断后追加的标识字符

      WITH COUNT：在截断后补充 “(剩余 N 个)” 的计数提示SUBSTR(...,1,200)最终将拼接结果截取前 200 个字符（双重保险，防止溢出）

（2）改造关键逻辑说明

• 核心函数替换：

  • Oracle LISTAGG(字段, ';') WITHIN GROUP(ORDER BY 字段) → GBase8a语法GROUP_CONCAT(字段 ORDER BY 字段 SEPARATOR ';')

  • Oracle CURDAY → GBase8a CURDATE()（注意：Oracle 的CURDAY如果是自定义变量 / 函数，需对应调整）

• 溢出截断模拟：

  • 用CHAR_LENGTH判断拼接后总长度是否超过 200（CHAR_LENGTH计算字符数，LENGTH计算字节数，根据编码选择）

  • 若超长：截取前 196 个字符，拼接...(剩余数量)，保证最终长度≤200

  • 剩余数量计算逻辑：总条数 - 截断后包含的条数（通过 “总长度 - 去分隔符后长度” 得到分隔符数量，分隔符数量 + 1 = 已包含条数）