Skip to content

Epic: Ordered Set Aggregate Functions #12824

Open

Description

@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

Screenshot 2024-10-09 at 8 49 19 AM

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

Activity

  1. Garamda commented on Oct 9, 2024

    @Garamda
    Contributor

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

  2. jayzhan211 commented on Oct 9, 2024

    @jayzhan211
    ContributorAuthor

    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

  3. Garamda commented on Oct 9, 2024

    @Garamda
    Contributor

    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,

    Screenshot 2024-10-09 at 1 42 05 PM

    not including those two functions, is that correct?

    Screenshot 2024-10-09 at 1 25 45 PM

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

  4. jayzhan211 commented on Oct 9, 2024

    @jayzhan211
    ContributorAuthor

    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.

  5. changed the title [-]Ordered Set Aggregate Functions[/-] [+]Epic: Ordered Set Aggregate Functions[/+] on Oct 9, 2024
  6. Garamda commented on Oct 9, 2024

    @Garamda
    Contributor

    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.)

  7. jonathanc-n commented on Oct 11, 2024

    @jonathanc-n
    Contributor

    @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

  8. Garamda commented on Oct 31, 2024

    @Garamda
    Contributor

    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

  9. Garamda commented on Feb 2, 2025

    @Garamda
    Contributor

    PR is ready to get review : #13511

    cc. @jayzhan211

  10. added
    EPICA larger project, actively underway, with sub tasks
    PROPOSAL EPICA proposal being discussed that is not yet fully underway
    and removed
    EPICA larger project, actively underway, with sub tasks
    on Aug 12, 2025
  11. Jefffrey commented on Oct 17, 2025

    @Jefffrey
    Contributor

    percentile_cont(fraction) added by #17988

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    PROPOSAL EPICA proposal being discussed that is not yet fully underwayenhancementNew feature or request

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions