Repository navigation
Description
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->NULLm2 == 0->NULL(orNaNwhenspark.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);
-- 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). NaNinput poisons the buffer and yieldsNaN(m2 == 0.0is false forNaN).+/-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 squares/fourth powers overflow
DOUBLE(e.g.1e100mixed with small values). - Input types:
TINYINT/SMALLINT/INT/BIGINT,FLOAT,DECIMAL(38, x)(cast toDOUBLEfirst, so
precision is lost before accumulation), numeric strings. Non-numeric strings:NULLwith ANSI off,
CAST_INVALID_INPUTwithspark.sql.ansi.enabled=true(default on in 4.0+). kurtosis(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
-1.5069204152249134ingroup-by.sql.outand-1.506920415224914in
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,
classesCentralMomentAgg(shared update/merge,momentOrder = 4) andKurtosis. - DataFusion core has no
kurtosisorskewness.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
skewness(momentOrder = 3) in a new module under
crates/spark/src/function/aggregate/. The state (nasDOUBLE,avg,m2,m3,m4) should be
exposed as the partial-aggregate state so a partial/final split behaves like Spark's. - 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/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
andygrove commentedon Nov 19, 2026
I prototyped this in Comet (apache/datafusion-comet#4818) and am closing that PR in favour of doing it here, so that
kurtosisandskewnesscan share one implementation and Comet can use it. The prototype branch isfeat/kurtosis-native-supportonandygrove/datafusion-comet(head4d4e573afe). The code is innative/spark-expr/src/agg_funcs/kurtosis.rsandwelford.rs. Review found several things that are easy to get wrong, so I'm recording them here.Layout that worked
[n, avg, m2, m3, m4], allFloat64, which is Spark'sCentralMomentAggbuffer. 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'supdateExpressionsDefandmergeExpressions, in the same operation order, so they match bit-for-bit.moments4_update,moments4_merge) beside the existing variance and covariance helpers. The order-4 recurrence subsumes the order-2 one, soskewness(momentOrder = 3) is only a differentevaluateover the same state. That is why Implement Spark-compatibleskewness#180 should share the accumulator rather than copy it.Semantics that review caught
m2 * m2, notm2. Spark guards onm2 == 0, butevaluateExpressiondivides bym2 * m2. For inputs1e-100and2e-100,m2is non-zero but its square underflows to exactly0.0. Spark'sDividethen applies its own zero-divisor rule:NULLwith ANSI off,DIVIDE_BY_ZEROwith ANSI on. A plain IEEE divide returnsNaN. This is a different switch fromspark.sql.legacy.statisticalAggregate, which governs only them2 == 0branch.DivideinsideKurtosis.evaluateExpressioncaptures its eval mode at analysis time. A temporary view analyzed with ANSI off and queried with it on returnsNULL. The reverse raisesDIVIDE_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.n2 == 0inmerge_batchgives a different answer when other partials overflow toNaN. With values near5e77in one file and an all-NULL second file, Spark returnsNaNand a version that skips the empty partial returns-1.22. The merge has to run Spark'smergeExpressionsfor every partial, including empty ones.kurtosis(...)query context, as Spark's does.NaNinput yieldsNaN. A single finite row or all-equal values yieldNULL, orNaNunder the legacy flag. A single infinite row yieldsNaN, becauseinf - infpoisonsm2.Tests worth porting
ExpressionDescriptionexamples (-0.7014368047529627and0.19432323191699075).moments4_mergeis only caught by a bit-exact comparison over several files. The1e-9tolerance in the Rust merge test would not catch one.Performance gap
GroupsAccumulator, so groupedkurtosisran through DataFusion's genericGroupsAccumulatorAdapter, which costs one boxedAccumulatorand aScalarValueround trip per group per batch.VarianceGroupsAccumulatorin Comet keeps flatVec<f64>state and is the model. The vectorized version should be written once forkurtosisandskewnessagainst the shared state. I did not benchmark either.Open question
retract_batchor 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.