CODEWITHSUNDEEP
pythondjangopostgresql
nMountain
nMountain

Reputation: 43

Sum averages over date ranges in Django

I'm trying to construct a query in Django that sums averages that were taken (i.e. averaged) over a range of times.

Here is the relevant Django model:

class Data(models.Model):

    class Meta:
        verbose_name_plural = "Data"

    site = models.ForeignKey(Site)
    created_on = models.DateTimeField(auto_created=True)
    reported_on = models.DateTimeField(null=True, blank=True)
    baseline_power_kw = models.FloatField('Baseline Power (kw)', blank=True, null=True)
    measured_power_kw = models.FloatField('Measured Power (kw)', blank=True, null=True)

In my query, I'm trying to average sites' data over a range of times, and then sum those averages for each range of time. Here is the query I have so far, which I believe just gets the average of all sites' data over a range of times.

        t_data = Data.objects.filter(site__in=sites) \
                                  .filter(created_on__range=(start, end)) \
                                  .extra(select={'date_slice': "trunc(extract(epoch from created_on) / '60' )"}) \
                                  .values('date_slice') \
                                  .annotate(avg_baseline_power_kw=Avg('baseline_power_kw'),
                                            avg_measured_power_kw=Avg('measured_power_kw'),
                                            time=Min('created_on')) \
                                  .order_by('-created_on')

Do you know how I can proceed? I am using Django with Postgres.

Thanks!

Upvotes: 0

Views: 193

Answers (1)

Bob Barcklay
Bob Barcklay

Reputation: 1692

If you add 'site' to your .values() clause, like this:

.values('date_slice', 'site')

and remove the order_by, which will cause the 'created_on' field to get added to the generated SQL GROUP BY, you should get averages for your two measurements for each slice+site. You can then sum those values to get the totals across all sites.

Upvotes: 2

Related Questions