Django aggregate functions which operate conditionally (i.e. generate SQL `CASE` statements)
Project description
currently tested against Django 1.4 - Django 1.7
(Django 1.4 and 1.5 needed an ugly hack but our tests pass, if you find any edge cases please post an issue)
Note: from Django 1.8 this module is not needed as support is built-in: https://docs.djangoproject.com/en/1.8/ref/models/conditional-expressions/#case
Sometimes you need some conditional logic to decide which related rows to ‘aggregate’ in your aggregation function.
In SQL you can do this with a CASE clause, for example:
SELECT
stats_stat.campaign_id,
SUM(
CASE WHEN (
stats_stat.stat_type = a
AND stats_stat.event_type = v
)
THEN stats_stat.count
ELSE 0
END
) AS impressions
FROM stats_stat
GROUP BY stats_stat.campaign_id
Note this is different to doing Django’s normal .filter(...).aggregate(Sum(...)) …what we’re doing is effectively inside the Sum(...) part of the ORM.
I believe these ‘conditional aggregates’ are most (perhaps only) useful when doing a GROUP BY type of query - they allow you to control exactly how the values in the group get aggregated, for example to only sum up rows matching certain criteria.
Usage:
pip install django-conditional-aggregates
from django.db.models import Q
from aggregates.conditional import ConditionalSum
# recreate the SQL example from above in pure Django ORM:
report = (
Stat.objects
.values('campaign_id') # values + annotate => GROUP BY
.annotate(
impressions=ConditionalSum(
'count',
when=Q(stat_type='a', event_type='v')
),
)
)
Note that standard Django Q objects are used to formulate the CASE WHEN(...) clause. Just like in the rest of the ORM, you can combine them with () | & ~ operators to make a complex query.
ConditionalSum and ConditionalCount aggregate functions are provided. There is also a base class if you need to make your own. The implementation of ConditionalSum is very simple and looks like this:
from aggregates.conditional import ConditionalAggregate, SQLConditionalAggregate
class ConditionalSum(ConditionalAggregate):
name = 'ConditionalSum'
class SQLClass(SQLConditionalAggregate):
sql_function = 'SUM'
default = 0
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Hashes for django-conditional-aggregates-0.3.0.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 7cfb6bae0dedfd06d2bab16f62d04bdf751b0aa26dd5324bafac86b9484b590a |
|
MD5 | 0a4ed41f22a5e278200d38aaf1306240 |
|
BLAKE2b-256 | 037e1a0bbaf39f916eb715a5294c607ef8715047d0bb827a24cd7d134e74a0ad |