Skip to content

Implement Spark-compatible kurtosis #179

Open

Description

@andygrove

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

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.

Activity

  1. andygrove commented on Nov 19, 2026

    @andygrove
    MemberAuthor

    I prototyped this in Comet (apache/datafusion-comet#4818) and am closing that PR in favour of doing it here, so that kurtosis and skewness can share one implementation and Comet can use it. The prototype branch is feat/kurtosis-native-support on andygrove/datafusion-comet (head 4d4e573afe). The code is in native/spark-expr/src/agg_funcs/kurtosis.rs and welford.rs. Review found several things that are easy to get wrong, so I'm recording them here.

    Layout that worked

    • The state is [n, avg, m2, m3, m4], all Float64, which is Spark's CentralMomentAgg buffer. Keeping that layout and exposing it as the partial state is what would let a Spark partial and a native final exchange bytes. The update and merge kernels are direct ports of Spark's updateExpressionsDef and mergeExpressions, in the same operation order, so they match bit-for-bit.
    • The recurrences are order-4 helpers (moments4_update, moments4_merge) beside the existing variance and covariance helpers. The order-4 recurrence subsumes the order-2 one, so skewness (momentOrder = 3) is only a different evaluate over the same state. That is why Implement Spark-compatible skewness #180 should share the accumulator rather than copy it.

    Semantics that review caught

    • The divisor is m2 * m2, not m2. Spark guards on m2 == 0, but evaluateExpression divides by m2 * m2. For inputs 1e-100 and 2e-100, m2 is non-zero but its square underflows to exactly 0.0. Spark's Divide then applies its own zero-divisor rule: NULL with ANSI off, DIVIDE_BY_ZERO with ANSI on. A plain IEEE divide returns NaN. This is a different switch from spark.sql.legacy.statisticalAggregate, which governs only the m2 == 0 branch.
    • The ANSI mode is the one captured when the aggregate was built, not the session's current setting. The Divide inside Kurtosis.evaluateExpression captures its eval mode at analysis time. A temporary view analyzed with ANSI off and queried with it on returns NULL. The reverse raises DIVIDE_BY_ZERO. Reading the current session setting gets both wrong. In Comet the serde reads the captured mode, so the function needs an input for the eval mode and cannot read a session flag.
    • An empty partial must be merged, not skipped. Skipping n2 == 0 in merge_batch gives a different answer when other partials overflow to NaN. With values near 5e77 in one file and an all-NULL second file, Spark returns NaN and a version that skips the empty partial returns -1.22. The merge has to run Spark's mergeExpressions for every partial, including empty ones.
    • The ANSI error should carry the kurtosis(...) query context, as Spark's does.
    • A NaN input yields NaN. A single finite row or all-equal values yield NULL, or NaN under the legacy flag. A single infinite row yields NaN, because inf - inf poisons m2.

    Tests worth porting

    • Both of Spark's own ExpressionDescription examples (-0.7014368047529627 and 0.19432323191699075).
    • Merge across partitions, compared against Spark rather than a tolerance. A merge-order change in moments4_merge is only caught by a bit-exact comparison over several files. The 1e-9 tolerance in the Rust merge test would not catch one.
    • The two-file empty-partial case above.
    • The saved-view case in both directions: analyzed with ANSI off and queried with it on, then the reverse.
    • Use plain queries in the SQL fixtures, not answer-only checks, so a fixture fails if the function silently falls back to Spark.

    Performance gap

    • The prototype had no GroupsAccumulator, so grouped kurtosis ran through DataFusion's generic GroupsAccumulatorAdapter, which costs one boxed Accumulator and a ScalarValue round trip per group per batch. VarianceGroupsAccumulator in Comet keeps flat Vec<f64> state and is the model. The vectorized version should be written once for kurtosis and skewness against the shared state. I did not benchmark either.

    Open question

    • Window use still fell back in Comet. If this lands here, the sliding-frame question in the implementation notes (retract_batch or recomputation) decides whether windows can run natively.

    Thank you to @mbutrovich, @rich7420, @sunchao and @comphead for the reviews on the Comet PR. Most of the list above is theirs.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions