Discussion :

[Jeff.p18]

Hello tous,

J'ai 3 moyennes à faire : la classique, la médiane et la modale (valeur la plus observée)

-la classique : easy
-la médiane : easy aussi avec une fonction analytique (voir en bas)
- mais alors la modale ... !??
je galère !
c'est quoi le plus simple ?
vu que c'est pour un datawarehouse, le faire avec une fonction analytique à la volée (comme la médiane) serait le mieux !

ma table de test :

CREATE TABLE zTestMoyenne
(
GRP VARCHAR2(4 BYTE),
VAL NUMBER(2)
)

jeu d'essai :
A 1
A 2
B 3
C 4
D 3
D 5
D 5
D 6

solution pour la médiane :

select
GRP,
percentile_cont(0.5) within group (order by VAl) mediane
from
zTestMoyenne
group by
GRP

D'avance merci !
+

[McM]

Pour le jeu d'essai, le plus simple est de donner un

Code :

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

1
2
WITH zTestMoyenne
 AS (SELECT ... UNION ALL...)

[McM]

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
WITH zTestMoyenne AS (SELECT 'A' grp, 1 val FROM DUAL 
UNION ALL SELECT 'A' grp, 2 val FROM DUAL 
UNION ALL SELECT 'B' grp, 3 val FROM DUAL 
UNION ALL SELECT 'C' grp, 4 val FROM DUAL 
UNION ALL SELECT 'D' grp, 3 val FROM DUAL 
UNION ALL SELECT 'D' grp, 5 val FROM DUAL 
UNION ALL SELECT 'D' grp, 5 val FROM DUAL 
UNION ALL SELECT 'D' grp, 6 val FROM DUAL
UNION ALL SELECT 'D' grp, 6 val FROM DUAL
UNION ALL SELECT 'D' grp, 6 val FROM DUAL
 )
SELECT grp, ROUND(AVG(val),2) AS moyenne, percentile_cont(0.5) WITHIN GROUP (ORDER BY val) mediane,
	MAX(modale)
FROM (
	SELECT grp, val, 
		first_value(val) OVER (PARTITION BY grp ORDER BY nbval DESC) AS modale
	FROM (
		SELECT GRP, val, 
			COUNT(*) OVER (PARTITION BY grp, val) AS nbval
		FROM zTestMoyenne
		)
	)
GROUP BY GRP

Code :

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

1
2
3
4
5
GRP	MOYENNE	MEDIANE	MAX(MODALE)
A	1,5	1	1
B	3	3	3
C	4	4	4
D	5,17	5	6

[Jeff.p18]

Merci McM
Je regarde si mon requêteur (bien connu, c'est en 2 mots, ça commence par un B et fini par un S ;-) sait faire ça à la volée sur mon modèle DWH en constellation (pas gagné). si c'est pas le cas faudra faire ça à l'alim (un ETL aussi bien connu) ...
+

[laurentschneider]

c'est pas STATS_MODE que tu cherches?

[laurentschneider]

n'est-il aussi pas plus simple d'utiliser median pour avoir la mediane?

Code :

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

1
2
3
4
5
 
SELECT  grp,   avg(val), 
  median(val),  stats_mode(val) 
FROM t
group by grp;

Edit: STATS_MODE ne supporte pas de clause analytique..

[McM]

C'est du quoi Stats_mode ? 10g ?

[laurentschneider]

-la médiane : easy aussi avec une fonction analytique (voir en bas)

Code :

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

percentile_cont(0.5) within group (order by VAl)

Si tu me permets ce commentaire, PERCENTILE_CONT n'est pas une fonction analytique dans ton exemple. C'est une fonction d'aggrégation (d'où le GROUP BY).

Une fonction analytique utilise une clause analytique OVER().

ex:

Code :

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

1
2
3
4
5
SELECT grp, val ,
  PERCENTILE_CONT(0.5) WITHIN GROUP (order by VAL) 
  over (partition by grp)
FROM t

[laurentschneider]

C'est du quoi Stats_mode ? 10g ?

oui, en tout cas en 10.1 ça marche, en 9.2 je ne pense pas

[Jeff.p18]

halluciant Stats_mode !!!
vive la 10g
Oracle ça devient vraiment un SGBDR de fainéants ^^
merci @ tous
+

[McM]

En 9.2 ça marche pas. Ni stats_mode, ni median