如果月份在Django中的两个字段的日期范围内，则按月份对数据进行分组

Question

我有一个包含开始和结束日期时间字段的合同模型。我想在图表中显示每月有多少合同处于活动状态（该月介于开始时间和结束时间之间）

如果每月没有多个数据库请求，如何获取此信息

我可以像这样为每个字段添加注释

start_month_contracts = contracts.annotate(
            start_month=TruncMonth("start")
        ) \
            .values("start_month") \
            .annotate(count=Count("start_month"))

end_month_contracts = contracts.annotate(
            end_month=TruncMonth("end")
        ) \
            .values("end_month") \
            .annotate(count=Count("end_month"))

但如何将两者结合起来，以获得每月的有效合同

Answer 1

假设您有以下带有开始和结束日期的模型：

class Contract(models.Model):
    ...
    start = models.DateTimeField()
    end = models.DateTimeField()

每月“活动”合同的基本查询

基本公式如您所述：

the month is between start and end time

一个查询可以得到任何给定月份的数据

# Get active contracts for December 2020
month = datetime.datetime(2020, 12, 1)

# all Contract records active in december
qs = Contract.objects.filter(start__lte=month, end__gte=month)
# Or, since we just care about the count, we can use `.count()` instead:
december_active_count = Contract.objects.filter(start__lte=month, end__gte=month).count()

如果您发现需要调整基本查询，那没关系。与其说是查询，不如说是方法，它们进行了解释，而不管查询是什么

单个查询中的多个计数

有几种方法可以进行一次查询并制定出合同

django应用程序中的计数记录

一种简单而天真的方法是，首先在一个查询中提取所有相关的合同，然后在Python中每月对其进行计数

这很好，但存在一些潜在问题：

1. DB将发送每个记录的数据。如果您有许多记录，那么数据库需要发送的字节数可能会过多

2. 虽然这里的计算相当轻量级，但Python确实需要一些CPU能力来处理每个记录的这些数字，如果有很多记录，可能需要一段时间

真的，我们可能想让DB帮我们计算

依靠数据库

如果希望在数据库上而不是在Python中处理此问题，可以使用.aggregate开发一个查询来在数据库端进行聚合。这里的好处是，DB只需要传输计数，而不是所有记录，这是一个非常小的字节数。它还将一些数字处理从应用程序转移到数据库

扩展第一个示例，让我们尝试在单个查询中获取超过1个月的计数。我们通过使用aggregate和Count聚合函数来实现这一点

from django.db.models import Count, Q

november = datetime.datetime(2020, 11, 1)
december = datetime.datetime(2020, 12, 1)

contract_counts = Contract.objects.aggregate(
    november_counts=Count('pk', filter=Q(start__lte=november, end__gte=november))
    december_counts=Count('pk', filter=Q(start__lte=december, end__gte=december))
)
print(contract_counts)
{'november_counts': 376, 'december_counts': 393}  # <  output

我们可以应用同样的原理来获得指定时间范围内所有月份的计数。为了做到这一点，我们预先确定将被计数的start和end之间的每个月，并为每个月使用Case和Count

实际上，现在只需生成如上所述的关键字参数，但是是动态的

我还将为此模型创建一个自定义管理器，以便使界面更美观一些

import calendar
from django.db.models import Count, Q
class ContractManager(models.Manager):
    def month_counts(self, start, end):
        qs = self.get_queryset()
        # generate keyword arguments for .aggregate
        aggregations = {}
        for month in months(start, end):  # the start of each month in the range
            month_name = calendar.month_name[month.month]
            aggregation_name = f'{month_name}_{month.year}'
            aggregations[aggregation_name] = Count(
                'pk', filter=Q(start__lte=month, end__gte=month)
            )
        return qs.aggregate(**aggregations)


class Contract(models.Model):
    start = models.DateTimeField()
    end = models.DateTimeField()
    objects = ContractManager()

然后，您可以按如下方式生成计数：

start = datetime(2020, 1, 1)
end = datetime(2021, 1, 1)
print(Contract.objects.month_counts(start, end))

从中收集的输出可能如下所示：

{'January_2020': 2,
 'February_2020': 90,
 'March_2020': 163,
 'April_2020': 234,
 'May_2020': 272,
 'June_2020': 284,
 'July_2020': 284,
 'August_2020': 275,
 'September_2020': 247,
 'October_2020': 205,
 'November_2020': 128,
 'December_2020': 68,
 'January_2021': 3}

您还可以看到仅使用了1个查询：

from django.db import connection
print(len(connection.queries))
# 1

最后的想法和笔记

我应该提到的是，这并不是最有效的方法，还有很大的优化空间。如果需要，也可以在DB端生成月份间隔，而不是在Python中。特定的后端也可能有更多的性能选项，比如Postgres的daterange函数。尽管如此，我们这里的内容应该提供足够的上下文，以便使用aggregate获得所需的计数

I can annotate it for each field like this

我不认为你的代码能让你得到你真正想要的计数。您正在计算特定月份开始或结束的合同数。。。但这无法告诉您在任何一个给定的月份有多少合同处于活动状态

附言

为了简洁起见，我省略了上面的months()函数的代码。如果您感兴趣，可以在here找到代码。像熊猫这样的东西可能更具表现力，但这不应该成为一个问题，除非你的时间间隔超过数千年：-）