SET DEFINE OFF

CREATE OR REPLACE FUNCTION stat_summary (
   p_table_name     VARCHAR2 DEFAULT NULL,
   p_column_name    VARCHAR2 DEFAULT NULL,
   p_owner          VARCHAR2 DEFAULT USER,
   p_format         VARCHAR2 DEFAULT 'TEXT')
   RETURN CLOB
   AUTHID CURRENT_USER
/*
     Wrapper pour DBMS_STAT_FUNCS.summary
     R.T. - 2014

    Utilisation des privileges de l'invocateur pour éviter les problèmes de droits
    lors de l'execution du SQL dynamique min/max.
*/
IS
   s               DBMS_STAT_FUNCS.summarytype;
   l_summary       CLOB;
   l_nl            VARCHAR2 (2) := CHR (10);
   l_tmp           VARCHAR2 (8000);
   l_url           VARCHAR2 (4000)
      := '<img align="center" src="http://chart.apis.google.com/chart?chbh=60,0,0&chf=bg,s,65432100&chxt=y&chs=300x400&cht=bvs&chdl=Outliers|Mediane|Q25-75|Q5-95|Min/Max&chco=FF0000,000000,FF9900,0CBF0B,0000FF&';
   l_outliers      VARCHAR2 (4000);
   l_outliers_nb   NUMBER := 0;
   l_iqr           NUMBER;
   l_lbv           NUMBER;
   l_ubv           NUMBER;
   l_owner         VARCHAR2 (30);
