Django ORM能否单条数据库查询完成跨时段AQI均值差计算

这个需求完全可以在单次数据库查询内完成，不需要拆分两次查询后在Python层计算差值。

原代码问题说明 #

• 你最初编写的筛选逻辑存在逻辑错误：&是逻辑与操作，单条记录的date字段不可能同时落在2021年4月和2022年4月两个区间，该查询永远返回空结果。
• 你目前使用的两次独立aggregate查询方案会触发两次数据库请求，表数据量较大时会产生不必要的性能开销。

单次查询实现代码 #

Django ORM支持在单次聚合调用中传入多个带独立筛选条件的聚合字段，同时可以通过F()表达式直接在数据库端完成聚合结果的算术运算，全程仅触发一次SQL查询：

import datetime
from django.db.models import Avg, Q, F
from django.db.models.functions import Round

# 直接获取2022年4月与2021年4月的AQI均值差，保留2位小数
aqi_diff = AirQuality.objects.aggregate(
    avg_202104=Avg(
        'aqi',
        filter=Q(date__range=(datetime.date(2021, 4, 1), datetime.date(2021, 4, 30)))
    ),
    avg_202204=Avg(
        'aqi',
        filter=Q(date__range=(datetime.date(2022, 4, 1), datetime.date(2022, 4, 30)))
    ),
    diff=Round(F('avg_202204') - F('avg_202104'), 2)
)['diff']

扩展场景 #

如果需要按州维度分别计算两个时段的AQI均值差，只需要将aggregate替换为annotate，配合values()分组即可，同样是单次查询完成：

state_level_diff = AirQuality.objects.values('state_name').annotate(
    avg_202104=Avg(
        'aqi',
        filter=Q(date__range=(datetime.date(2021, 4, 1), datetime.date(2021, 4, 30)))
    ),
    avg_202204=Avg(
        'aqi',
        filter=Q(date__range=(datetime.date(2022, 4, 1), datetime.date(2022, 4, 30)))
    ),
    diff=Round(F('avg_202204') - F('avg_202104'), 2)
)

注：该写法兼容SQLite、MySQL、PostgreSQL等所有Django官方支持的数据库，ORM会自动生成对应数据库的原生SQL语句，不需要手动编写原生SQL。

内容的提问来源于stack exchange，提问作者finethen