Problème avec les fonctions statistiques : Variance, Stddev, etc
Bonjour,
Je rencontre un problème avec l'utilisation des fonctions statistiques d'Oracle. Je ne trouve pas les même résultats lorsque je fais les calcules de mon coté.
Je vais prendre l'exemple 2 de ce site : http://www.statcan.gc.ca/edu/power-p...214891-fra.htm
Si je reproduit cet exemple, je trouve :
écart type oracle : 2.2894.... au lieu de 2.25....
la variance aussi est différente.
voici ma table et ses valeurs :
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
28
29
30
31
32
| CREATE TABLE farmers (workers number, freq number, xf number, diff number, diff2 number, diff2f number);
INSERT INTO farmers (workers) Values (0);
INSERT INTO farmers (workers) Values (1);
INSERT INTO farmers (workers) Values (2);
INSERT INTO farmers (workers) Values (2);
INSERT INTO farmers (workers) Values (3);
INSERT INTO farmers (workers) Values (3);
INSERT INTO farmers (workers) Values (3);
INSERT INTO farmers (workers) Values (4);
INSERT INTO farmers (workers) Values (4);
INSERT INTO farmers (workers) Values (4);
INSERT INTO farmers (workers) Values (4);
INSERT INTO farmers (workers) Values (4);
INSERT INTO farmers (workers) Values (4);
INSERT INTO farmers (workers) Values (5);
INSERT INTO farmers (workers) Values (5);
INSERT INTO farmers (workers) Values (5);
INSERT INTO farmers (workers) Values (5);
INSERT INTO farmers (workers) Values (5);
INSERT INTO farmers (workers) Values (6);
INSERT INTO farmers (workers) Values (6);
INSERT INTO farmers (workers) Values (6);
INSERT INTO farmers (workers) Values (6);
INSERT INTO farmers (workers) Values (7);
INSERT INTO farmers (workers) Values (7);
INSERT INTO farmers (workers) Values (7);
INSERT INTO farmers (workers) Values (8);
INSERT INTO farmers (workers) Values (8);
INSERT INTO farmers (workers) Values (8);
INSERT INTO farmers (workers) Values (9);
INSERT INTO farmers (workers) Values (9); |
Mon programme :
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
| DECLARE
CURSOR worker IS
SELECT distinct workers from farmers;
exist NUMBER;
counter NUMBER;
average NUMBER;
varaianc NUMBER;
varaianco NUMBER;
stddevo NUMBER;
BEGIN
FOR i IN worker LOOP
SELECT count(freq) INTO exist FROM farmers WHERE workers = i.workers;
-- Avoid to calculate the frequence
IF exist = 0 THEN
SELECT count(workers)
INTO counter
FROM farmers
WHERE workers = i.workers;
UPDATE farmers
SET FREQ = counter,
XF =
(workers * counter)
WHERE workers = i.workers;
END IF;
END LOOP;
SELECT sum(sum(distinct xf)) / sum(sum(distinct freq))
INTO average
FROM farmers
GROUP BY workers;
FOR i IN worker LOOP
SELECT count(diff) INTO exist FROM farmers WHERE workers = i.workers;
-- Avoid to calculate diff, ...
IF exist = 0 THEN
UPDATE farmers
SET DIFF = workers - average,
DIFF2 =
(workers - average) * (workers - average),
DIFF2F =
(workers - average) * (workers - average) * freq
WHERE workers = i.workers;
END IF;
END LOOP;
SELECT sum(sum(distinct diff2f))/sum(sum(distinct freq))
INTO varaianc
FROM farmers
GROUP BY workers;
SELECT variance(workers) INTO varaianco FROM farmers;
SELECT stddev(workers) INTO stddevo FROM farmers;
dbms_output.put_line('variance : ' || varaianc);
dbms_output.put_line('oracle variance : ' || varaianco);
dbms_output.put_line('standard dev : ' || sqrt(varaianc));
dbms_output.put_line('oracle standard dev : ' || sqrt(varaianco));
END; |
Je ne trouve aucune documentation sur comment Oracle fait calcule.
Donc si quelqu'un voit où se trouve le problème...
Merci d'avance.
