I have a case:
some script to reproduce:
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');
And Select:
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;
What i want to achieve is concatenate texts until sum of them is less than 3000. If larger, then another text.
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
And here goes problem: texts with group 1 when You sum them is greater than 3000. Text with id = 2 supposed to have group 2 but is 1.
Anyone has an idea? Thanks
Advertisement
Answer
From Oracle 12, you can use MATCH_RECOGNIZE:
SELECT id,
LENGTH( text ) AS text_len,
match_num,
SUM( LENGTH( text ) ) OVER ( PARTITION BY match_num ) AS total_len
FROM test_txt
MATCH_RECOGNIZE(
ORDER BY id
MEASURES
MATCH_NUMBER() - 1 AS match_num
ALL ROWS PER MATCH
PATTERN ( a+ )
DEFINE
A AS SUM( LENGTH( text )) <= 3000
)
Which, for your sample data, outputs:
ID TEXT_LEN MATCH_NUM TOTAL_LEN 1 1039 0 2078 2 1039 0 2078 3 1039 1 2078 4 1039 1 2078 5 1039 2 2078 6 1039 2 2078
db<>fiddle here