[ACCEPTED]-Missing 'Median' Aggregate Function in Django?-aggregate-functions

Accepted answer
Score: 27

Here's your missing function. Pass it a 11 queryset and the name of the column that 10 you want to find the median for:

def median_value(queryset, term):
    count = queryset.count()
    return queryset.values_list(term, flat=True).order_by(term)[int(round(count/2))]

That wasn't 9 as hard as some of the other responses seem 8 to indicate. The important thing is to 7 let the db sorting do all of the work, so 6 if you have the column already indexed, this 5 is a super cheap operation.

(update 1/28/2016) If you want 4 to be more strict about the definition of 3 median for an even number of items, this 2 will average together the value of the two 1 middle values.

def median_value(queryset, term):
    count = queryset.count()
    values = queryset.values_list(term, flat=True).order_by(term)
    if count % 2 == 1:
        return values[int(round(count/2))]
    else:
        return sum(values[count/2-1:count/2+1])/Decimal(2.0)
Score: 15

Because median isn't a SQL aggregate. See, for 1 example, the list of PostgreSQL aggregate functions and the list of MySQL aggregate functions.

Score: 7

Well, the reason is probably that you need to 6 track all the numbers to calculate median. Avg, Count, Max, Min, StDev, Sum, and 5 Variance can all be calculated with constant 4 storage needs. That is, once you "record" a 3 number you'll never need it again.

FWIW, the 2 variables you need to track are: min, max, count, <n> = avg, <n^2> = avg 1 of the square of the values.

Score: 2

A strong possibility is that median is not 2 part of standard SQL.

Also, it requires 1 a sort, making it quite expensive to compute.

Score: 2

I have no idea what db backend you are using, but 3 if your db supports another aggregate, or 2 you can find a clever way of doing it, You 1 can probably access it easily by Aggregate.

Score: 1

FWIW, you can extend PostgreSQL 8.4 and 4 above to have a median aggregate function 3 with these code snippets.

Other code snippets (which work for 2 older versions of PostgreSQL) are shown here. Be sure 1 to read the comments for this resource.

More Related questions