andygrove opened a new issue, #179:
URL: https://github.com/apache/datafusion-spark/issues/179
Part of #173 and apache/datafusion-comet#7300.
### What the function does
`kurtosis(expr)` is an aggregate that returns the excess kurtosis
(population form, `n * m4 / m2^2 - 3`)
of the non-NULL values in a group. The argument is implicitly cast to
`DOUBLE` (`ImplicitCastInputTypes`
with `inputTypes = Seq(DoubleType)`), so integral, decimal, float and string
inputs are accepted. The
result is `DOUBLE`, always nullable. Available since Spark 1.6.0. No
aliases. It can also be used as a
window function.
Spark evaluates it as a `DeclarativeAggregate` with a buffer of `n, avg, m2,
m3, m4` (all `DOUBLE`,
initialized to `0.0`), updated per row with an online (Welford-style)
recurrence and combined across
partitions with the pairwise higher-order moment merge formula. The final
value is:
- `n == 0` -> `NULL`
- `m2 == 0` -> `NULL` (or `NaN` when
`spark.sql.legacy.statisticalAggregate=true`)
- otherwise `n * m4 / (m2 * m2) - 3.0`
### Examples
```sql
SELECT kurtosis(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
-- -0.7014368047529627
SELECT kurtosis(col) FROM VALUES (1), (10), (100), (10), (1) AS tab(col);
-- 0.19432323191699075
-- testData(a) = 1, 1, 2, 2, 3, 3, NULL, 3, NULL (group-by.sql.out)
SELECT kurtosis(a) FROM testData;
-- -1.5069204152249134
-- single row, spark.sql.legacy.statisticalAggregate=false (default)
SELECT kurtosis(a) FROM VALUES (1) AS t(a);
-- NULL
-- single row, spark.sql.legacy.statisticalAggregate=true
SELECT kurtosis(a) FROM VALUES (1) AS t(a);
-- NaN
-- string input is cast to DOUBLE (promoteStrings.sql.out, ANSI off)
SELECT kurtosis('1') FROM (SELECT 1);
-- NULL
```
### Edge cases to test
- Empty input and all-NULL groups: `NULL` (`n == 0`), independent of the
legacy flag.
- NULL rows are skipped (the update keeps the buffer unchanged when the
child is NULL).
- Single row and all-equal values: `m2` is exactly `0.0`, so the result is
`NULL` by default and `NaN` with
`spark.sql.legacy.statisticalAggregate=true` (internal conf, default
`false`).
- `NaN` input poisons the buffer and yields `NaN` (`m2 == 0.0` is false for
`NaN`).
- `+/-Infinity` input: the update computes `inf * (inf - inf)`, so `m2`
becomes `NaN` and the result is
`NaN` even for a single row (unlike a single finite row, which gives
`NULL`).
- Very large values whose squares/fourth powers overflow `DOUBLE` (e.g.
`1e100` mixed with small values).
- Input types: `TINYINT`/`SMALLINT`/`INT`/`BIGINT`, `FLOAT`, `DECIMAL(38,
x)` (cast to `DOUBLE` first, so
precision is lost before accumulation), numeric strings. Non-numeric
strings: `NULL` with ANSI off,
`CAST_INVALID_INPUT` with `spark.sql.ansi.enabled=true` (default on in
4.0+).
- `kurtosis(DISTINCT x)`: DISTINCT is applied before the moments are
computed.
- `GROUP BY` with many groups, and use as a window function over different
frames.
- Merge path: results depend on accumulation and merge order. The same
multiset `{1,1,2,2,3,3,3}` is
`-1.5069204152249134` in `group-by.sql.out` and `-1.506920415224914` in
`DataFrameWindowFunctionsSuite`. Test partial/final aggregation over
several partitions, and port both
the update and merge expressions exactly (same operation order) to match
Spark bit-for-bit.
### Differences across Spark versions
None found. `Kurtosis` and `CentralMomentAgg` (update, merge and evaluate
expressions) are identical in
3.4.3, 3.5.8, 4.0.4, 4.1.3 and 4.2.0. ANSI mode being on by default in 4.0
only affects the implicit cast
of string inputs.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/CentralMomentAgg.scala`,
classes `CentralMomentAgg` (shared update/merge, `momentOrder = 4`) and
`Kurtosis`.
- DataFusion core has no `kurtosis` or `skewness`.
`datafusion/functions-aggregate/src/variance.rs` has a
Welford-style variance accumulator, but it only tracks `count, mean, m2`
and its update/merge formulas
are not Spark's, so it is a structural reference only.
- Share one central-moment accumulator with `skewness` (`momentOrder = 3`)
in a new module under
`crates/spark/src/function/aggregate/`. The state (`n` as `DOUBLE`, `avg`,
`m2`, `m3`, `m4`) should be
exposed as the partial-aggregate state so a partial/final split behaves
like Spark's.
- Support `GroupsAccumulator` for performance. Sliding window frames need
either `retract_batch` or
recomputation; whichever is used must reproduce Spark's values for the
frame.
- Tests: add `crates/sqllogictest/test_files/aggregate/kurtosis.slt`, and
document with `#[user_doc]`.
---
_This issue was generated by an LLM (Claude) from the Apache Spark source
code and tests. Examples come from Spark's `ExpressionDescription` examples,
test suites and `.sql.out` golden files, but please verify them against Spark
before relying on them._
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]