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]

Reply via email to