"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
Avec la mémoire de travail par défaut de 4Mb, la différence serait encore plus flagrante.