Oracle SQL按3000字符上限拼接文本的正确分组方法咨询

将test_txt表中的多行text字段值按id降序拼接，要求单组拼接后总长度小于3000，若加入下一条文本后总长度超过3000，则将下一条文本归入新分组。

create table test_Txt(
 id   number(1),
 text varchar(2000)
);
 
insert into test_txt values(1,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');
 
insert into test_txt values(2,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');
 
insert into test_txt values(3,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');
  
insert into test_txt values(4,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');

insert into test_txt values(5,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');

insert into test_txt values(6,'BRUG HIDDEN 4. The student is obliged to justify the absence from educational classes within the specified period:
1) parents or legal guardians excuse the student''s absence by the end of the second week of the following month / for the previous month / in justified cases (e.g. a visit to a doctor), the parent or legal guardian may exempt some classes in person or in writing;
2) in the event of a student''s resignation from participation in non-compulsory classes, written information from the parent or legal guardian is required;
3) expected absence of a student for more than one week (e.g. stay in a sanatorium, hospital, chronic disease), parents or legal guardians are obliged to notify the class teacher within 3 days;
4) if the absence is not excused within the prescribed period, the class teacher explains the reasons for the absence with the parents or legal guardians;
5) information about frequent absenteeism of students is passed to the school pedagogue, parents are called
6)xfasdfkjasdgkdsethkgjaskgjbsajgbkjsdguihifafa');

初始查询SQL #

SELECT t.*,FLOOR((sum_text)/3000)
        FROM   (SELECT id,
                       TO_CHAR(text) AS text,
                       LENGTH(text) AS len_Text,
                       SUM(LENGTH(text)) OVER(ORDER BY id DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_text
                FROM   test_txt) t;

运行结果 #

ID  LEN_TEXT    SUM_TEXT    FLOOR((SUM_TEXT)/3000)
6   1039    1039    0
5   1039    2078    0
4   1039    3117    1
3   1039    4156    1
2   1039    5195    1
1   1039    6234    2

问题原因 #

直接用累计总和除以3000取整的逻辑错误，没有遵循「加入当前条后总长度超过3000就立即切分新分组」的规则，初始逻辑中分组1的总长度为3117，已经超过3000阈值，id=2本应归入分组2，却被错误划分到分组1。

方案1：通用递归CTE实现（适配所有支持递归语法的数据库） #

WITH ordered_text AS (
    -- 按id降序排序生成行号，保证遍历顺序
    SELECT 
        id,
        text,
        LENGTH(text) AS len_text,
        ROW_NUMBER() OVER(ORDER BY id DESC) AS rn
    FROM test_txt
),
recur_group AS (
    -- 递归起点：第一条数据归为分组0
    SELECT 
        rn,
        id,
        text,
        len_text,
        0 AS group_id,
        len_text AS group_total_len
    FROM ordered_text
    WHERE rn = 1
    UNION ALL
    -- 递归遍历后续行判断分组
    SELECT 
        ot.rn,
        ot.id,
        ot.text,
        ot.len_text,
        CASE WHEN rg.group_total_len + ot.len_text < 3000 THEN rg.group_id ELSE rg.group_id + 1 END,
        CASE WHEN rg.group_total_len + ot.len_text < 3000 THEN rg.group_total_len + ot.len_text ELSE ot.len_text END
    FROM ordered_text ot
    JOIN recur_group rg ON ot.rn = rg.rn + 1
)
SELECT id, text, len_text, group_id
FROM recur_group
ORDER BY rn;

预期运行结果 #

每个分组总长度均为2078，符合小于3000的要求。

方案2：Oracle MATCH_RECOGNIZE实现（12c及以上版本适用，性能更优） #

SELECT id, text, LENGTH(text) len_text, group_id
FROM test_txt
MATCH_RECOGNIZE (
    ORDER BY id DESC
    MEASURES MATCH_NUMBER() -1 AS group_id
    ALL ROWS PER MATCH
    PATTERN (a+)
    DEFINE a AS SUM(LENGTH(text)) < 3000
)
ORDER BY id DESC;

分组后拼接文本 #

如果需要直接得到拼接后的结果，可基于上述分组结果用LISTAGG聚合：

WITH ordered_text AS (
    SELECT 
        id,
        text,
        LENGTH(text) AS len_text,
        ROW_NUMBER() OVER(ORDER BY id DESC) AS rn
    FROM test_txt
),
recur_group AS (
    SELECT 
        rn,
        id,
        text,
        len_text,
        0 AS group_id,
        len_text AS group_total_len
    FROM ordered_text
    WHERE rn = 1
    UNION ALL
    SELECT 
        ot.rn,
        ot.id,
        ot.text,
        ot.len_text,
        CASE WHEN rg.group_total_len + ot.len_text < 3000 THEN rg.group_id ELSE rg.group_id + 1 END,
        CASE WHEN rg.group_total_len + ot.len_text < 3000 THEN rg.group_total_len + ot.len_text ELSE ot.len_text END
    FROM ordered_text ot
    JOIN recur_group rg ON ot.rn = rg.rn + 1
)
SELECT 
    group_id,
    LISTAGG(text, ' ') WITHIN GROUP (ORDER BY id DESC) AS concat_text,
    SUM(len_text) AS total_len
FROM recur_group
GROUP BY group_id
ORDER BY group_id;

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