Ramener une quantité fixe de données d'une colonne

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
Phil
Administrateur du site
Messages : 291
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Ramener une quantité fixe de données d'une colonne

Message par Phil »

Merci à Bruno pour sa question

"J'obtiens une ORA-1489 sous Oracle 11g en utilisant la fonction listagg pour afficher des commentaires s'il y en a trop. Je n'ai pas besoin de toute la liste, un gros aperçu suffit."

Réponse :

La fonction LISTAGG retourne un varchar, type limité à 4000 octets en SQL avec Oracle Database 11.2. Il est possible avec Oracle Database 12c+ d'ajouter à listagg une clause ON OVERFLOW gérant les dépassements. Mais, s'il ne faut de toute façon qu'un aperçu, il est envisageable de ne pas aller plus loin que les 4000 octets. La solution peut être de faire quelque chose de ce style, cela fonctionnera en 11g comme en 12c :

Code : Tout sélectionner

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

create table t1(id integer primary key, com varchar2(1333 char));

Table created.

insert into t1 select rownum, DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(1,100))) from dual connect by level <= 500000;

500000 rows created.

select listagg(com, '; ') within group (order by id), lengthb(listagg(com, '; ') within group (order by id))
from
(select id, com
from 
(select 
id, 
com, 
2*dense_rank() over (order by id) - 2 + sum(lengthb(com)) over (order by id) sn
from 
(select * from t1 where com is not null 
-- inserer les filtres supplementaires
and rownum <= 1334) v0
) v1
where sn <= 4000) v2;

notes :
- sn représente la taille en octets des commentaires agrégés, lengthb est utilisé plutôt que length, la limitation du type varchar est en octets et pas en caractères et 1 caractère peut prendre plus de 1 octet dans les bases en AL32UTF8
- le rownum <= 1334 est pour éviter de balayer toute la table, une chaîne vide est nulle avec Oracle donc une chaîne non nulle fait forcément au moins 1 caractère. En comptant le séparateur faisant 2 octets, il est inutile de s'intéresser à plus de 1334 lignes.

 
Au passage en 12c+ la limite pourrait être repoussée avec les varchar étendus. Si les 4000 octets suffisent, pas besoin de faire de modification cependant, exemple en 18c :

Code : Tout sélectionner

Live SQL 18.1.2, running Oracle Database 18c Enterprise Edition - 18.1.0.0.0 

create table t1(id integer primary key, com varchar2(1333 char));

Table created.

insert into t1 select rownum, DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(1,100))) from dual connect by level <= 50000;

50000 rows created.

select listagg(com, '; ') within group (order by id), lengthb(listagg(com, '; ') within group (order by id))
from
(select id, com
from 
(select 
id, 
com, 
2*dense_rank() over (order by id) - 2 + sum(lengthb(com)) over (order by id) sn
from 
(
select * from t1 where com is not null 
-- inserer les filtres supplementaires
fetch first 1334 rows only) v0
) v1
where sn <= 4000) v2; 

note : 
- 50000 lignes seulement pour tenir dans l'espace fourni par Oracle sur l'environnement de démonstration livesql.oracle.com
- petite variation par rapport à la version 11g de la requête, la syntaxe standard "fetch first N rows only" peut être utilisée à la place de la syntaxe propriétaire Oracle Database basée sur rownum
Avec PostgreSQL, il n'y a de toute façon pas de limitation de longueur au niveau des varchar mais pour avoir le même résultat :

Code : Tout sélectionner

select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.24 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 ligne)

create table t1(id integer primary key, com varchar);
NOTICE:  CREATE TABLE / PRIMARY KEY créera un index implicite « t1_pkey » pour la table « t1 »
CREATE TABLE

insert into t1 select generate_series(1, 500000), md5(random()::text);
INSERT 0 500000

select string_agg(com, '; ' order by id), length(string_agg(com, '; ' order by id))
from
(select id, com
from 
(select 
id, 
com, 
2*dense_rank() over (order by id) - 2 + sum(length(com)) over (order by id) sn
from 
(select * from t1 
where com is not null and com <> ''
-- inserer les filtres supplementaires
fetch first 1334 rows only) v0
) v1
where sn <= 4000) v2; 

note : 
- string_agg à la place de listagg
- une chaîne vide n'est PAS nulle avec PostgreSQL donc nous écartons ici les chaînes vides en plus des valeurs nulles
Autre écriture possible qui permet potentiellement de s'intéresser uniquement aux lignes nécessaires : une CTE récursive avec une jointure latérale qui est la boucle FOR EACH du SQL.
Je prends une ligne respectant les critères dans l’ordre de mon choix puis je fais une itération sur la ligne suivante dans la liste des lignes respectant les critères jusqu’à dépasser les 4000. C’est assez proche de ce que nous ferions si nous devions le faire à la main en fait.
Attention, il faut Oracle Database 12.1+ ou PostgreSQL 9.3+, ce type de jointure ne fonctionne pas officiellement avec Oracle Database 11.2 donc il faut un peu patienter :

Code : Tout sélectionner

select version();
                                                                  version                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 ligne)

with recursive boucle_com(id, com, longueur) as 
(
		(select id, com, length(com) longueur from t1 order by id asc fetch first 1 row only) 
	union all
		(select t.id, t.com, bc.longueur + 2 + length(t.com) 
		from boucle_com bc 
		join lateral
		(select id, com, length(com) longueur from t1 where t1.id > bc.id order by id asc fetch first 1 row only) t 
		on (true) 
	where bc.longueur + 2 + length(t.com)  <= 4000)
)
select string_agg(com, '; ' order by id), length(string_agg(com, '; ' order by id)) from boucle_com;
Cdlt. Phil - pgphil.ovh
Répondre