Repository navigation
Description
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->NULLm2 == 0->NULL(orNaNwhenspark.sql.legacy.statisticalAggregate=true)- otherwise
sqrt(n) * m3 / sqrt(m2 * m2 * m2)(note:sqrt(m2^3), notpow(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);
-- NULLEdge 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:
m2is exactly0.0, so the result isNULLby default andNaNwith
spark.sql.legacy.statisticalAggregate=true(internal conf, defaultfalse). - Symmetric input (e.g.
1, 2, 3) should give0.0or a value extremely close to it; check the exact
bits Spark's formula produces rather than assuming0.0. NaNinput poisons the buffer and yieldsNaN.+/-Infinityinput: the update computesinf * (inf - inf), som2becomesNaNand the result is
NaNeven for a single row (unlike a single finite row, which givesNULL).- Very large values whose cubes overflow
DOUBLE. - Input types:
TINYINT/SMALLINT/INT/BIGINT,FLOAT,DECIMAL(38, x)(cast toDOUBLEbefore
accumulation), numeric strings. Non-numeric strings:NULLwith ANSI off,CAST_INVALID_INPUTwith
spark.sql.ansi.enabled=true(default on in 4.0+). skewness(DISTINCT x): DISTINCT is applied before the moments are computed.GROUP BYwith 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.2723801058145729ingroup-by.sql.outand-0.27238010581457267in
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,
classesCentralMomentAgg(shared update/merge,momentOrder = 3) andSkewness. - DataFusion core has no
skewnessorkurtosis.datafusion/functions-aggregate/src/variance.rshas a
Welford-style variance accumulator, but it only trackscount, mean, m2and 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/. WithmomentOrder = 3Spark'sm3update/merge do not depend
onm4, so a shared accumulator can carrym4without changingskewnessresults, but the partial
state should still match Spark's (n, avg, m2, m3). - Support
GroupsAccumulatorfor performance. Sliding window frames need eitherretract_batchor
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.
Activity