andygrove opened a new issue, #180:
URL: https://github.com/apache/datafusion-spark/issues/180
Part of #173 and apache/datafusion-comet#7300.
### What the function does
`skewness(expr)` is an aggregate that returns the population skewness
(`sqrt(n) * m3 / m2^1.5`) 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` (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 `sqrt(n) * m3 / sqrt(m2 * m2 * m2)` (note: `sqrt(m2^3)`, not
`pow(m2, 1.5)`)
### Examples
```sql
SELECT skewness(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
-- 1.1135657469022011
SELECT skewness(col) FROM VALUES (-1000), (-100), (10), (20) AS tab(col);
-- -1.1135657469022011
-- testData(a) = 1, 1, 2, 2, 3, 3, NULL, 3, NULL (group-by.sql.out)
SELECT skewness(a) FROM testData;
-- -0.2723801058145729
-- single row, spark.sql.legacy.statisticalAggregate=false (default)
SELECT skewness(a) FROM VALUES (1) AS t(a);
-- NULL
-- single row, spark.sql.legacy.statisticalAggregate=true
SELECT skewness(a) FROM VALUES (1) AS t(a);
-- NaN
-- string input is cast to DOUBLE (promoteStrings.sql.out, ANSI off)
SELECT skewness('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`).
- Symmetric input (e.g. `1, 2, 3`) should give `0.0` or a value extremely
close to it; check the exact
bits Spark's formula produces rather than assuming `0.0`.
- `NaN` input poisons the buffer and yields `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 cubes overflow `DOUBLE`.
- Input types: `TINYINT`/`SMALLINT`/`INT`/`BIGINT`, `FLOAT`, `DECIMAL(38,
x)` (cast to `DOUBLE` 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+).
- `skewness(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
`-0.2723801058145729` in `group-by.sql.out` and `-0.27238010581457267` 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. `Skewness` 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 = 3`) and
`Skewness`.
- DataFusion core has no `skewness` or `kurtosis`.
`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 `kurtosis` (`momentOrder = 4`)
in a new module under
`crates/spark/src/function/aggregate/`. With `momentOrder = 3` Spark's
`m3` update/merge do not depend
on `m4`, so a shared accumulator can carry `m4` without changing
`skewness` results, but the partial
state should still match Spark's (`n, avg, m2, m3`).
- 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/skewness.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]