Recommended way to support "WITHIN GROUP"

[lightupyiqian]

Hi, Is there a way to accomplish percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)? https://www.postgresql.org/docs/9.4/functions-aggregate.html

[benjaminsanborn]

Also interested in a similar thing: I have implemented some PostgreSQL specific AggregateFunction in our code, and I'd love to be able to pass ORDER BY sort_expression into them, ala JSONB_AGG("table_name" ORDER BY "table_name"."sort_expression")

I think in both cases we would like to extend the behavior of AggregateFunction to include some more inner expressions?

[benjaminsanborn]

Just to follow up; I extended AggregateFunction and implemented get_special_params_sql to add the appropriate keywords inside the function; as is done in AnalyticFunction.

[mccarthyryanc]

This was a total hack, but one way I got something working was to add it as a Field. That tended to give better rendered queries, than just putting in a plain string:

from pypika import Table, Query, Field

table = Table('tablename')
q = Query\
    .from_(table)\
    .select(
        Field('percentile_cont(0.1) within group (order by column asc) as col_perc')
    )
q.get_sql()

'SELECT "percentile_cont(0.1) within group (order by column asc) as col_perc" FROM "tablename"'

Again, this was a hack, use it at your own risk! It would be better to have an actual WithinGroup implementation in pypika, but this at least got me an answer.