django orm 的order_by 和聚合同时使用时的注意事项

[kagxin]

from anxin.trade.models import *
from django.db.models import *

q1 = DeviceBill.objects.values('pay_type').annotate(total=Sum('money'))
print(q1.query.__str__())
q2 = DeviceBill.objects.order_by('-created_at').values('pay_type').annotate(total=Sum('money'))
print(q2.query.__str__())
q3 = DeviceBill.objects.values('pay_type').annotate(total=Sum('money')).order_by('-created_at')
print(q3.query.__str__())

SELECT "trade_devicebill"."pay_type", SUM("trade_devicebill"."money") AS "total" FROM "trade_devicebill" GROUP BY "trade_devicebill"."pay_type"
SELECT "trade_devicebill"."pay_type", SUM("trade_devicebill"."money") AS "total" FROM "trade_devicebill" GROUP BY "trade_devicebill"."pay_type", "trade_devicebill"."created_at" ORDER BY "trade_devicebill"."created_at" DESC
SELECT "trade_devicebill"."pay_type", SUM("trade_devicebill"."money") AS "total" FROM "trade_devicebill" GROUP BY "trade_devicebill"."pay_type", "trade_devicebill"."created_at" ORDER BY "trade_devicebill"."created_at" DESC

DeviceBill model 为

class DeviceBill(models.Model):
    PAY_TYPE = (
        (0, "支付宝"),
        (1, "微信"),
        (2, "银联"),
        (3, "余额"),
        (4, "web充值")
    )

    bill_id = models.UUIDField(default=get_uuid)
    money = models.DecimalField(max_digits=10, decimal_places=2)
    pay_type = models.SmallIntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True, db_index=True)

q1 的group by 为 pay_type

DeviceBill.objects.values('pay_type').annotate(total=Sum('money'))

SELECT "trade_devicebill"."pay_type", SUM("trade_devicebill"."money") AS "total" 
    FROM "trade_devicebill" 
    GROUP BY "trade_devicebill"."pay_type"

q2和q3 的group by 为 pay_type，created_at

DeviceBill.objects.order_by('-created_at').values('pay_type').annotate(total=Sum('money'))
DeviceBill.objects.values('pay_type').annotate(total=Sum('money')).order_by('-created_at')

SELECT "trade_devicebill"."pay_type", SUM("trade_devicebill"."money") AS "total" 
    FROM "trade_devicebill" 
    GROUP BY "trade_devicebill"."pay_type", "trade_devicebill"."created_at" 
    ORDER BY "trade_devicebill"."created_at" DESC

在sql中order by字段是必须在group by字段中的

如果涉及到分组(聚合)和排序的话，可以分开进行。因为在数据集不变的情况下，排序对分组(聚合)的结果没有影响。