- Categories:
Aggregate functions (Linear Regression) ,Window functions
REGR_AVGY¶
Returns the average of the dependent variable for non-null pairs in a group, wherex is the independent variable andy is the dependent variable.
Syntax¶
Aggregate function
REGR_AVGY(y, x)
Window function
REGR_AVGY(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.
Returns¶
If any of the input expressions is of type DECFLOAT, the returned type is DECFLOAT. Otherwise, thereturned type is FLOAT.
Usage notes¶
DISTINCT is not supported for this function.
In order for a row to be included in the average, BOTH the x and y valuesmust be non-NULL.
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_avgy(v,v2)fromaggrgroupbyk;---+------------------+k | regr_avgy(v, v2) |---+------------------+1 | [NULL] |2 | 20 |---+------------------+