[Answered ]-Django Orm: Custom Select column with aggregate functions for grouped values

by

2👍

I found a nice solution with a parametrized class that works as an Aggreation Function:

def custom_aggregation(select_query):
    class SqlAggregate(models.sql.aggregates.Aggregate):
        sql_function = ''
        sql_template = select_query

    class VisitorRate(models.Aggregate):
        sql = SqlAggregate
        def add_to_query(self, query, alias, col, source, is_summary):
            aggregate = self.sql(col,
                                 source=source,
                                 is_summary=is_summary,
                                 **self.extra)
            query.aggregates[alias] = aggregate

    return VisitorRate


    aggregate_query = "SUM(CASE WHEN metric = visitors' THEN value ELSE 0 END) / NULLIF (SUM(value), 0)"
    AggregationFunction = custom_aggregation(aggregate_query)

    result = MetricHour.objects \
        .extra(select={'date': 'date_trunc(%s, timeframe)'}, select_params=[interval]) \
        .values('date') \
        .annotate(value=AggregationFunction('value'))
👤user524824

Leave a comment