Evgeny Stanilovsky created IGNITE-22252:
-------------------------------------------
Summary: Sql. Implement aggregate functions according to standard
Feature T621
Key: IGNITE-22252
URL: https://issues.apache.org/jira/browse/IGNITE-22252
Project: Ignite
Issue Type: Improvement
Components: sql
Affects Versions: 3.0.0-beta1
Reporter: Evgeny Stanilovsky
Standard defines Feature T621 as support for STDDEV_POP, STDDEV_SAMP, VAR_POP,
VAR_SAMP. Seems all we need to implement such functionality:
# Register appropriate in IgniteSqlOperatorTable
# Append IgniteSqlValidator#validateAggregateFunction
# Define implementation here Accumulators#accumulatorFunctionFactory
# And the main (because calcite derive incorrect return type by default)
override IgniteTypeSystem.deriveAvgAggType
According to standard :
STDDEV_POP(X) is equivalent to SQRT(VAR_POP(X)).
STDDEV_SAMP(X) is equivalent to SQRT(VAR_SAMP(X)).
Thus derived type need to be double or BigDecimal if we need to use
java.math.BigDecimal.sqrt
useful implementation info can be found here
AggregateReduceFunctionsRule#reduceAgg
{noformat}
STDDEV_POP:
SQRT((SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / COUNT(x))
STDDEV_SAMP:
SQRT((SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / CASE COUNT(x) WHEN 1
THEN NULL ELSE COUNT(x) - 1 END)
VAR_POP:
(SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / COUNT(x)
case VAR_SAMP:
(SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / CASE COUNT(x) WHEN 1 THEN
NULL ELSE COUNT(x) - 1 END
{noformat}
Also need to mention that results for appropriate muted tests (for example
test_stddev.test_ignore) are not correct (sqrt return is trimmed) and pg
returns a bit different results that corresponds to standard, need to fix
return results too.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
