REGR_FUNCTIONS

Purpose

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

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).

Regression functions

REGR_SLOPE

The slope of the regression line

REGR_INTERCEPT

The y-intercept of the regression line

REGR_COUNT

The number of non-NULL number pairs

REGR_R2

The coefficient of determination (goodness of fit)

REGR_AVGX

The average of the independent values (x values)

REGR_AVGY

The average of the dependent values (y values)

REGR_SXX

Auxiliary function

REGR_SXY

Auxiliary function

REGR_SYY

Auxiliary function

Examples

Query using REGR_COUNT

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

Query using REGR_INTERCEPT

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

Query using REGR_SLOPE

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