[Epic]: Improved Support for order aware aggregates  #8583

Is your feature request related to a problem or challenge?

This type of function is important in streaming usecases and the goal is to support multiple order aware normal (as opposed to window) aggregates such as various N'th value, rank functions, etc and timeseries functions. These would be both for built in functions and user defined aggregates.

At the time of writing DataFusion supports three aggregate functions that can be "order aware": ARRAY_AGG, FIRST_VALUE and LAST_VALUE. This means that you can supply a ORDER BY clause to their argument, for example FIRST_VALUE(x ORDER BY time).

This Epic captures plans related to improving support

• Support multiple order aware aggregate functions in a query #8582
• Support ORDER BY in AggregateUDF  #8984

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response