python - 基于 Django 中聚合结果的额外列

Question

我有一张表存储 KLine 信息，包括证券 ID、最大/最小价格和日期，我想计算特定时期内每种证券的 yield 。这是我的功能

def get_rising_rate(start, end):
    return models.KLine.objects.\
            filter(start_time__gt=start). \
            filter(start_time__lt=end).\
            values("security__id").\
            annotate(min_price=django_models.Min("min_price_in_cent"),
                      max_price=django_models.Max("max_price_in_cent")).\
            extra(select={"gain": "max_price_in_cent/min_price_in_cent"})
            order_by("gain")

但是我得到了以下错误:

django.db.utils.OperationalError: (1247, "Reference 'max_price' not supported (reference to group function)")

我可以像这样使用原始 SQL 进行查询

SELECT
`security_id`,
`min_price`,
`max_price`,
`max_price`/`min_price` AS gain
FROM(
SELECT
`security_id`, 
MIN(`min_price_in_cent`) AS `min_price`, 
MAX(`max_price_in_cent`) AS `max_price`
FROM `stocks_kline` 
WHERE `start_time` > '2014-12-31 16:00:00' AND `start_time` < '2015-12-31 16:00:00'
GROUP BY `security_id`
) AS A
ORDER BY gain DESC

但我想知道是否有更“django”的方式来完成它。我搜索了“django join queryset”、“django queryset as derived tables”，但找不到解决方案。

提前致谢。

Answer 1

你能试试这个吗:

    def get_rising_rate(start, end):
        return models.KLine.objects.\
                filter(start_time__gt=start). \
                filter(start_time__lt=end).\
                values("security_id").\
                annotate(min_price=django_models.Min("min_price_in_cent"),
                          max_price=django_models.Max("max_price_in_cent")).\
                extra(select={"gain": "max(max_price_in_cent)/min(min_price_in_cent)"})
                order_by("gain")