Skip to content

Implement Spark-compatible skewness #180

Open

Description

@andygrove

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

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.

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