如何通过Django ORM在数据库层面实现月度客房入住量统计？

问题背景 #

你的Reservation模型包含两个日期字段：

start_date = models.DateField()
end_date = models.DateField()

需要统计指定月份的总客房晚数（每天入住房间数之和），原Python循环方案因多次数据库查询速度过慢，以下提供两种数据库层面的高效实现方案。

方案一：计算每个预订在目标月份的贡献天数（推荐） #

核心思路是直接计算每个与目标月份有重叠的预订，在该月内实际占用房间的天数，最后求和。这种方式只需一次数据库查询，效率极高。

Django ORM实现代码 #

from django.db.models import F, ExpressionWrapper, IntegerField, Sum, Q
from django.db.models.functions import Greatest, Least
from datetime import date
from dateutil.relativedelta import relativedelta

# 目标月份
target_year = 2023
target_month = 9

# 计算目标月份的起始和结束日期
start_of_month = date(target_year, target_month, 1)
end_of_month = start_of_month + relativedelta(months=1) - relativedelta(days=1)

# 统计总客房晚数
total_nights = Reservation.objects.filter(
    # 筛选与目标月份有重叠的预订
    Q(start_date__lte=end_of_month) & Q(end_date__gte=start_of_month)
).annotate(
    # 计算预订与目标月份的重叠起始/结束日期
    overlap_start=Greatest(F('start_date'), start_of_month),
    overlap_end=Least(F('end_date'), end_of_month),
    # 计算该预订在目标月份的占用天数（end_date是退房日，当天不占用，所以用结束减起始的天数）
    nights=ExpressionWrapper(
        F('overlap_end') - F('overlap_start'),
        output_field=IntegerField()
    )
).aggregate(total=Sum('nights'))['total'] or 0

逻辑说明 #

1. 先筛选出所有和目标月份有时间重叠的预订，排除完全不相关的数据；
2. 对每个预订，计算它在目标月份内的实际占用区间（取预订开始和月初的最大值，预订结束和月末的最小值）；
3. 计算该区间的天数（即贡献的客房晚数），最后将所有预订的贡献值求和。

方案二：生成日期序列并统计每日入住数 #

如果更倾向于按日期统计再求和的思路，可以利用数据库的日期生成功能（不同数据库语法略有差异），在数据库内生成目标月份的所有日期，关联预订表统计每日入住数后求和。

PostgreSQL原生SQL实现（Django调用） #

from django.db import connection
from datetime import date
from dateutil.relativedelta import relativedelta

target_year = 2023
target_month = 9

start_of_month = date(target_year, target_month, 1)
end_of_month = start_of_month + relativedelta(months=1) - relativedelta(days=1)

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT SUM(daily_occupied)
        FROM (
            SELECT COUNT(r.id) AS daily_occupied
            FROM generate_series(%s::date, %s::date, '1 day'::interval) AS dates(day)
            LEFT JOIN reservation r ON r.start_date <= dates.day AND r.end_date > dates.day
            GROUP BY dates.day
        ) AS daily_stats
    """, [start_of_month, end_of_month])
    total_nights = cursor.fetchone()[0] or 0

其他数据库适配提示 #

• MySQL/MariaDB：可以用递归CTE生成日期序列替代generate_series；
• SQLite：需要借助递归查询或自定义函数生成日期范围。

原方案慢的原因 #

你之前的Python循环会对目标月份的每一天发起一次数据库查询（最多31次），每次查询都要重新过滤数据，大量的IO交互导致速度变慢。而上述两种方案都只需一次数据库请求，所有计算逻辑在数据库内完成，大幅提升效率。

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