1

I am trying to do something very similar to: Annotate Custom SQL Function (similar to date_trunc) to Django ORM Queryset

but have found the solution to be inadequate.

Using the same example as found in the prior link, suppose we have a Django model as such.

class Measurement(models.Model):

    device_id = models.IntegerField(primary_key=True)
    time = models.DateTimeField()
    s0 = models.FloatField(blank=True, null=True)
    s1 = models.FloatField(blank=True, null=True)

We are trying to leverage the following in the ORM.

SELECT
  time_bucket('1 minute', time) AS tb,
  AVG(s0) 
FROM measurements
WHERE
  time >= to_timestamp(1) AND
  time <= to_timestamp(2)
GROUP BY tb
ORDER BY tb ASC;

We have a successful custom function as such:

class TimeBucket(Func):

    function = 'time_bucket'
    template = '%(function)s(\'{bucket_width}\', %(expressions)s)'.format(bucket_width='1 minute')

But we see that running a query like the following:

qs = (
    Measurement.objects
    .filter(...)
    .annotate(tb=TimeBucket('time'))
    .values('tb')
    .annotate(s_desc=Avg('s0'))
    .order_by('tb')
)

and checking its corresponding qs.query will always include time in the GROUP BY query. I only want to group by tb, the derived value, as shown in the original SQL. From what I'm reading, you need only use values('tb') as we are doing above to group by tb, but Django seems to append on time for some reason. Why is this the case?

Is it possible that my problem is less with this external function and more that I'm trying to group by a calculated field?

Rhubarb
  • 198
  • 1
  • 11
  • 1
    try droping the second annotate, and checking whether its still has group by time. I feel like its caused by Avg aggregate. But not sure. – Blackdoor Jun 15 '20 at 03:55
  • That one doesn't have the `GROUP BY time` but defeats the purpose since I want to use this calculated field to compute other annotations. Even following something directly from the Docs of Timescale DB: ``` SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10; ``` Becomes: ``` metrics.objects.values(five_min=time_bucket(time, "5 minutes")).annotate(avg_cpu=Avg("cpu")) SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu) AS avg_cpu FROM metrics GROUP BY time, time_bucket('5 minutes', time); ``` – Rhubarb Jun 15 '20 at 04:38
  • I'm silly! I had a default ordering in my model! It was causing it to add a GROUP BY by the ordered fields. Thank you! – Rhubarb Jun 15 '20 at 15:28

0 Answers0