Skip to main content

Django aggregate functions which operate conditionally (i.e. generate SQL `CASE WHEN` statements)

Project description

Build Status Latest PyPI version

Python 2.7

Django 1.6 Django 1.7

(Django 1.4 and 1.5 are not possible due to limitation of older `SQLCompiler` class)

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

django-conditional-aggregates-0.5.0.tar.gz (4.4 kB view hashes)

Uploaded Source

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page