Oracle PL/SQL/Analytical Functions/REGR SYY

Материал из SQL эксперт
Перейти к: навигация, поиск

REGR_AVGY(y, x) Returns the average of y after eliminating x and y pairs where either x or y is null

   <source lang="sql">

SQL> CREATE TABLE all_sales (

 2    year INTEGER,
 3    month INTEGER,
 4    prd_type_id INTEGER,
 5    emp_id INTEGER ,
 6    amount NUMBER(8, 2)
 7  );

Table created. SQL> SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,1    ,1          ,21    ,16034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,2    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,3    ,2          ,21    ,20167.83);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,4    ,2          ,21    ,25056.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,5    ,2          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,6    ,1          ,21    ,15564.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,7    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,8    ,1          ,21    ,16434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,9    ,1          ,21    ,19654.57);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,10   ,1          ,21    ,21764.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,12   ,2          ,21    ,10034.64);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,22    ,16634.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,21    ,26034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,2    ,1          ,21    ,12644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,3    ,1          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,4    ,1          ,21    ,25026.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,5    ,1          ,21    ,17212.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,6    ,1          ,21    ,15564.26);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,7    ,2          ,21    ,62654.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,8    ,2          ,21    ,26434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,9    ,2          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,10   ,2          ,21    ,21264.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,12   ,1          ,21    ,10032.64);

1 row created. SQL> SQL> select * from all_sales;

     YEAR      MONTH PRD_TYPE_ID     EMP_ID     AMOUNT

---------- ----------- ---------- ----------
     2006          1           1         21   16034.84
     2006          2           1         21   15644.65
     2006          3           2         21   20167.83
     2006          4           2         21   25056.45
     2006          5           2         21
     2006          6           1         21   15564.66
     2006          7           1         21   15644.65
     2006          8           1         21   16434.82
     2006          9           1         21   19654.57
     2006         10           1         21   21764.19
     2006         11           1         21   13026.73
     2006         12           2         21   10034.64
     2005          1           2         22   16634.84
     2005          1           2         21   26034.84
     2005          2           1         21   12644.65
     2005          3           1         21
     2005          4           1         21   25026.45
     2005          5           1         21   17212.66
     2005          6           1         21   15564.26
     2005          7           2         21   62654.82
     2005          8           2         21   26434.82
     2005          9           2         21   15644.65
     2005         10           2         21   21264.19
     2005         11           1         21   13026.73
     2005         12           1         21   10032.64

25 rows selected. SQL> SQL> SQL> --REGR_AVGY(y, x) Returns the average of y after eliminating x and y pairs where either x or y is null. SQL> -- y is interpreted by the functions as a variable that depends on x. SQL> SELECT

 2   prd_type_id,
 3   REGR_AVGY(amount, month) AS avgy
 4  FROM all_sales
 5  GROUP BY prd_type_id;

PRD_TYPE_ID AVGY


----------
         1 16234.0357
         2 24880.7867

SQL> SQL> SQL> drop table all_sales; Table dropped. SQL>

      </source>
   
  


REGR_SYY(expr1, expr2): For sum of squares

   <source lang="sql">

SQL> SQL> SQL> create table TestTable (

 2    x    number primary key,
 3    y    number
 4  );

Table created. SQL> insert into TestTable values (1, 7 ); 1 row created. SQL> insert into TestTable values (2, 1 ); 1 row created. SQL> insert into TestTable values (3, 2 ); 1 row created. SQL> insert into TestTable values (4, 5 ); 1 row created. SQL> insert into TestTable values (5, 7 ); 1 row created. SQL> insert into TestTable values (6, 34 ); 1 row created. SQL> insert into TestTable values (7, 32 ); 1 row created. SQL> insert into TestTable values (8, 43 ); 1 row created. SQL> insert into TestTable values (9, 87 ); 1 row created. SQL> SQL> select * from TestTable;

        X          Y

----------
        1          7
        2          1
        3          2
        4          5
        5          7
        6         34
        7         32
        8         43
        9         87

9 rows selected. SQL> SQL> --REGR_SYY(expr1, expr2): For sum of squares SQL> SQL> --REGR_SYY(expr1, expr2) SQL> SQL> SELECT REGR_SYY(y, x) FROM TestTable; REGR_SYY(Y,X)


  6445.55556

SQL> SQL> drop table TestTable; Table dropped. SQL> SQL> SQL> SQL>

      </source>
   
  


REGR_SYY(y, x) Returns REG_COUNT(y, x) * VAR_POP(y)

   <source lang="sql">

SQL> CREATE TABLE all_sales (

 2    year INTEGER,
 3    month INTEGER,
 4    prd_type_id INTEGER,
 5    emp_id INTEGER ,
 6    amount NUMBER(8, 2)
 7  );

Table created. SQL> SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,1    ,1          ,21    ,16034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,2    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,3    ,2          ,21    ,20167.83);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,4    ,2          ,21    ,25056.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,5    ,2          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,6    ,1          ,21    ,15564.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,7    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,8    ,1          ,21    ,16434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,9    ,1          ,21    ,19654.57);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,10   ,1          ,21    ,21764.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,12   ,2          ,21    ,10034.64);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,22    ,16634.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,21    ,26034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,2    ,1          ,21    ,12644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,3    ,1          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,4    ,1          ,21    ,25026.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,5    ,1          ,21    ,17212.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,6    ,1          ,21    ,15564.26);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,7    ,2          ,21    ,62654.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,8    ,2          ,21    ,26434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,9    ,2          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,10   ,2          ,21    ,21264.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,12   ,1          ,21    ,10032.64);

1 row created. SQL> SQL> select * from all_sales;

     YEAR      MONTH PRD_TYPE_ID     EMP_ID     AMOUNT

---------- ----------- ---------- ----------
     2006          1           1         21   16034.84
     2006          2           1         21   15644.65
     2006          3           2         21   20167.83
     2006          4           2         21   25056.45
     2006          5           2         21
     2006          6           1         21   15564.66
     2006          7           1         21   15644.65
     2006          8           1         21   16434.82
     2006          9           1         21   19654.57
     2006         10           1         21   21764.19
     2006         11           1         21   13026.73
     2006         12           2         21   10034.64
     2005          1           2         22   16634.84
     2005          1           2         21   26034.84
     2005          2           1         21   12644.65
     2005          3           1         21
     2005          4           1         21   25026.45
     2005          5           1         21   17212.66
     2005          6           1         21   15564.26
     2005          7           2         21   62654.82
     2005          8           2         21   26434.82
     2005          9           2         21   15644.65
     2005         10           2         21   21264.19
     2005         11           1         21   13026.73
     2005         12           1         21   10032.64

25 rows selected. SQL> SQL> SQL> --REGR_SYY(y, x) Returns REG_COUNT(y, x) * VAR_POP(y). SQL> -- y is interpreted by the functions as a variable that depends on x. SQL> SELECT

 2   prd_type_id,
 3   REGR_SYY(amount, month) AS syy
 4  FROM all_sales
 5  GROUP BY prd_type_id;

PRD_TYPE_ID SYY


----------
         1  194132942
         2 1839656970

SQL> SQL> SQL> SQL> drop table all_sales; Table dropped. SQL>

      </source>