REGR_FUNCTIONS

Purpose

With the help of the linear regression functions you can determine a least-square regression line.

Syntax

regr_functions::=

Usage Notes

  • If either expr1 or expr2 is NULL, then the corresponding number pair is not considered for the computation.
  • Exasol interprets expr2 as independent variable (x value) and expr1 as dependent variable (y value).
  • The regression functions are described in the following table:
  • Function Description Formula
    REGR_SLOPE Slope of the regression line
    REGR_INTERCEPT y-intercept of the regression line
    REGR_COUNT Number of non-NULL number pairs  
    REGR_R2 Coefficient of determination (goodness of fit)
    REGR_AVGX Average of the independent values (x values)
    REGR_AVGY Average of the dependent values (y values)
    REGR_SXX Auxiliary function
    REGR_SXY Auxiliary function
    REGR_SYY Auxiliary function

Examples

Analytic Function

SELECT 
    id, department, hire_date, 
    REGR_COUNT(starting_salary, current_salary) 
        OVER (PARTITION BY department ORDER BY hire_date) REGR_COUNT 
FROM employee_table ORDER BY department, hire_date;
Result
ID DEPARTMENT HIRE_DATE REGR_COUNT
2005 ACCOUNTS 2013-01-01 1
2003 ACCOUNTS 2015-07-01 2
2002 ACCOUNTS 2017-01-01 4
2004 ACCOUNTS 2017-01-01 4
2001 ACCOUNTS 2018-07-01 5
1003 HR 2014-01-01 1
1002 HR 2016-01-01 3
1004 HR 2016-01-01 3
1001 HR 2018-07-01 4
SELECT 
    id, department, hire_date, 
    REGR_INTERCEPT(starting_salary, current_salary) 
        OVER (PARTITION BY department ORDER BY hire_date) REGR_INTERCEPT 
FROM employee_table ORDER BY department, hire_date;
Result
ID DEPARTMENT HIRE_DATE REGR_INTERCEPT
2005 ACCOUNTS 2013-01-01 NULL
2003 ACCOUNTS 2015-07-01 NULL
2002 ACCOUNTS 2017-01-01 96666.667
2004 ACCOUNTS 2017-01-01 96666.6674
2001 ACCOUNTS 2018-07-01 46000
1003 HR 2014-01-01 NULL
1002 HR 2016-01-01 57500
1004 HR 2016-01-01 57500
1001 HR 2018-07-01 70757.576
SELECT 
    id, department, hire_date, 
    REGR_SLOPE(starting_salary, current_salary) 
        OVER (PARTITION BY department ORDER BY hire_date) REGR_SLOPE 
FROM employee_table ORDER BY department, hire_date;
Result
ID DEPARTMENT HIRE_DATE REGR_SLOPE
2005 ACCOUNTS 2013-01-01 NULL
2003 ACCOUNTS 2015-07-01 NULL
2002 ACCOUNTS 2017-01-01 -0.66666667
2004 ACCOUNTS 2017-01-01 -0.66666667
2001 ACCOUNTS 2018-07-01 0
1003 HR 2014-01-01 NULL
1002 HR 2016-01-01 -0.25
1004 HR 2016-01-01 -0.25
1001 HR 2018-07-01 -0.41414141