[Tech Request]: Advance aggregate functions, regression and polynomial fitting

[fengttt]

Is there an existing issue for the same feature request?

• [X] I have checked the existing issues.

Is your feature request related to a problem?

No response

Describe the feature you'd like

Linear and higher degree polynomial fitting. PostgresSQL/Snowflake/Oracle have linear regression regr_XXX functions. For example, https://docs.snowflake.com/en/sql-reference/functions/regr_intercept or

It is also useful to fix different functions, like higher degree polynomial, exponential, log, etc etc.

Lets' start from linear

Describe implementation you've considered

No response

Documentation, Adoption, Use Case, Migration Strategy

No response

Additional information

No response

[dengn]

This is a very big feature and requires clarification. Needs specification about what functions are going to be implemented and their syntax.

[aressu1985]

can not understand this requirement

[domingozhang]

y The dependent variable. This must be an expression that can be evaluated to a numeric type.

x The independent variable. This must be an expression that can be evaluated to a numeric type.

• REGR_AVGX: Returns the average of the independent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable. Syntax REGR_AVGX(y, x)

• REGR_AVGY: Returns the average of the dependent variable for non-null pairs in a group, where x is the independent variable and y is the dependent variable. Syntax REGR_AVGY(y, x)

• REGR_COUNT: Returns the number of non-null number pairs in a group. Syntax REGR_COUNT(y, x)

• REGR_INTERCEPT: Returns the intercept of the univariate linear regression line for non-null pairs in a group. Where x is the independent variable and y is the dependent variable. It is computed for non-null pairs using the following formula: AVG(y)-REGR_SLOPE(y,x)*AVG(x) Syntax REGR_INTERCEPT(y, x)

• 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: NULL if VAR_POP(x) = 0, else 1 if VAR_POP(y) = 0 and VAR_POP(x) <> 0, else POWER(CORR(y,x), 2) Where x is the independent variable and y is the dependent variable. Syntax REGR_R2(y, x)

• REGR_SXX: Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs. Syntax REGR_SXX(y, x)

• REGR_SXY: Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs. Syntax REGR_SXY(y, x)

• REGR_SXY: Returns REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2) for non-null pairs. Syntax REGR_SXY(y, x)