Cumul + rollup avec PostgreSQL

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 : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Cumul + rollup avec PostgreSQL

Message par Phil » jeu. 25 avr. 2019 17:28

Merci à Guillaume pour sa question :

"Pour faire une somme de valeurs par code, un cumul ET avoir la somme générale dans une ligne supplémentaire, j'utilise cette syntaxe avec Oracle Database :

with table1 as (
select 'A' code, 10 valeur from dual
union all
select 'B' code, 20 valeur from dual
union all
select 'C' code, -10 valeur from dual
),
table2 as (
select
code,
valeur,
sum(valeur) over (order by code) cumul
from table1)
SELECT CODE,
GROUPING(CODE) GROUPING_CODE,
SUM(valeur) valeur,
MAX(cumul) KEEP ( DENSE_RANK LAST ORDER BY code ) CUMUL
FROM table2
GROUP BY rollup ( CODE);

Avec PostgreSQL, je ne peux pas faire directement l'agrégation et le calcul analytique dans ce cas car il n'y pas de FIRST/LAST + KEEP.
Comment faire ? J'ai pensé à faire ça :

with table1 as (
select 'A' code, 10 valeur from dual
union all
select 'B' code, 20 valeur from dual
union all
select 'C' code, -10 valeur from dual
),
table2 as (
select
code,
valeur,
sum(valeur) over (order by code) cumul
from table1)
SELECT CODE,
GROUPING(CODE) GROUPING_CODE,
SUM(valeur) valeur,
case when GROUPING(CODE) = 1 then sum(valeur) else MAX(cumul) end CUMUL
FROM table2
GROUP BY rollup ( CODE);

mais c'est pas joli...

"


Réponse :

Ce n'est pas joli mais ce serait OK. Cependant, attention aux performances. Il faut évaluer s'il peut être intéressant de faire d'abord l'agrégation puis la partie analytique pour traiter le cumul. Avec 3 lignes pas de différence mais, avec 1 million, la différence peut devenir nette :

Code : Tout sélectionner

select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 (Debian 11.2-2.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-2) 8.3.0, 64-bit
(1 ligne)

create table ventes(code int, valeur int);

insert into ventes
select 
random()*10,
random()*100
from generate_series(1, 1000000); 

with travail as (
select 
  code, 
  valeur, 
  sum(valeur) over (order by code) cumul 
from ventes)
SELECT CODE,
  GROUPING(CODE) GROUPING_CODE,
  SUM(valeur) valeur,
  case when GROUPING(CODE) = 1 then sum(valeur) else MAX(cumul) end CUMUL
FROM travail
GROUP BY rollup ( CODE)
order by code;
...
================> 2s

with travail as (select code, grouping(code) groupage, sum(valeur) somme_par_code from ventes group by rollup(code))
select 
code,  
somme_par_code valeur , 
case when groupage = 0 then sum(somme_par_code) over (order by code nulls last) else somme_par_code end cumul
from travail
;
...
================> 0s4

Le serveur utilisé est très ancien, le sous-système de stockage et les CPU sont lents mais j'utilisais une mémoire de travail (work_mem) de 128Mb.
Avec la mémoire de travail par défaut de 4Mb, la différence serait encore plus flagrante.
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Grouper avant de fenêtrer

Message par Phil » sam. 27 avr. 2019 20:45

Pour approfondir le sujet, une page présentant l'intérêt de grouper ce qui peut l'être afin d'utiliser les fonctions analytiques sur un volume de données plus réduit : https://pgphil.ovh/group_by_window_11_01.php
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Re: Grouper avant de fenêtrer

Message par Phil » mar. 30 avr. 2019 00:13

Merci à Julien pour sa question :

"Quelle syntaxe pour ajouter à chaque ligne de résultat la perf médiane du géant et de son clan, la perf moyenne du géant et de son clan ?"

Réponse :

Plusieurs solutions possibles, par exemple :

Code : Tout sélectionner

