将自定义 SQL 函数（类似于 date_trunc）注释为 Django ORM 查询集

我在用timescaledb这基本上只是一个扩展postgres。它带有一个名为的 SQL 函数time_bucket https://docs.timescale.com/latest/api/api-timescaledb#time_bucket-a-id-time_bucket-a-。我想将此函数与 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;

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)

到目前为止我的尝试：

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

正如你所看到的，还剩下两个难看的地方：

• 我如何使用别名tb in the GROUP BY而不是重复它？
• 我只需要查询time_bucket and s0。如何摆脱time不破坏查询？

The 未截断的 column time可以使用only在过滤器中before首先annotate(...)再也不会了。

截断时间注释必须用于.values() (e.g. .values('tb')) 在任何聚合函数之前。

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

关于 GROUP BY 中的别名 https://stackoverflow.com/a/3841804/448474：别名一般只能在所有数据库中的 ORDER BY 中使用，但只有特定数据库允许在 GROUP BY 或 WHERE 中使用（仅 MySQL 和 Postgresql）。这可能是因为 GROUP BY 和 WHERE 子句在 SELECT 子句之前计算。这可以通过子查询绕过，但根本没有用。我确信每个现代数据库驱动程序的标准查询计划优化器都会重用 GROUP BY 中的辅助表达式，并且它从不重复评估该函数。如果 SQL 是手动编写并由人类读取的，则别名很有用，但仅对于某些后端在 Django ORM 编译器中通过别名实现它是没有用的。

编辑：此解决方案适用于 Django >= 1.11 并在 Django 3.0 中进行了测试。

（关于 Django 版本的信息不正确。我不记得 Django