Visitor : 419

Read Comments

Aggregation and Annotation in Django

Django Concept


Aggregation

Generating aggregates over a QuerySet

Aggregation :  Retrieve values that are derived by summarizing or aggregating a collection of objects.

Aggregation in Django Query set are used when aggregation functionalities like Sum , Avg , Count  is required across the query set. In a traditional way is like looping the entire queryset and getting the aggregation from a field.

Referring to the same model used in the Django Documentation.

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

If the requirement is to find the average age of authors in the above models , the indirect way would be to retrive the entire queryset and loop across to get the average age in a variable.

The standard way for achieving the same is to use the aggregate function.

>> Avg_Age = Author.objects.aggregate(Avg('age'))
>> {'age__avg': 34.35}

>> Avg_Age = Author.objects.aggregate(Avg('age'))['age__sum']
>> 34.35

Note : The first query will return a object , while the second one will return the value by appending ['<fieldName>__<aggregate(smallCase)>

Code Snippet

>> Book.objects.all().aggregate(Avg('price')) 
{'price__avg': 34.35} # Returns Object

>>> Book.objects.aggregate(Avg('price'))
{'price__avg': 34.35} # all is redundant and can be excluded

## Aggregate with Expicit name
>>> Book.objects.aggregate(average_price=Avg('price'))
{'average_price': 34.35} # Specify name for return

Note : 
aggregate() is a terminal clause for a QuerySet that, when invoked, returns a dictionary of name-value pairs. The name is an identifier for the aggregate value; the value is the computed aggregate. The name is automatically generated from the name of the field and the aggregate function. If you want to manually specify a name for the aggregate value, you can do so by providing that name when you specify the aggregate clause:


## Multiple Aggregates
>>> from django.db.models import Avg, Max, Min
>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

##Aggregate with filter
>> obj.portfolio_stock_RealizedPL = equity_portfolio_Trade.objects.filter(stock_master_id =zrec['stock_master_id'] , equity_portfolio_id =zrec['equity_portfolio_id'] ).aggregate(Sum('stock_RealizedPL'))['stock_RealizedPL__sum']

 

Annotate

Generating aggregates for each item in a QuerySet. Annotate returns a query set


##Returns Queryset with count 
>>> q = Book.objects.annotate(Count('authors'))

### Annotate wit Values
## Group by field stock_master and return sum of stock_dividend field
>>> obj_dividend_group = stock_dividend.objects.all().values('stock_master').annotate(zdiv_tot = Sum('stock_dividend')).order_by('-zdiv_tot')

## Annotate with multiple fields and order by

obj_equity = equity_portfolio_stock.objects.values('stock_master').annotate(zxirr = Avg('portfolio_stock_xirr'), zallocation = Sum('portfolio_stock_value'),
zdev_peak = Avg('portfolio_stock_DevPeak'),zholdingVal = Sum('portfolio_stock_value'),zcurrentVal = Sum('portfolio_stock_currentval'),zPL = Sum('portfolio_stock_PL'),zrelPL = Sum('portfolio_stock_RealizedPL'),zNAV = Avg('portfolio_stock_currentNAV'),zdividend = Avg('portfolio_stock_dividend'),zqty = Sum('portfolio_stock_qty')).order_by('-zxirr')

 

Note :

Annotate with Values Key : Group By functionality

Ordinarily, annotations are generated on a per-object basis - an annotated QuerySet will return one result for each object in the original QuerySet. However, when a values() clause is used to constrain the columns that are returned in the result set, the method for evaluating annotations is slightly different. Instead of returning an annotated result for each result in the original QuerySet, the original results are grouped according to the unique combinations of the fields specified in the values() clause. An annotation is then provided for each unique group; the annotation is computed over all members of the group.



Add Comments