You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
SELECT median(n) FROM (SELECT0AS n UNION ALLSELECT1UNION ALLSELECT2UNION ALLSELECT3UNION ALLSELECT4UNION ALLSELECT5UNION ALLSELECT6UNION ALLSELECT7UNION ALLSELECT8UNION ALLSELECT9);
Expected behavior
same value of percentile 50
in datafusion-cli 51
SELECT percentile_cont(n,0.5) FROM (SELECT0AS n UNION ALLSELECT1UNION ALLSELECT2UNION ALLSELECT3UNION ALLSELECT4UNION ALLSELECT5UNION ALLSELECT6UNION ALLSELECT7UNION ALLSELECT8UNION ALLSELECT9);
Additional context
testing ibis tests in Sail and add agg funtion
related to lakehq/sail#1072
I have a question about the intended semantics here: is median supposed to behave as a discrete median (returning a value of the same type as the input, e.g. INT), or more like a continuous percentile (similar to percentile_cont(..., 0.5))?
For example, in DataFusion today, median for integer inputs effectively takes the midpoint but returns it in the input type, so for values [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] you get 4, while a continuous percentile like percentile_cont(n, 0.5) would return 4.5.
It would be helpful to clarify whether the goal here is:
to keep median as a discrete median that preserves the input type, or
to align median with a continuous percentile definition (and possibly promote to Float64).
Thanks, and sorry — I didn’t know that DataFusion aims to follow Postgres semantics by default for aggregates.
I will check how Postgres defines median for these cases. If the behavior I implemented does not match the Postgres semantics, I will close this issue and move the Sail-specific version to the datafusion-spark crate instead, since Spark’s median behaves differently.
Hi again @alamb
I couldn't find a built-in median function in PostgreSQL (I am definitely not a Postgres expert), but I did find the two official options:
percentile_cont(0.5) WITHIN GROUP (ORDER BY x) — continuous median with interpolation
percentile_disc(0.5) WITHIN GROUP (ORDER BY x) — discrete median
So it seems that PostgreSQL does not define a standalone median() function, and relies on these two percentile variants instead.
Let's close this issue for now. I’ll open a PR once the implementation is integrated on the Sail side, based on the ongoing work in the current PR.
Activity
davidlghellin commentedon Nov 21, 2025
I have a question about the intended semantics here: is
mediansupposed to behave as a discrete median (returning a value of the same type as the input, e.g.INT), or more like a continuous percentile (similar topercentile_cont(..., 0.5))?For example, in DataFusion today,
medianfor integer inputs effectively takes the midpoint but returns it in the input type, so for values[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]you get4, while a continuous percentile likepercentile_cont(n, 0.5)would return4.5.It would be helpful to clarify whether the goal here is:
medianas a discrete median that preserves the input type, ormedianwith a continuous percentile definition (and possibly promote toFloat64).alamb commentedon Nov 21, 2025
In general, I think DataFusion should follow existing system semantics when possible. I think for aggregates that means:
So basically, what does postgres return for median in this case?
If spark has different semantics, perhaps we can add a new median implementation in the datafusion-spark crate with those semantics
davidlghellin commentedon Nov 21, 2025
Thanks, and sorry — I didn’t know that DataFusion aims to follow Postgres semantics by default for aggregates.
I will check how Postgres defines
medianfor these cases. If the behavior I implemented does not match the Postgres semantics, I will close this issue and move the Sail-specific version to thedatafusion-sparkcrate instead, since Spark’s median behaves differently.Thanks for the clarification!
alamb commentedon Nov 21, 2025
No worries! We haven't documented the postgres thing -- it was hard to get consensus on the wording
davidlghellin commentedon Nov 21, 2025
Hi again @alamb
I couldn't find a built-in
medianfunction in PostgreSQL (I am definitely not a Postgres expert), but I did find the two official options:percentile_cont(0.5) WITHIN GROUP (ORDER BY x)— continuous median with interpolationpercentile_disc(0.5) WITHIN GROUP (ORDER BY x)— discrete medianSo it seems that PostgreSQL does not define a standalone
median()function, and relies on these two percentile variants instead.Let's close this issue for now. I’ll open a PR once the implementation is integrated on the Sail side, based on the ongoing work in the current PR.
Thanks a lot for the guidance!