python - Django Orm : Custom Select column with aggregate functions for grouped values

Question

我的 postgres 数据库表看起来(简化)如下:timeframe::timestamp, value::integer。

我有一个分组查询(分组时间范围:1 小时、1 天、一周等)并且我有不同的聚合函数。

一个示例查询在 sql 中如下所示:

SELECT
    date_trunc(%s, timeframe),
    SUM(CASE WHEN metric = 'visitors' THEN value ELSE 0 END) / NULLIF(SUM(CASE WHEN metric = 'total' THEN value ELSE 0 END), 0) as value
FROM aggregation_metrichour
GROUP BY date_trunc(%s, timeframe)

我可以使用原始查询，但我需要 orm 来动态过滤特定日期范围、一些其他信息并进行授权。

我的查询中唯一会改变的部分是值是计算机的方式，我在这里使用不同的东西，比如产品总和、平均值和总和。

我尝试用 orm 构建它，但失败了。

我想要做的是向我的 Django 查询集添加一个自定义选择列，其中包括一些带有聚合函数的奇特原始计算。对我来说，最好的方法是只使用一个字符串进行计算并将其添加到查询集中。

这是我到目前为止所做的:

带有默认聚合函数的简单

result = MetricHour.objects \
    .extra(select={'date': 'date_trunc(%s, timeframe)'}, select_params=[interval]) \
    .values('date') \
    .annotate(value=Sum('value'))

 print 'simple', result.query
 # simple SELECT (date_trunc(day, timeframe)) AS "date",
 # SUM("aggregation_metrichour"."value") AS "value" FROM
 # "aggregation_metrichour" GROUP BY (date_trunc(day, timeframe))

使用额外的选择

result = MetricHour.objects \
    .extra(select={'date': 'date_trunc(%s, timeframe)'}, select_params=[interval]) \
    .extra(select={'value': "SUM(CASE WHEN metric = 'visitors' THEN value ELSE 0 END) / NULLIF (SUM(value), 0)"}) \
    .values('date', 'value')

print 'extra-select', result.query # wont add a group by
# extra-select SELECT (date_trunc(day, timeframe)) AS "date",
# (SUM(CASE WHEN metric = 'visitors' THEN value ELSE 0 END) /
# NULLIF (SUM(value), 0)) AS "value" FROM "aggregation_metrichour"

自定义聚合函数

然后我找到this stackoverflow question关于如何编写自己的聚合函数。但是我认为 django 中的代码发生了变化。我现在需要在我不知道如何添加新自定义类型的 VisitorRate (models.Aggregate) 中将名称设置为字符串

class VisitorRateSql(models.sql.aggregates.Sum):
    sql_template = "SUM(CASE WHEN metric = visitors' THEN value ELSE 0 END) / NULLIF (SUM(value), 0)"

class VisitorRate(models.Sum):
    name = 'Sum'
    sql = VisitorRateSql
    def add_to_query(self, query, alias, col, source, is_summary):
            aggregate = VisitorRateSql(col,
                                       source=source,
                                       is_summary=is_summary,
                                       **self.extra)
            query.aggregates[alias] = aggregate


result = MetricHour.objects \
    .extra(select={'date': 'date_trunc(%s, timeframe)'}, select_params=[interval]) \
    .values('date') \
    .annotate(value=VisitorRate('value'))

print "Annotate Class", result.query
# Annotate Class SELECT (date_trunc(day, timeframe)) AS "date",
# SUM(CASE WHEN metric = visitors' THEN value ELSE 0 END) /
# NULLIF (SUM(value), 0) AS "value" FROM "aggregation_metrichour"
# GROUP BY (date_trunc(day, timeframe))

更新:我没有完全理解 models.Aggregate 函数这看起来好多了。但是我确实需要针对我的用例简化它。我想把sql_template直接给annotate函数

我想知道如何轻松地将新的自定义选择查询添加到我的分组查询中!感谢您的帮助!

Answer 1

我找到了一个很好的解决方案，它带有一个用作聚合函数的参数化类:

def custom_aggregation(select_query):
    class SqlAggregate(models.sql.aggregates.Aggregate):
        sql_function = ''
        sql_template = select_query

    class VisitorRate(models.Aggregate):
        sql = SqlAggregate
        def add_to_query(self, query, alias, col, source, is_summary):
            aggregate = self.sql(col,
                                 source=source,
                                 is_summary=is_summary,
                                 **self.extra)
            query.aggregates[alias] = aggregate

    return VisitorRate


    aggregate_query = "SUM(CASE WHEN metric = visitors' THEN value ELSE 0 END) / NULLIF (SUM(value), 0)"
    AggregationFunction = custom_aggregation(aggregate_query)

    result = MetricHour.objects \
        .extra(select={'date': 'date_trunc(%s, timeframe)'}, select_params=[interval]) \
        .values('date') \
        .annotate(value=AggregationFunction('value'))