BEGIN
   IF NVL (LENGTH (p_owner), 0) = 0
   THEN
      l_owner := USER;
   ELSE
      l_owner := p_owner;
   END IF;

   DBMS_STAT_FUNCS.summary (p_ownername    => l_owner,
                            p_tablename    => p_table_name,
                            p_columnname   => p_column_name,
                            s              => s);

   l_summary :=
         '  '
      || l_nl
      || UPPER (l_owner)
      || '.'
      || UPPER (p_table_name)
      || ' ['
      || UPPER (p_column_name)
      || ']'
      || l_nl
      || '---------------------- '
      || l_nl
      || '  '
      || l_nl
      || 'N: '
      || s.COUNT
      || l_nl
      || 'Min/Max: '
      || s.MIN
      || '/'
      || s.MAX
      || ' - Delta: '
      || s.range
      || l_nl
      || 'Var.: '
      || ROUND (s.VARIANCE, 6)
      || ' - e.t.: '
      || ROUND (s.STDDEV, 6)
      || l_nl
      || '  '
      || l_nl
      || 'Quantiles '
      || l_nl
      || '---------------------- '
      || l_nl
      || '     5%: '
      || s.quantile_5
      || l_nl
      || '    25%: '
      || s.quantile_25
      || l_nl
      || 'Mediane: '
      || s.MEDIAN
      || l_nl
      || '    75%: '
      || s.quantile_75
      || l_nl
      || '    95%: '
      || s.quantile_95
      || l_nl
      || '  '
      || l_nl
      || 'Principale(s) modalite(s): ';

   l_iqr := s.quantile_75 - s.quantile_25;

   IF     (s.MIN >= s.quantile_25 - 1.5 * l_iqr)
      AND (s.MAX <= s.quantile_75 + 1.5 * l_iqr)
   THEN
      l_lbv := s.MIN;
      l_ubv := s.MAX;
   ELSE
      EXECUTE IMMEDIATE
            'SELECT MIN ('
         || p_column_name
         || '), MAX ('
         || p_column_name
         || ')
  FROM '
         || l_owner
         || '.'
         || p_table_name
         || '
 WHERE '
         || p_column_name
         || ' BETWEEN :Q25 - 1.5 * :IQR AND :Q75 + 1.5 * :IQR'
         INTO l_lbv, l_ubv
         USING s.quantile_25,
               l_iqr,
               s.quantile_75,
               l_iqr;

      IF (s.MIN >= s.quantile_25 - 1.5 * l_iqr)
      THEN
         l_lbv := s.MIN;
      END IF;

      IF (s.MAX <= s.quantile_75 + 1.5 * l_iqr)
      THEN
         l_ubv := s.MAX;
      END IF;
   END IF;

   IF (s.cmode.LAST < 15)
   THEN
      FOR i IN s.cmode.FIRST .. s.cmode.LAST
      LOOP
         l_tmp := l_tmp || l_nl || ('   [' || i || ']: ' || s.cmode (i));
      END LOOP;
   ELSE
      l_tmp :=
            l_tmp
         || l_nl
         || ' /!\ Non-affiché - Trop de valeurs ('
         || s.cmode.LAST
         || ')';
   END IF;

   l_summary :=
         l_summary
      || l_tmp
      || l_nl
      || '  '
      || l_nl
      || 'Cinq valeurs les plus grandes [Outliers (*) >'
      || ROUND (s.quantile_75 + l_iqr * 1.5, 6)
      || ']:';

   l_tmp := '';

   FOR i IN s.top_5_values.FIRST .. s.top_5_values.LAST
   LOOP
      l_tmp := l_tmp || l_nl || '   [' || i || ']: ' || s.top_5_values (i);

      IF (s.top_5_values (i) >= s.quantile_75 + l_iqr * 1.5)
      THEN
         l_outliers_nb := l_outliers_nb + 1;
         l_outliers := l_outliers || '|-1,' || s.top_5_values (i) || ',-1';
         l_tmp := l_tmp || ' (*)';
      END IF;
   END LOOP;

   l_summary :=
         l_summary
      || l_tmp
      || l_nl
      || '  '
      || l_nl
      || 'Cinq valeurs les plus petites [Outliers (*) <'
      || ROUND (s.quantile_25 - l_iqr * 1.5, 6)
      || ']:';
   l_tmp := '';

   FOR i IN s.bottom_5_values.FIRST .. s.bottom_5_values.LAST
   LOOP
      l_tmp :=
            l_tmp
         || l_nl
         || '   ['
         || (s.bottom_5_values.LAST + 1 - i)
         || ']: '
         || s.bottom_5_values (i);

      IF (s.bottom_5_values (i) <= s.quantile_25 - l_iqr * 1.5)
      THEN
         l_outliers_nb := l_outliers_nb + 1;
         l_outliers := l_outliers || '|-1,' || s.bottom_5_values (i) || ',-1';
         l_tmp := l_tmp || ' (*)';
      END IF;
   END LOOP;

   IF p_format = 'HTML'
   THEN
      l_url :=
            l_url
         || 'chxr=0,'
         || s.MIN * 0.9
         || ','
         || s.MAX * 1.1
         || '&chds='
         || s.MIN * 0.9
         || ','
         || s.MAX * 1.1;
      l_url :=
            l_url
         || '&chd=t0:-1,'
         || l_lbv
         || ',-1|-1,'
         || s.quantile_25
         || ',-1|-1,'
         || s.quantile_75
         || ',-1|-1,'
         || l_ubv
         || ',-1|-1,'
         || s.MEDIAN
         || ',-1|-1,'
         || s.quantile_5
         || ',-1|-1,'
         || s.quantile_95
         || ',-1'
         || l_outliers;
      l_url :=
            l_url
         || '&chm=F,FF9900,0,1:4,80|H,0000FF,0,1:4,1:60|H,000000,4,1:4,1:80|H,0000FF,3,1:4,1:60|H,0CBF0B,5,1:4,1:60|H,0CBF0B,6,1:4,1:60';


      FOR i IN 1 .. l_outliers_nb
      LOOP
         l_url := l_url || '|o,FF0000,' || (6 + i) || ',-1,7';
      END LOOP;

      l_summary :=
            '<table<tr><td><pre>'
         || l_summary
         || l_tmp
         || l_nl
         || '</pre><br></td><td width="15px"></td><td>'
         || l_url
         || '"/></td></tr></table>';
   ELSE
      l_summary := l_summary || l_tmp || l_nl;
   END IF;

   RETURN l_summary;
END;
/