Epic: Ordered Set Aggregate Functions

[jayzhan211]

Is your feature request related to a problem or challenge?

DataFusion doesn't support ordered-set aggregate functions yet.

Those functions are supported in Postgres, DuckDB, so I think we should support them in this repo as the core functions

All the aggregates listed in Table 9-51 ignore null values in their sorted input. For those that take a fraction parameter, the fraction value must be between 0 and 1; an error is thrown if not. However, a null fraction value simply produces a null result.

Related PR, #11732.

We have approx_percentile_cont and approx_percentile_cont_with_weight already, maybe we can start from these two functions

• approx_percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)
• approx_percentile_cont_with_weight(fraction) WITHIN GROUP (ORDER BY sort_expression)

Tasks to do

• Support WITHIN GROUP syntax
  • Support WITHIN GROUP syntax to standardize certain existing aggregate functions  #13511
• mode() WITHIN GROUP (ORDER BY sort_expression)
• percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)
  • feat: Add percentile_cont aggregate function #17988
• percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)
  • Support array argument for percentile_cont aggregate function #18600
• percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)
• percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)
• Clarify our support of ordered-set aggregate functions
  • docs: refine AggregateUDFImpl::is_ordered_set_aggregate documentation #17805
• WITHIN GROUP needs to be more strict #18109
• Refactor ordered-set aggregate Dataframe APIs to align with SQL #18279
• Consider renaming AggregateUDFImpl::is_ordered_set_aggregate to AggregateUDFImpl::supports_within_group_clause #18280
• Disallow order by within ordered-set aggregate functions argument lists #18281
• Support date types in percentile_cont aggregate function #18601

[Garamda]

Hi, I'm new here. I want to take the issue or participate in developing one of those functions. Would it be possible?

[jayzhan211]

Hi, I'm new here. I want to take the issue or participate in developing one of those functions. Would it be possible?

Sure, just type take then you will be assigned

[Garamda]

Alright. Before I take the issue, let me be clear. What I understood is that the things to be implemented are those five functions below,

---

not including those two functions, is that correct?

Let me double check because there are 7 tasks in total in this issue checklist.

[jayzhan211]

I think the first step is to solve #11732, fix the syntax of approx_percentile_cont and approx_percentile_cont _with_weight since there is no WITHIN GROUP currently. And the next step is to support order by and other functions.

[Garamda]

I see. So there are 3 sub tasks, I think.

1. fix the syntax of approx_percentile_cont and approx_percentile_cont_with_weight to support WITHIN GROUP (Standardize APPROX_PERCENTILE_CONT / PERCENTILE_CONT and similar aggregation functions #11732)
2. fix the same functions to support ORDER BY sort_expression
3. After finishing step 1 & 2, implement new functions below (since WITHIN GROUP & ORDER BY will be reused)

• mode() WITHIN GROUP (ORDER BY sort_expression)
• percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)
• percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression)
• percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)
• percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression)

If there is nothing that I missed, I can start from step 1.
So I think I can make a take comment on #11732 and get assigned, right?

(or if you want to reorganize sub tasks and make new issues for each one since it was changed into EPIC, I can wait.)

[jonathanc-n]

@Garamda @jayzhan211 After the WITHIN GROUP is added, I would like to work on this for the ORDER BY impelmentations for approx_percentile_cont and approx_percentile_cont_with_weight

[Garamda]

This task has taken longer than I initially expected, because recently I haven’t been able to fully focus on it. Nevertheless, I believe I can complete it successfully.

However, if this is a delay for the datafusion roadmap or has any negative effect on the ecosystem, I am open to passing this issue to a more experienced committer and shifting my focus to a other smaller issue.

If there’s still room for me to continue, I would like to see this issue through to completion.

I’m sharing this update to inform the current status, especially since some may be waiting for this issue’s completion.

cc. @jayzhan211 @jonathanc-n

[Garamda]

PR is ready to get review : #13511

cc. @jayzhan211