Repository navigation
Description
Part of #173 and apache/datafusion-comet#7300.
What the function does
percentile_disc(percentage) WITHIN GROUP (ORDER BY col [ASC|DESC]) returns a value that actually occurs in the input:
the smallest value (in the requested sort order) whose cume_dist() is at least percentage. PercentileDisc
extends PercentileBase with frequency = 1L. col must be NUMERIC, INTERVAL DAY TO SECOND or
INTERVAL YEAR TO MONTH (STRING is implicitly cast to DOUBLE). percentage is a foldable DOUBLE in
[0.0, 1.0]. The array form is also accepted by PercentileBase and returns an array.
Return type: DOUBLE for numeric input, so the value is converted to double even for integer or decimal input. For
interval input the result has the same interval kind. The result is nullable, and empty or all-NULL groups give NULL.
DISTINCT is rejected. Exactly one ORDER BY item is required.
Since 4.0.0 as a registered function (PercentileDiscBuilder). In 3.4 / 3.5 the syntax is only a parser rule.
Examples
SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10) AS tab(col);
-- 0.0
SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY col)
FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
-- 0-0
-- percentiles.sql.out, view aggr(k, v):
-- (0,0),(0,10),(0,20),(0,30),(0,40),(1,10),(1,20),(2,10),(2,20),(2,25),(2,30),(3,60),(4,NULL)
SELECT k, percentile_disc(0.25) WITHIN GROUP (ORDER BY v),
percentile_disc(0.25) WITHIN GROUP (ORDER BY v DESC)
FROM aggr GROUP BY k ORDER BY k;
-- 0 10.0 30.0
-- 1 10.0 20.0
-- 2 10.0 30.0
-- 3 60.0 60.0
-- 4 NULL NULL
-- p = 0.0, 0.1, ..., 1.0 (percentiles.sql.out)
-- FROM VALUES (0), (1) -> 0.0 0.0 0.0 0.0 0.0 0.0 1.0 1.0 1.0 1.0 1.0
-- FROM VALUES (0), (1), (2) -> 0.0 0.0 0.0 0.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
-- FROM VALUES (0), (1), (2), (3), (4) -> 0.0 0.0 0.0 1.0 1.0 2.0 2.0 3.0 3.0 4.0 4.0
-- same, with spark.sql.legacy.percentileDiscCalculation=true
-- -> 0.0 0.0 0.0 1.0 1.0 2.0 2.0 2.0 3.0 3.0 4.0
-- postgreSQL/aggregates_part4.sql.out (tenk1.thousand)
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY thousand) FROM tenk1;
-- 499.0Edge cases to test
- Algorithm (non-legacy):
Nis the row count,higher = ceil(N * p), and the result is the first sorted key whose
cumulative count is at leasthigher. Forp = 0that is the first key. spark.sql.legacy.percentileDiscCalculation(internal, defaultfalse, added in 3.3.4) switches to the old algorithm,
which usespercentile_contpositions without interpolation (pos = (N - 1) * p, take the lower key). The legacy row
above shows where the two differ (p = 0.7 and p = 0.9). The flag is read when the expression is constructed.DESCreverses the sort and then applies the same algorithm. It is not1 - pon the ascending sequence: when
N * pis an integer the two differ. From the code,percentile_disc(0.25) WITHIN GROUP (ORDER BY v DESC)over
(1),(2),(3),(4)picks the 1st largest value (4.0), butpercentile_disc(0.75)ascending picks the 3rd smallest
(3.0).- Empty input, all-NULL groups, and a
FILTERthat removes every row give NULL. A single row gives that value as double
for everyp(first p-sweep row above). - Duplicates: each distinct value is stored once with a count, so ties are handled through cumulative counts.
- Integer / decimal input is returned as
DOUBLE.BIGINTvalues above 2^53 and wide decimals lose precision. Interval
input is returned exactly (0-10/2-6for theintervalsviewdtcolumn). - NaN sorts largest, and
-0.0/0.0compare equal but are separate keys (see thepercentileissue). - Errors are the same as
percentile_cont:INVALID_WITHIN_GROUP_EXPRESSION.DISTINCT_UNSUPPORTEDINVALID_WITHIN_GROUP_EXPRESSION.WITHIN_GROUP_MISSINGINVALID_WITHIN_GROUP_EXPRESSION.WRONG_NUM_ORDERINGSWRONG_NUM_ARGS.WITHOUT_SUGGESTIONDATATYPE_MISMATCH.NON_FOLDABLE_INPUT/UNEXPECTED_NULL/VALUE_OUT_OF_RANGEfor the percentageINVALID_WINDOW_SPEC_FOR_AGGREGATION_FUNCfor windows withORDER BYor a frame
- Window use with
OVER (PARTITION BY ...)(basic_payscases inpercentiles.sql.out, e.g. IT department
5186.0ascending and8113.0DESC). - Merge path: the state is a value-to-count map merged by adding counts, so the result is independent of partitioning.
Differences across Spark versions
- 3.4 / 3.5: parser-only syntax (
#percentilerule inSqlBaseParser.g4,AstBuilder.visitPercentile). There is no
FunctionRegistryentry, andDISTINCTis a parse error. - 4.0+:
PercentileDiscBuilderandSupportsOrderingWithinGroup, with theINVALID_WITHIN_GROUP_EXPRESSION.*errors. - The non-legacy algorithm and the legacy flag are the same in 3.4.3 through 4.2.0.
Implementation notes
- Spark:
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/percentiles.scala
(PercentileDisc.getPercentile,PercentileDiscBuilder,PercentileBase). - DataFusion core has no
percentile_disc. Corepercentile_cont/approx_percentile_contare not usable here. - Share the value-to-count accumulator with the Spark
percentile/percentile_contimplementations (separate
issues), using adiscreteflag and areverseflag from theWITHIN GROUPsort direction
(AccumulatorArgs::order_bys,supports_within_group_clause). Module:crates/spark/src/function/aggregate/. - Tests: add
crates/sqllogictest/test_files/aggregate/percentile_disc.slt. The p-sweep andaggrcases from
percentiles.sql.outport directly. 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