Repository navigation
Description
Is your feature request related to a problem or challenge?
Some built in aggregates (such as FIRST_VALUE, LAST_VALUE and ARRAY_AGG) support an optional ORDER BY argument that defines the order they see their input. For example:
❯ create table foo(x int, y int) as values (1, 100),(2, 100),(0, 200);
0 rows in set. Query took 0.003 seconds.
-- note the `ORDER BY x` in the argument to `FIRST_VALUE`
❯ select FIRST_VALUE(x ORDER BY x) from foo GROUP BY y;
+--------------------+
| FIRST_VALUE(foo.x) |
+--------------------+
| 1 |
| 0 |
+--------------------+
2 rows in set. Query took 0.008 seconds.This is not supported today in user defined aggregates
Describe the solution you'd like
I would like to be be able to create a user defined aggregate that can specify its input order.
This would roughly require:
- Extending the
AggregateUDFImpltrait to communicate the ordering somehow . - Updating the implementation of https://docs.rs/datafusion/latest/datafusion/physical_plan/trait.AggregateExpr.html#method.order_bys
- writing an end to end test in https://github.com/apache/arrow-datafusion/blob/main/datafusion/core/tests/user_defined/user_defined_aggregates.rs showing it all working
Here are some other places that likely need to changed
https://github.com/apache/arrow-datafusion/blob/b5db7187763bc4511aaffdd6d89b2f0908f17938/datafusion/core/src/physical_planner.rs#L242-L252
Maybe looking at how OrderSensitiveArrayAgg is implemented can help https://github.com/apache/arrow-datafusion/blob/5d70c32a9a4accf21e9f27ff5ed62666cbbcbe54/datafusion/physical-expr/src/aggregate/array_agg_ordered.rs#L45
Describe alternatives you've considered
No response
Additional context
No response
Activity
alamb commentedon Jan 24, 2024
This is a neat project to work on, I think largely it would be an API design question
jayzhan211 commentedon Jan 25, 2024
Interested in this
jayzhan211 commentedon Jan 26, 2024
I think we also need to update
order_byinsql_function_to_exprhttps://github.com/apache/arrow-datafusion/blob/8a4bad46540598c6acdf432bde08c2a4c76c5039/datafusion/sql/src/expr/function.rs#L166-L172
jayzhan211 commentedon Jan 28, 2024
@alamb I'm trying to add
ordering_reqinAggregateUDFImpl. ButLexOrderingis physical_expr, importing it causes cyclic dependency.I'm testing with
FirstValueAccumulatorwhich has argumentordering_req: LexOrdering. BothOrderSensitiveArrayAggandFirstValueincludesordering_req: LexOrdering, so it seems reasonable to me that we can also haveLexOrderingfor UDF. However, I'm not sure how can I have LexOrdering for AggregateUDFImpl without cyclic dependency.If
ordering_reqreturn withVec<datafusion_expr::Expr>instead ofLexOrdering, then UDF caller needs to convert Expr::Sort to LexOrdering by themself inside their accumulator likeFirstValueUDFAccumulator::new(data_type: &DataType, ordering_req: Option<Vec<Expr>>),not sure does it make sensejayzhan211 commentedon Jan 29, 2024
It seems that #8793 has the similar issue #8793 (comment). But I'm not sure can I also move
trait PhysicalExprandstruct PhysicalSortExprtodatafusion_expr.alamb commentedon Jan 29, 2024
Maybe we can have the AggregateUDF declare its needed ordering in terms of
Exprs and then translate toLexOrderingas part of physical planningPerhaps we can follow the model of
In ListingOptions: https://docs.rs/datafusion/latest/datafusion/datasource/listing/struct.ListingOptions.html
jayzhan211 commentedon Mar 16, 2024
@alamb Do you know the rationale of
AccumulatorFactoryFunction, why do we need a closure like argument for accumulator, but not directly passing Accumulator around (withArc<dyn Accumulator>orBox<dyn Accumulator>)? Can we create Accumulator directly without any given arguments?For example
Rewrite to
The current
AccumulatorFactoryFunctionrequires given arguments, which is not flexible for arbitrary arguments for UDAF cases. I'm finding the way that do not require given arguments but get the accumulator directlyalamb commentedon Mar 16, 2024
One thing that may be related is that the hash aggregator creates an
Accumulatorper group (not per aggregate function in the query).And each
Accumulatorstores state for a particular group (so a singleAccumulatorinstance can't be shared across groups, for example)jayzhan211 commentedon Apr 5, 2024
Complete with #9874