Support for postgresql aggregate options

Describe the use case

Postgresql supports many options for aggregate functions, like distinct, filter, etc. Documentation at https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES

There is currently support for

• distinct using distinct
• order_by using aggregate_order_by.

A possible api may be to replace aggregate_order_by with an aggregate_options that accepts also other functionality.

Databases / Backends / Drivers targeted

postgresql

Example Use

select array_agg(distinct v order by v desc) FILTER (WHERE v < 3)
from (
  select v
  from generate_series(1, 3) v
  union all
  select v
  from generate_series(2, 5) v
) as v

Additional context

No response

Start by reading SQLAlchemy's existing distinct support and PostgreSQL aggregate_order_by API, then compare them with PostgreSQL's aggregate-expression documentation. Use the provided array_agg example as the target behavior and verify that the proposed API can represent its FILTER and ordering options in generated SQL.