Django annotation+SUM如何获取所有条目

Question

我的型号

class Machine(models.Model):
machineName = models.CharField(verbose_name="Machine Name", max_length=20, blank=False, null=False)

class SalesReport(models.Model):
machine = models.ForeignKey(Machine, on_delete=models.CASCADE, null=False, blank=False)
deviceDate = models.CharField(max_length=200, null=True, blank=True)
serverDate = models.DateTimeField(auto_now_add=True)
totalPrice = models.FloatField()

我有3台机器，我想得到过去7天每台机器的总销售额。

我的查询是

from django.db.models import Sum, Value as V
from django.db.models.functions import Coalesce
SalesReport.objects.values("serverDate__date", "machine__machineName").annotate(
...                 sales=Coalesce(Sum("totalPrice"),V(0))).filter(
...                                                 serverDate__gte=week_start,
...                                                 serverDate__lte=week_end)

得出以下结果，

[{'serverDate__date': datetime.date(2020, 7, 22), 'machine__machineName': 'machine__1', 'sales': 15.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__1', 'sales': 145.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__2', 'sales': 270.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__3', 'sales': 255.0}]

我想得到的是

[{'serverDate__date': datetime.date(2020, 7, 22), 'machine__machineName': 'machine__1', 'sales': 15.0},
{'serverDate__date': datetime.date(2020, 7, 22), 'machine__machineName': 'machine__2', 'sales': 0.0},
{'serverDate__date': datetime.date(2020, 7, 22), 'machine__machineName': 'machine__3', 'sales': 0.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__1', 'sales': 145.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__2', 'sales': 270.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__3', 'sales': 255.0}]

我正试着用Coalesce做这件事，但我错了。

*我使用mysql作为数据库。特定于数据库的查询也可以。

Answer 1

由于这是一个更多的SQL问题，我添加了一个更具体的答案

SELECT m.machineName, s.price
FROM machine m LEFT OUTER JOIN (
SELECT machine_id id, sum(totalPrice) price
FROM salesreport
WHERE serverDate BETWEEN DATE_SUB(curdate(), INTERVAL 1 WEEK) and curdate()
GROUP BY by machine_id) s on m.id = s.id

如果您希望serverDate作为outout，则必须应用聚合函数(Max，Min(，因为它位于SalesReport表中。

这取决于serverDate代表什么。如果它是您购买机器的日期，那么它应该在机器表中，并且可以直接从机器表中选择(WHERE BETWEEN子句必须存在于子选择中，并且也适用于机器表(。如果它是salesDate，那么它必须在SalesReport中，并且您必须对它应用聚合函数。例如：一周内可能有7个日期。。。

SELECT m.machineName, s.MaxserverDate, s.price
FROM machine m LEFT OUTER JOIN (
SELECT machine_id id, max(serverDate) MaxserverDate, sum(totalPrice) price
FROM salesreport
WHERE serverDate BETWEEN DATE_SUB(curdate(), INTERVAL 1 WEEK) and curdate()
GROUP BY by machine_id) s on m.id = s.id

Answer 2

问题是你在某些日期没有任何销售。与其说是django-ORM问题，不如说是DB特有的问题。我建议在您的机器表上使用带有左外部联接的原始sql=>带上所有的机器，列出销售清单(如果有(。

machine = Machine.objects.raw('''
SELECT machine.id, machine.name, sales.sid FROM app_machinelist as machine
LEFT JOIN (select sales_id as sid
from app_sales
where profile_id = {0}) sales
ON sales.sid = machine.id
ORDER BY machine.name ASC
'''.format(myuser.id))

这个例子有效，但出于安全考虑，最好通过字典传递参数

machine = Machine.objects.raw(mysql, params)

何处

params = {'profile_id': pk, 'startdate': startdate, 'enddate': enddate}
mysql = '''
SELECT machine.id, machine.name, sales.sid FROM app_machinelist as machine
LEFT JOIN (select sales_id as sid
from app_sales
where profile_id = %(profile_id)s) sales
ON sales.sid = machine.id
ORDER BY machine.name ASC
'''