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.--   w  w w.j  a va  2 s .c  om

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>-- from   ww  w .j a v a 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>-- w  ww. j ava2s .  com
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 w w w .j  a v  a 2s.  c  o m
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)-- from  ww  w . j a va  2  s.co m
--------------
      00010.00

SQL>

For average value of dependent (y) variables:

REGR_AVGY(expr1, expr2) 

Demo

SQL>
SQL>-- w  w w  . j  av  a2  s . co  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)-- from  w  w w.j  a  va  2 s .  c  om
-------------
     00570.00

SQL>

For sum of squares y:

REGR_SYY(expr1, expr2) 

Demo

SQL>
SQL>-- from ww  w. j  a  v a2s.  c om
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>-- from w w w.  j a v a2  s.  c  o  m
SQL> SELECT REGR_SXY(y, x) FROM my_table;

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

SQL>