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 ww .  j a  va2  s  . c o m

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  w w  w .  j  a  v a 2  s .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>--  ww w  .j  a  v  a2s.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>--  w  ww.j  a va2s.  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)-- w w w . ja  va2  s. c o  m
--------------
      00010.00

SQL>

For average value of dependent (y) variables:

REGR_AVGY(expr1, expr2)

Demo

SQL>
SQL>-- www . j  a  v a  2 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)-- from   www. ja  v  a2 s  .  c o m
-------------
     00570.00

SQL>

For sum of squares y:

REGR_SYY(expr1, expr2)

Demo

SQL>
SQL>-- from   w  ww.  ja  va  2 s .  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 av a 2  s . co m
SQL> SELECT REGR_SXY(y, x) FROM my_table;

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

SQL>