Discussion :

[Antoun]

Hello,

Je ne comprends pas bien comment se comportent les fonctions analytiques utilisant OVER par rapport au GROUP BY... en fait je n'arrive pas du tout à les utiliser dans ce contexte.

Si par exemple, je reprends la table COTATION de l'article de Lalystar :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
-- historique des cotations
create table COTATION (
   isin    varchar2(20),
   dte     date,
   ouv     number,
   max     number,
   min     number,
   clo     number,
   volume  number,
   constraint pk_cotation primary key (isin, dte)
);

Lorsque j'interroge la table au niveau individuel, avec par exemple une somme annuelle des cotations de clôture, tout fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
select isin, 
  dte, 
  extract(year from dte) as annee, 
  clo, 
  sum(clo) over (partition by extract(year from dte)) as total_annuel
from cotation

(oui, je sais, c'est idiot d'additionner des cotations... imaginons juste que ce soit n'importe quoi d'autre, par exemple des litres de vodka bus par jour )

Mais si maintenant je veux le total annuel de chaque société en regard du total annuel toutes sociétés confondues, j'obtiens une erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
select isin, 
  extract(year from dte) as annee, 
  sum(clo) as total_societe, 
  sum(clo) over (partition by extract(year from dte)) as total_toutes_soc
from cotation
group by isin, extract(year from dte)

ORA-00979: N'est pas une expression GROUP BY

La partie surlignée par Toad est sum(clo) over...


Que faut-il faire pour faire marcher cette requête ?


PS : je reprends ici le script de Lalystar pour alimenter la table COTATION.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- table de nomenclature des sociétés
create table ACTION (
   isin     varchar2(20),
   libelle  varchar2(100),
   constraint pk_action primary key (isin)
 
insert into ACTION
select   'FR' || rownum, 'SOCIETE ' || rownum
from     ALL_OBJECTS
where    rownum < 50
 
insert into COTATION
select
   isin,
   dte,
   round(100*dbms_random.value, 2),
   round(100*dbms_random.value, 2),
   round(100*dbms_random.value, 2),
   round(100*dbms_random.value, 2),
   round(100000*dbms_random.value)
from
   ACTION,
   (
      select   sysdate-rownum dte
      from     ALL_OBJECTS
      where    rownum < 200
   )

[Waldar]

Hello Antoun,


Ce qu'il faut se dire et absolument garder en mémoire quand on utilise des fonctions analytiques c'est qu'elles s'appliquent après le group by (et avant le distinct, mais ce n'est pas ce qui nous intéresse ici).

Votre requête ici est parfaitement valide :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
  SELECT isin, 
         extract(year FROM dte) AS annee, 
         sum(clo) AS total_societe
    FROM cotation
GROUP BY isin, extract(year FROM dte)
ORDER BY isin asc, annee asc;

Maintenant, vous voulez refaire un calcul total supplémentaire dans une nouvelle colonne.

Vous écrivez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

sum(clo) over (partition BY extract(year FROM dte))

Mais à ce moment, clo n'existe plus dans le jeu de données.

Il ne reste que ces trois données à dispositions :

1. isin
2. extract(year FROM dte)
3. sum(clo)

C'est bien sum(clo) que vous cherchez à additionner.
Dès lors, il faut utiliser la syntaxe suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
  SELECT isin, 
         extract(year FROM dte) AS annee, 
         sum(clo) AS total_societe,
         sum(sum(clo)) over (partition BY extract(year FROM dte)) AS total_toutes_soc
    FROM cotation
GROUP BY isin, extract(year FROM dte)
ORDER BY isin asc, annee asc

[mnitu]

Ou si ça vous aide à mieux comprendre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
Select t.*,
       sum(total_societe) over(partition BY annee)
from (       
      SELECT isin, 
        extract(year FROM dte) AS annee, 
        sum(clo) AS total_societe 
      FROM cotation
      GROUP BY isin, extract(year FROM dte) 
) t
order by isin, annee

Agrégation seule => Group By
Détails plus agrégation => Fonction analytique

[Antoun]

OK, je comprends le truc.

Merci beaucoup à vous deux !