将自定义SQL函数（类似于date_trunc）注释为Django ORM Queryset [英] Annotate Custom SQL Function (similar to date_trunc) to Django ORM Queryset

问题描述

我正在使用 timescaledb ，这基本上只是 postgres 的扩展。它带有一个名为 <$的SQL函数。 c $ c> time_bucket 。我想将此功能与ORM结合使用以生成查询，如下所示：

I am using timescaledb which is basically just an extension for postgres. It comes with a SQL function called time_bucket. I want to use this function in combination with the ORM to generate a query as follows:

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;

models.py ：

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)

---

到目前为止我的尝试：

---

My try so far:

class TimeBucket(Func):

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


(Measurement.objects
    .values('time')
    .annotate(tb=TimeBucket('time'))
    .annotate(s_desc=Avg('s0'))
    .filter(
        time__gte=datetime.fromtimestamp(start),
        time__lte=datetime.fromtimestamp(end))
    .order_by('tb')
)

结果：

SELECT
  "measurements"."time",
  time_bucket('1 minute', "measurements"."time") AS "tb",
  (AVG("measurements"."s0")) AS "s_desc"
FROM "measurements"
WHERE (
  "measurements"."time" <= 2447-10-02 14:17:01+00:00 AND 
  "measurements"."time" >= 1970-01-01 00:00:01+00:00
)
GROUP BY "measurements"."time", time_bucket('1 minute', "measurements"."time")
ORDER BY "tb" ASC

您看到还有两个丑点：

• 如何在 GROUP中使用别名 tb BY 而不是重复它？


• 我只需要查询 time_bucket 和 s0 。如何摆脱时间而又不中断查询？

• How could I use the alias tb in the GROUP BY instead of repeating it?
• I only need to query time_bucket and s0. How to get rid of time without breaking the query?

推荐答案

必须在 .values（）中使用截断的值注释（例如 .values（'tb'）

The truncated value annotation must be used in .values() (e.g. .values('tb')) before any aggregation function.

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

---

在Django 1.11中，可以使用更干燥的解决方案并合并行

---

In Django 1.11, it is possible to use a more DRY solution and combine the lines

.annotate(tb=TimeBucket('time'))
.values('tb')

至

.values(tb=TimeBucket('time'))

这篇关于将自定义SQL函数（类似于date_trunc）注释为Django ORM Queryset的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！