mysql - 获取每个月最后一条记录的值(Django)

Question

我的模型:

class Transaction (models.Model):
    transaction_id = models.AutoField(primary_key=True)
    net_monthly_transaction = models.DecimalField(max_digits = 10, decimal_places = 2, default=0)
    # deposit or withdrawal (withdrawal with negative value)
    amount = models.DecimalField(max_digits = 10, decimal_places = 2)
    time_stamp = models.DateTimeField(default=datetime.now, blank=True)


    def __str__(self):              # __unicode__ on Python 2
        return str(self.time_stamp)  + str(self.amount) + str(self.net_monthly_transaction)

我的目标是从每个月的最后一个条目中获取 net_monthly_transaction 的值。

在 S.O. 的帮助下我已经设法做到这一点:

truncate_date = connection.ops.date_trunc_sql('month', 'time_stamp')
        lem = Transaction.objects.extra({'month':truncate_date}).values('month').annotate(last_record=Max('time_stamp')).values_list('net_monthly_transaction', flat=True)

上面的查询假设是从每个月的max time_stamp 中获取net_monthly_transaction 的值。

但事实并非如此。

如果我一个接一个地为 10 月创建三个条目:

1. net_monthly_transaction = 3000
2. net_monthly_transaction = 4000
3. net_monthly_transaction = 5000

查询将返回所有 3 个值。

另一方面:

1. net_monthly_transaction = 3000
2. net_monthly_transaction = 2000
3. net_monthly_transaction = 1000

然后只返回值 3000。

所以在某处根据 net_monthly_transaction 的大小设置了一个条件。我不知道如何解决这个问题。

有人可以提供一些指导吗。

提前致谢。

Answer 1

我会使用两个查询集来解决这个问题(除非可以选择下面更简单的方法)。只要您不显式评估 last_entries，这将在您评估 transactions 时产生单个查询。

from django.db.models import Max
from django.db.models.functions import TruncMonth

# Selects last time_stamp for each month
last_entries = (Transaction.objects
    .annotate(tx_month=TruncMonth('time_stamp'))
    .values('tx_month')
    .annotate(last_entry=Max('time_stamp'))
    .values_list('last_entry', flat=True))

# Selects transactions with time_stamps matching last_entries
# ie. last transaction in each month
transactions = Transaction.objects.filter(
    time_stamp__in=last_entries
)

transactions 是一个普通查询集，包含每个月的最后一个 Transaction 实例。如果您想要一个简单的 net_monthly_transaction 值列表，没有其他信息，您可以添加:

net_values = transactions.values_list(
    'net_monthly_transaction', flat=True
)

真正要小心的一件事是，如果两个条目具有相同的时间戳，那么它们都会出现在结果集中。

更简单的方法

如果 net_monthly_transaction 只是给定月份所有 amount 的总和，那么您可以使用类似这样的东西代替上面的东西

from django.db.models import Sum
from django.db.models.functions import TruncMonth

transactions = (Transaction.objects
    .annotate(month=TruncMonth('time_stamp'))
    .values('month')
    .annotate(month_net=Sum('amount')))

现在 transactions 包含代表每个月最后一笔交易的字典。每个字典都有一个包含月份的 month 键和一个包含当月净交易的 month_net 键。作为奖励，您不必担心具有相同时间戳的条目。

当然，如果 net_monthly_transaction 是更复杂计算的结果，那么这可能不是一个选项。

您的初始方法

您的初始查询无效有两个主要原因。

1. 这种查询通常依赖于将表连接到自身或 WHERE 子句中的子查询。我不知道有什么好方法可以使用 Django 的 ORM 使用单个查询集来执行这些操作，除非您诉诸于在 extra() 中填充原始 SQL 或类似的东西。但是，如果您按照我们上面的方式使用两个查询集，则生成 WHERE 子句子查询是微不足道的。

2. 这样使用values_list()是没有意义的

   truncate_date = connection.ops.date_trunc_sql('month', 'time_stamp')
   lem = Transaction.objects.extra({'month':truncate_date}).values('month').annotate(last_record=Max('time_stamp'))
   

   到目前为止，它类似于上面使用的 last_entries 查询集。我们正在为每个月份选择唯一的月份值和最后一个 time_stamp。

   当我们添加 .values_list('net_monthly_transaction', flat=True) 时，我们告诉查询构建器我们只关心 net_monthly_transaction 字段，因此它抛出去掉其他所有东西并产生这样的东西

   SELECT "transaction"."net_monthly_transaction"
   FROM "transaction"
   GROUP BY "transaction"."net_monthly_transaction"
   

   GROUP BY 是 extra 和 annotate 调用中唯一剩下的东西，甚至它也被改变了，所以它不会不要做我们想让它做的事。