Using DISTINCT with Multiple Condtional Aggregations (Annotate)

Question

Python: 2.7 Version: Django 1.11

Hello,

I am having issues with using conditional aggregation with COUNT DISTINCT in my Django query. The problem is when I join a separate table, my distinct count on the IDs does not seem to work. I have a query where I do a conditional aggregation to count days and sum minutes based on other attributes.

In the example below, we want to query for two things at the same time:

The count of the 'number of sunny days logged' for each dog.
The total amount of time we walked each dog

(Please bear with me on the example. I tried simplifying the models)

Models:

from django.db import models

class Dog(models.Model):
    name = models.CharField(max_length=255)

class DailyLog(models.Model):
    dog = models.ForeignKey(Dog, on_delete=models.CASCADE)
    is_sunny = models.BooleanField(default=False)

class WalkSession(models.Model):
    daily_log = models.ForeignKey(DailyLog, on_delete=models.CASCADE)
    minutes_walked = models.IntegerField()

Populate Data via Migration:

 d1 = Dog.objects.create(name="Fido")
 d2 = Dog.objects.create(name="Fido2")
 d3 = Dog.objects.create(name="Fido3")

 dl1 = DailyLog.objects.create(dog=d1, is_sunny=True)
 dl2 = DailyLog.objects.create(dog=d2, is_sunny=False)
 dl3 = DailyLog.objects.create(dog=d3, is_sunny=False)

 WalkSession.objects.create(daily_log=dl1, minutes_walked=100)
 WalkSession.objects.create(daily_log=dl1, minutes_walked=200)
 WalkSession.objects.create(daily_log=dl2, minutes_walked=50)
 WalkSession.objects.create(daily_log=dl3, minutes_walked=999)

Python Console:

Simple check for the minutes summed.

   DailyLog.objects.all().values('dog__name').annotate(total_minutes_walked=Sum('walksession__minutes_walked'))

Result: <QuerySet [{'dog__name': 'Fido', 'total_minutes_walked': 300},
     {'dog__name': 'Fido2', 'total_minutes_walked': 50},
     {'dog__name': 'Fido3', 'total_minutes_walked': 999}]>

Simple check on the number of sunny days logged.

DailyLog.objects.all().values('dog__name').annotate(sunny_days_logged=Count(Case(When(is_sunny=True, then='id'), distinct=True)))

Result: <QuerySet [{'dog__name': 'Fido', 'sunny_days_logged': 1},
     {'dog__name': 'Fido2', 'sunny_days_logged': 0},
     {'dog__name': 'Fido3', 'sunny_days_logged': 0}]>

Query that joins the DailyLog table and WalkSession with conditional aggregation.

We now see that sunny days logged is '2'. We expected this to be '1'.

DailyLog.objects.all().values('dog__name').annotate(total_minutes_walked=Sum('walksession__minutes_walked'), sunny_days_logged=Count(Case(When(is_sunny=True, then='id'), distinct=True)))

Result: <QuerySet [{'dog__name': 'Fido', 'total_minutes_walked': 300, 'sunny_days_logged': 2},
     {'dog__name': 'Fido2', 'total_minutes_walked': 50, 'sunny_days_logged': 0},
     {'dog__name': 'Fido3', 'total_minutes_walked': 999, 'sunny_days_logged': 0}]>

I looked at the queries generated and it seems that the DISTINCT option is dropped when we use CASE WHEN.

SELECT dogwalker_dog.name,
        SUM(dogwalker_walksession.minutes_walked) AS 'total_minutes_walked',
        COUNT(CASE
              WHEN dogwalker_dailylog.is_sunny = true THEN dogwalker_dailylog.id ELSE NULL END) AS 'sunny_days_logged'
    FROM dogwalker_dailylog
    INNER JOIN dogwalker_dog
    ON dogwalker_dailylog.dog_id = dogwalker_dog.id
    LEFT OUTER JOIN dogwalker_walksession
    ON dogwalker_dailylog.id = dogwalker_walksession.daily_log_id
GROUP BY dogwalker_dog.name

DISTINCT was missing from COUNT.
COUNT(DISTINCT CASE WHEN dogwalker_dailylog.is_sunny = true THEN dogwalker_dailylog.id ELSE NULL END) AS 'sunny_days_logged'

Documentation does say that multiple aggregations could show wrong results.

Reference: https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#combining-multiple-aggregations I was attempting to use the DISTINCT parameter to help with this issue.

Why does DISTINCT get dropped when using CASE WHEN?
Would it be best to separate the queries instead of trying to calculate multiple things in one query?

score 1 · Answer 1 · answered Jan 15 '20 at 22:45

My mistake on the brackets and missing option output_field option.

The statement below yields the correct count of Sunny days for each dog.

DailyLog.objects.all().values('dog__name').annotate(total_minutes_walked=Sum('walksession__minutes_walked'), sunny_days_logged=Count(Case(When(is_sunny=True, then='id'), output_field=IntegerField()), distinct=True))

Using DISTINCT with Multiple Condtional Aggregations (Annotate)

1 Answers1