Oracle SQL - Function REGR function

Introduction

REGR linear regression function gives a least square regression line to a set of pairs of numbers.

Given the following table.

Demo

SQL>
SQL> drop table my_table;

Table dropped.-- from w w  w  .  j a va 2 s.com

SQL> create table my_table(
  2  x      NUMBER(4)  primary key,
  3  y      NUMBER(4)  not null);
SQL>
SQL> insert into my_table values(1,2);
SQL> insert into my_table values(2,4);
SQL> insert into my_table values(3,6);
SQL> insert into my_table values(4,9);
SQL> insert into my_table values(5,13);
SQL> insert into my_table values(6,16);
SQL> insert into my_table values(7,20);
SQL> insert into my_table values(8,27);
SQL> insert into my_table values(9,32);
SQL> insert into my_table values(10,47);
SQL> insert into my_table values(11,53);
SQL> insert into my_table values(12,60);
SQL> insert into my_table values(13,76);
SQL> insert into my_table values(14,100);
SQL> insert into my_table values(15,130);
SQL> insert into my_table values(16,189);
SQL> insert into my_table values(17,300);
SQL> insert into my_table values(18,789);
SQL> insert into my_table values(19,1000);
SQL>

The following options are available for the REGR function.

For the estimated slope of the line:

REGR_SLOPE(expr1, expr2) 

Demo

SQL>
SQL>--  w  w  w.ja  va  2s .c o m
SQL> SELECT REGR_SLOPE(y, x) FROM my_table;

REGR_SLOPE(Y,X)
---------------
       00034.34

SQL>

For the y-intercept of the line:

REGR_INTERCEPT(expr1, expr2) 

Demo

SQL>
SQL>-- from   w w  w  .j a v a2  s  .c o m
SQL> SELECT REGR_INTERCEPT(y, x) FROM my_table;

REGR_INTERCEPT(Y,X)
-------------------
          -00192.23

SQL>

For the number of observations:

REGR_COUNT(expr1, expr2) 

Coe:

SELECT REGR_COUNT(y, x) FROM my_table;

For the coefficient of determination (R-square):

REGR_R2(expr1, expr2) 

Demo

SQL>
SQL>-- from ww  w.  ja  v a2s . com
SQL> SELECT REGR_R2(y, x) FROM my_table;

REGR_R2(Y,X)
------------
    00000.50

SQL>

For average value of independent (x) variables:

REGR_AVGX(expr1, expr2) 

Demo

SQL>
SQL> SELECT REGR_AVGX(y, x) FROM my_table;

REGR_AVGX(Y,X)--   www  .  j a  v a 2 s.  com
--------------
      00010.00

SQL>

For average value of dependent (y) variables:

REGR_AVGY(expr1, expr2) 

Demo

SQL>
SQL>-- from  w w  w .j a v  a2  s  . c  o  m
SQL> SELECT REGR_AVGY(y, x) FROM my_table;

REGR_AVGY(Y,X)
--------------
      00151.21

SQL>

For sum of squares x:

REGR_SXX(expr1, expr2) 

Demo

SQL>
SQL> SELECT REGR_SXX(y, x) FROM my_table;

REGR_SXX(Y,X)--  w w w. j a  v  a  2  s  . c  om
-------------
     00570.00

SQL>

For sum of squares y:

REGR_SYY(expr1, expr2) 

Demo

SQL>
SQL>--   w w w . j ava  2 s  .  c  o m
SQL> SELECT REGR_SYY(y, x) FROM my_table;

REGR_SYY(Y,X)
-------------
    #########

SQL>

For sum of cross-product xy:

REGR_SXY(expr1, expr2) 

Demo

SQL>
SQL>--   w  w w  .  j a v  a 2  s .c  o m
SQL> SELECT REGR_SXY(y, x) FROM my_table;

REGR_SXY(Y,X)
-------------
     19576.00

SQL>