Django aggregate functions which operate conditionally (i.e. generate SQL `CASE` statements)
Project description
(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 on 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 djconnagg 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 djconnagg.aggregates 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.4.0.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | ed434799e998bf7e3bc80d2c5245d5c7911f6b6e4b79930fea642beb4cb9e542 |
|
MD5 | d13d9068c29b939e9cc787971406a2d2 |
|
BLAKE2b-256 | 447a9cf8a777d2fdad1b6c87291622eb23ecd019c29b5f90c1557c5c16a08025 |