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

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

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