select version();
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 (Ubuntu 11.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit

with travail as(
select
l.idge,
grouping(l.idge) groupage,
g.idcl,
max(perf) pma,
percentile_cont(0.5) within group (order by perf) pme,
avg(perf) pmo,
count(1) nbl,
dense_rank() over (partition by g.idcl order by l.idge) rgc
from
geants g join lancers l on (g.idge = l.idge)
where dtl >= date '2019-01-01'
group by grouping sets ((l.idge, g.idcl), (g.idcl))
)
select
t1.idge,
t1.idcl,
t1.pme pmeg,
t2.pme pmec,
t1.pmo pmog,
t2.pmo pmoc,
t1.pma pmag,
t2.pma pmac,
t1.nbl nblg,
t2.nbl nblc,
max(t1.rgc) over  (partition by t1.idcl) nbgc
from
(select * from travail where idge is not null) t1
join
(select idcl, pme, pmo, pma, nbl from travail where groupage = 1) t2 on (t1.idcl = t2.idcl)
order by t1.idge;

- pmag est la perf médiane du géant, pmac la perf médiane de son clan
- pmog est la perf moyenne du géant, pmoc la perf moyenne de son clan
- la durée d'exécution de cette requête est autour de 200ms avec l'environnement de test de la page https://pgphil.ovh/group_by_window_11_01.php
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Re: Grouper avant de fenêtrer

Message par Phil » mar. 30 avr. 2019 14:24

Merci à Guillaume pour sa question :


"J'ai lu la page https://pgphil.ovh/group_by_window_11_01.php , d'accord sur le principe mais avec Oracle Database j'obtenais la médiane avec du code du style

percentile_cont(0.5) within group (order by X) over (partition by Y)

Que faut-il faire pour avoir en plus la perf moyenne générale, la perf médiane générale, la perf max générale ?
Au niveau présentation, pas besoin d'avoir des colonnes supplémentaires pour chaque niveau d'agrégation. C'est même plus facile pour moi avec des lignes supplémentaires."


Réponse :

Avec Oracle Database 11.2 ou 19c, percentile_cont est une fonction d'agrégation pouvant être utilisée en combinaison avec GROUP BY mais aussi une fonction analytique pouvant être utilisée avec une fenêtre via OVER (...)
Avec PostgreSQL 9.4 à 11, percentile_cont est une fonction d'agrégation pouvant être utilisée en combinaison avec GROUP BY mais PAS une fonction analytique pouvant être utilisée avec une fenêtre via OVER (...)
Ce n'est pas une limitation très importante, d'autant qu'agréger ce qui peut l'être avant de fenêtrer est souvent plus performant, avec PostgreSQL comme avec Oracle Database.

Pour obtenir les perfs moyennes, médianes et maximales de l'ensemble des géants, il suffit d'ajouter () à la clause grouping sets. Cela donne par exemple :

Code : Tout sélectionner

select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 (Debian 11.2-2.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-2) 8.3.0, 64-bit

with travail as(
select
grouping(l.idge, g.idcl) groupage,
l.idge,
g.idcl,
max(perf) pma,
percentile_cont(0.5) within group (order by perf) pme,
avg(perf) pmo,
count(1) nbl,
dense_rank() over (partition by g.idcl order by l.idge) rgc
from
geants g join lancers l on (g.idge = l.idge)
where dtl >= date '2019-01-01'
group by grouping sets ((l.idge, g.idcl), (g.idcl), ())
)
select
groupage,
idge,
idcl,
pme,
pmo,
pma,
nbl,
max(rgc) over  (partition by idcl) nbgc
from
travail
order by idge nulls last, idcl nulls last;
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 217
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Re: Grouper avant de fenêtrer

Message par Phil » mar. 30 avr. 2019 16:12

Merci à Guillaume pour sa question :

"C'est valable avec 1000 géants et 10 clans mais est-ce que ce serait encore pertinent avec plus de clans et plus de géants ? Enfin en gros si l'agrégation effectuée avec la with query travail renvoyait beaucoup de lignes ?"

Réponse :

Il faut bien sûr tester dans ton contexte mais, sur mon environnement, j'observe encore un gain non négligeable avec 100 000 géants appartenant à 1000 clans par exemple.
Cdlt. Phil - pgphil.ovh

Répondre