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]

Reply via email to