Fonctions analytiques et GROUP BY

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:

---

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:

---

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:

---

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)

---

Citation:

---

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:

---

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 )

---

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:

---

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:

---

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:

---

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

---

Ou si ça vous aide à mieux comprendre

Code:

---

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

OK, je comprends le truc.

Merci beaucoup à vous deux !