CREATE OR REPLACE TYPE avg_samp_confintimpl
   AS OBJECT
(
   n NUMBER,
   mean NUMBER,
   m2 NUMBER,
   diff NUMBER,
   cnfinf NUMBER,
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT avg_samp_confintimpl)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateiterate (self    IN OUT avg_samp_confintimpl,
                                         VALUE   IN     NUMBER)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateterminate (
      self          IN     avg_samp_confintimpl,
      returnvalue      OUT NUMBER,
      flags         IN     NUMBER)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregatemerge (self   IN OUT avg_samp_confintimpl,
                                       ctx2   IN     avg_samp_confintimpl)
      RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY avg_samp_confintimpl
IS
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT avg_samp_confintimpl)
      RETURN NUMBER
   IS
   BEGIN
      sctx :=
         avg_samp_confintimpl (0,
                               0,
                               0,
                               0,
                               0);
      RETURN odciconst.success;
   END;

   MEMBER FUNCTION odciaggregateiterate (self    IN OUT avg_samp_confintimpl,
                                         VALUE   IN     NUMBER)
      RETURN NUMBER
   IS
   BEGIN
      n := n + 1;
      diff := VALUE - mean;
      mean := mean + diff / n;
      M2 := M2 + diff * (VALUE - mean);

      RETURN odciconst.success;
   END;

   MEMBER FUNCTION odciaggregateterminate (
      self          IN     avg_samp_confintimpl,
      returnvalue      OUT NUMBER,
      flags         IN     NUMBER)
      RETURN NUMBER
   IS
      TYPE student_quant_97_5 IS TABLE OF NUMBER
         INDEX BY BINARY_INTEGER;

      student_lookup   student_quant_97_5;
      student_coef     NUMBER;
   BEGIN
      student_lookup (1) := 12.706;
      student_lookup (2) := 4.303;
      student_lookup (3) := 3.182;
      student_lookup (4) := 2.776;
      student_lookup (5) := 2.571;
      student_lookup (6) := 2.447;
      student_lookup (7) := 2.365;
      student_lookup (8) := 2.306;
      student_lookup (9) := 2.262;
      student_lookup (10) := 2.228;
      student_lookup (11) := 2.201;
      student_lookup (12) := 2.179;
      student_lookup (13) := 2.16;
      student_lookup (14) := 2.145;
      student_lookup (15) := 2.131;
      student_lookup (16) := 2.12;
      student_lookup (17) := 2.11;
      student_lookup (18) := 2.101;
      student_lookup (19) := 2.093;
      student_lookup (20) := 2.086;
      student_lookup (21) := 2.08;
      student_lookup (22) := 2.074;
      student_lookup (23) := 2.069;
      student_lookup (24) := 2.064;
      student_lookup (25) := 2.06;
      student_lookup (26) := 2.056;
      student_lookup (27) := 2.052;
      student_lookup (28) := 2.048;
      student_lookup (29) := 2.045;
      student_lookup (30) := 2.042;
      student_lookup (31) := 2.04;
      student_lookup (32) := 2.037;
      student_lookup (33) := 2.035;
      student_lookup (34) := 2.032;
      student_lookup (35) := 2.03;
      student_lookup (36) := 2.028;
      student_lookup (37) := 2.026;
      student_lookup (38) := 2.024;
      student_lookup (39) := 2.023;
      student_lookup (40) := 2.021;
      student_lookup (50) := 2.009;
      student_lookup (60) := 2;
      student_lookup (70) := 1.994;
      student_lookup (80) := 1.99;
      student_lookup (90) := 1.987;
      student_lookup (100) := 1.984;
      student_lookup (110) := 1.982;
      student_lookup (120) := 1.98;
      student_lookup (130) := 1.978;
      student_lookup (140) := 1.977;

      IF n < 2
      THEN
         returnvalue := 0;
      ELSE
         IF n <= 41
         THEN
            student_coef := student_lookup (n - 1);
         ELSIF n <= 150
         THEN
            student_coef := student_lookup (ROUND ( (n - 1) / 10) * 10);
         ELSE
            -- convergence vers la loi normale
            student_coef := 1.96;
         END IF;

         returnvalue := student_coef * (SQRT (M2 / (n - 1)) / SQRT (n));
      END IF;

      RETURN odciconst.success;
   END;

   MEMBER FUNCTION odciaggregatemerge (self   IN OUT avg_samp_confintimpl,
                                       ctx2   IN     avg_samp_confintimpl)
      RETURN NUMBER
   IS
   BEGIN
      --- A voir si une parallelisation est possible ?
      RETURN odciconst.success;
   END;
END;
/

CREATE OR REPLACE FUNCTION avg_samp_confint (input NUMBER)
   RETURN NUMBER
   AGGREGATE USING avg_samp_confintimpl
   /* Fonction de calcul de l'intervalle de confiance à 95% d'une moyenne
   R.T. - 2013
   Modif. 09-2013: Amélioration de l'algorithme de calcul de la variance:
   https://en.wikipedia.org/wiki/Algorithms_for_calculating_variance
   */
;
/