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 : 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
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 : 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
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.