- Categories:
Aggregate functions (Linear Regression) ,Window function syntax and usage
REGR_R2¶
Returns the coefficient of determination for non-null pairs in a group. It is computed for non-null pairs using the following formula:
NULLifVAR_POP(x)=0,else1ifVAR_POP(y)=0andVAR_POP(x)<>0,elsePOWER(CORR(y,x),2)
Wherex is the independent variable andy is the dependent variable.
Syntax¶
Aggregate function
REGR_R2(y, x)
Window function
REGR_R2(y, x)OVER([PARTITIONBY<expr3>])
Arguments¶
yThe dependent variable. This must be an expression that can be evaluated to a numeric type.
xThe independent variable. This must be an expression that can be evaluated to a numeric type.
expr3This is the optional expression used to group rows into partitions.
Important
Note the order of the arguments; the dependent variable is first.
Usage notes¶
DISTINCT is not supported for this function.
When this function is called as a window function, it does not support:
An ORDER BY clause within the OVER clause.
Explicit window frames.
Examples¶
CREATEORREPLACETABLEaggr(kINT,vDECIMAL(10,2),v2DECIMAL(10,2));INSERTINTOaggrVALUES(1,10,null);INSERTINTOaggrVALUES(2,10,11),(2,20,22),(2,25,null),(2,30,35);
SELECTk,REGR_R2(v,v2)FROMaggrGROUPBYk;
+---+----------------+| k | regr_r2(v, v2) ||---+----------------+| 1 | [NULL] || 2 | 0.9976905312 |+---+----------------+