Django ORM 按出生日期分组

Question

我一直在努力寻找一个好的方法，来使用Django的ORM按日期范围进行分组。我有一个用户模型，其中有一个出生日期的字段，我想把用户按年龄段分组，比如10到15岁、15到20岁等等，并返回每个年龄段的人数。

我的模型：

class Reader(models.Model):
    user = models.OneToOneField(User)
    birth_date = models.DateField()

我的原始SQL查询看起来是这样的：

 SELECT count(*) as total,
   CASE
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) <= 10 THEN '1-10'
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) <= 15 THEN '11-15'
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) <= 20 THEN '16-20'
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) <= 25 THEN '21-25'
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) <= 30 THEN '26-30'
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) <= 40 THEN '31-40'
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) <= 50 THEN '41-50'
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) <= 60 THEN '51-60'
     WHEN EXTRACT(year from AGE(NOW(), birth_date)) > 60 THEN '60+'
   END as age
   FROM main_reader AS reader
   GROUP BY age

Answer 1

也许可以试试这样做（没有测试过）：

case_when_query = "(case when extract...end)" # Your case when query here

extra_qs = Reader.objects.extra(select={'count': 'count(1)', 'age': case_when_query})

query_set = extra_qs.values('count', 'age')
query_set.query.group_by = ['age']

Answer 2

这可能对未来寻找答案的人有帮助。

现在我们可以通过条件表达式来解决这个问题，具体来说是Case()、When()和聚合函数Count()。

在这里，我还使用了relativedelta来计算日期。

下面是一个例子：

from django.utils import timezone
from django.db.models import Count, Case, When
from dateutil.relativedelta import relativedelta


current_date = timezone.now().date()

range_ages = (
    {"lookup": "gte", "label": "-17", "age": [18]},
    {"lookup": "range", "label": "18-24", "age": [18, 25]},
    {"lookup": "range", "label": "25-34", "age": [25, 35]},
    {"lookup": "range", "label": "35-44", "age": [35, 45]},
    {"lookup": "range", "label": "45-54", "age": [45, 55]},
    {"lookup": "range", "label": "55-64", "age": [55, 65]},
    {"lookup": "lt", "label": "65+", "age": [65]},
)

aggr_query = {}
for item in range_ages:
    age = item.get("age")
    lookup = item.get("lookup")
    label = item.get("label")
    # calculate start_date an end_date
    end_date = current_date - relativedelta(years=age[0])
    start_date = current_date - relativedelta(years=age[-1], days=-1)
    f_value = start_date if len(age) == 1 else (start_date, end_date)
    if lookup == "gte":
        aggr_query[label]=Count(Case(When(date_of_birth__gte=f_value, then=1)))
    elif lookup == "lt":
        aggr_query[label]=Count(Case(When(date_of_birth__lt=f_value, then=1)))
    else:
        aggr_query[label]=Count(Case(When(date_of_birth__range=f_value, then=1)))

#Aggregate values
qs_values = MyModel.objects.filter(gender=gender).aggregate(**aggr_query)

输出结果会是这样的：

{'55-64': 1726, '25-34': 2590, '65+': 5691, '18-24': 517, '45-54': 1209, '-17': 0, '35-44': 2416}

我们还可以使用annotate()，它会返回一个查询集的对象。