andygrove opened a new issue, #181:
URL: https://github.com/apache/datafusion-spark/issues/181
Part of #173 and apache/datafusion-comet#7300.
### What the function does
`percentile(col, percentage [, frequency])` is an exact aggregate that
returns the percentile of a numeric or ANSI
interval column at `percentage`, interpolating linearly between neighbouring
values. `percentage` can be a single
value or an array of values. Every percentage must be foldable, non-NULL and
in `[0.0, 1.0]`. `frequency` is an
optional integral column (default `1L`) that gives a repeat count for each
row. Since 2.1.0.
Input types (`ImplicitCastInputTypes`): `col` must be `NUMERIC`, `INTERVAL
DAY TO SECOND` or `INTERVAL YEAR TO MONTH`.
`STRING` is implicitly cast to `DOUBLE`. `BOOLEAN`, `DATE`, `TIMESTAMP`,
`CALENDAR INTERVAL` and `VOID` columns are
rejected. Numeric children are not cast. Each value is converted to `double`
(`Decimal.toDouble`, `Number.doubleValue`)
only when the result is computed. `percentage` is cast to `DOUBLE` or
`ARRAY<DOUBLE>`. `frequency` must be integral
(`TINYINT`..`BIGINT`). `FLOAT`/`DOUBLE` frequencies are rejected.
Return type: `DOUBLE` for numeric input. For interval input the result has
the same interval kind, and the double
result is truncated with `toInt` / `toLong`. If `percentage` is an array,
the result is `ARRAY<that type>` with
`containsNull = false`. The result is nullable, and an empty or all-NULL
group returns NULL.
`median(col)` (since 3.4.0) is a `RuntimeReplaceableAggregate` that rewrites
to `percentile(col, 0.5)`, so implementing
`percentile` also covers `median`. `percentile_cont` / `percentile_disc` are
tracked separately. They rewrite to the
same `PercentileBase` machinery.
### Examples
```sql
SELECT percentile(col, 0.3) FROM VALUES (0), (10) AS tab(col);
-- 3.0
SELECT percentile(col, array(0.25, 0.75)) FROM VALUES (0), (10) AS tab(col);
-- [2.5,7.5]
SELECT percentile(col, 0.5) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10'
MONTH) AS tab(col);
-- 0-5
SELECT percentile(col, array(0.2, 0.5)) FROM VALUES (INTERVAL '0' SECOND),
(INTERVAL '10' SECOND) AS tab(col);
-- [0 00:00:02.000000000,0 00:00:05.000000000]
SELECT median(col) FROM VALUES (0), (10) AS tab(col);
-- 5.0
-- From DataFrameAggregateSuite "percentile_like" (year values 2012, 2013):
SELECT percentile(y, 0.3) FROM VALUES (2012), (2013) AS t(y);
-- 2012.2999999999997 (formula is (hi - pos) * lo + (pos - lo) * hi, not
lo + (hi - lo) * frac)
SELECT percentile(y, 0.3, 2) FROM VALUES (2012), (2013) AS t(y);
-- 2012.0
SELECT percentile(y, array(0.25, 0.75), 2) FROM VALUES (2012), (2013) AS
t(y);
-- [2012.0,2013.0]
SELECT percentile(y, array(0.25, 0.75), 2) FROM VALUES (2012), (2012),
(2013) AS t(y);
-- [2012.0,2012.75]
-- percentiles.sql.out, view aggr(k, v) with k=0: v in (0,10,20,30,40); k=4:
v = NULL
SELECT k, median(v), percentile(v, 0.5) FROM aggr GROUP BY k ORDER BY k;
-- 0 20.0 20.0
-- 1 15.0 15.0
-- 2 22.5 22.5
-- 3 60.0 60.0
-- 4 NULL NULL
```
### Edge cases to test
- Empty input and all-NULL groups return NULL. NULL `col` rows and NULL
`frequency` rows are skipped.
Rows with `frequency = 0` are skipped. If every row has frequency 0 the
result is NULL (`PercentileSuite` "null handling").
- Negative frequency raises `NEGATIVE_VALUES_IN_FREQUENCY_EXPRESSION`
(SQLSTATE 22003) at execution time:
"Found the negative value in <frequencyExpression>: <negativeValue>, but
expected a positive integral value."
- Single row returns that value as a double for any percentage.
- `percentage` NULL gives `DATATYPE_MISMATCH.UNEXPECTED_NULL`. An array
containing NULL (`array(0.1, null)`) fails as
`UNEXPECTED_INPUT_TYPE`. A non-literal percentage gives
`DATATYPE_MISMATCH.NON_FOLDABLE_INPUT`. A value outside
`[0, 1]` gives `DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE` with `valueRange`
`[0.0, 1.0]`. All of these are analysis
errors.
- Percentage exactly 0.0 / 1.0, and a percentage array whose order is not
sorted. The output keeps the input order.
- Interpolation formula: `(higher - pos) * lower + (pos - lower) * upper` in
`f64`, where `pos = (N - 1) * p` and `N`
is the sum of frequencies. The `2012.2999999999997` case above must match
bit for bit.
- Interval results are truncated, not rounded (`0.25` of `0..10` months is
`0-2`). Also test negative intervals.
- `BIGINT` values above 2^53 and `DECIMAL(38, x)` lose precision because
each value goes through `double`.
- NaN and -0.0: keys are compared with Spark's SQL ordering (NaN is largest,
and `-0.0` compares equal to `0.0`), but
the hash map keeps `0.0` and `-0.0` as separate keys.
`DataFrameAggregateSuite` SPARK-45599 expects
`percentile(val, 0.1)` over `[0.0, 2.0, 153.0, 168.0, 3252411229536261.0,
7.205759403792794e+16, 1.7976931348623157e+308, 0.25, NaN, NaN, -0.0, -128.0,
NaN, NaN]`
to be `-0.0`. Infinity inputs can produce NaN or Inf through interpolation.
- `DISTINCT`: `percentile(DISTINCT col, p)` is allowed and gives every
distinct value a frequency of 1.
- `percentile(v, 0.5) WITHIN GROUP (ORDER BY v)` raises
`INVALID_SQL_SYNTAX.FUNCTION_WITH_UNSUPPORTED_SYNTAX`.
- Use as a window aggregate (`OVER (PARTITION BY ...)`).
- Partial/final merge: the buffer is a map from value to count, merged by
adding counts, so the result does not depend
on partitioning. The one exception is the order of keys that compare equal
(`0.0` / `-0.0`) after the stable sort.
Test multi-partition input and compare with single-partition input.
### Differences across Spark versions
- 3.4.x: the `OpenHashSet` bug fixed by SPARK-45599 can drop `0.0` or `-0.0`
keys, so the result in the case above
differs. The fix and test are present in 3.5.8 and later.
- 3.4.x / 3.5.x: negative frequency raises `_LEGACY_ERROR_TEMP_2013`
("Negative values found in <frequencyExpression>").
4.0+ raises `NEGATIVE_VALUES_IN_FREQUENCY_EXPRESSION`, which adds the
value.
- `NON_FOLDABLE_INPUT` `inputName` is `percentage` in 3.x and ``
`percentage` `` in 4.x.
- Otherwise the calculation is the same from 3.4 to 4.2.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/percentiles.scala`
(`PercentileBase`, `Percentile`, `Median`). The buffer is an
`OpenHashMap[AnyRef, Long]`
(`TypedAggregateWithHashMapAsBuffer`). `eval` sorts the keys, builds
cumulative counts, and binary-searches
`lower + 1` / `higher + 1`.
- DataFusion core has `percentile_cont` and `median` in
`datafusion/functions-aggregate/src/percentile_cont.rs` and
`median.rs`. They use `index = p * (n - 1)`, but they differ from Spark in
several ways:
- Interpolation is `lower + (upper - lower) * weight`, with the weight
quantized to 1e-6 (`INTERPOLATION_PRECISION`).
- The return type follows the input type (decimals stay decimal) instead
of always being `DOUBLE`.
- There is no frequency argument, no array of percentages, and no interval
input.
- `p == 0` / `p == 1` take a special path.
Write a new accumulator rather than wrapping core. Comet currently maps
only the single-percentage numeric form to
core `percentile_cont`.
- Belongs in `crates/spark/src/function/aggregate/` (new `percentile.rs`),
next to `avg.rs` / `try_sum.rs`. Also
register `median` as an alias-style wrapper, or document that callers
rewrite it. A value-to-count state lets
`percentile_cont` / `percentile_disc` reuse the same accumulator with a
`reverse` flag and a discrete `getPercentile`.
- Tests: add `crates/sqllogictest/test_files/aggregate/percentile.slt`.
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._
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]