问有没有办法查询出自生日的人口统计数据？
EN

Stack Overflow用户

提问于 2019-09-28 06:36:46

回答 1查看 61关注 0票数 0

我正在尝试生成人口统计数据。我做了所有单独的查询，因为我想不出一次做它们的方法。

from django.utils import timezone
from dateutil.relativedelta import relativedelta # $ pip install python-dateutil

teenagers_count = queryset.filter(birthday__lte=now-relativedelta(years=10), birthday__gt=now-relativedelta(years=20)).count()
twenties_count = queryset.filter(birthday__lte=now-relativedelta(years=20), birthday__gt=now-relativedelta(years=30)).count()
thirties_count = queryset.filter(birthday__lte=now-relativedelta(years=30), birthday__gt=now-relativedelta(years=40)).count()
forties_count = queryset.filter(birthday__lte=now-relativedelta(years=40), birthday__gt=now-relativedelta(years=50)).count()
fifties_and_older_count = queryset.filter(birthday__lte=now-relativedelta(years=50)).count()

有没有办法只用一个查询就能做到这一点呢？

django

django-queryset

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-09-28 07:33:34

首先，使用以下命令将您的年龄标注到您的查询集：

age = ExpressionWrapper(datetime.now() - F('created_at'), output_field=fields.DurationField())
queryset.annotate(age=age) #=> Will add "age" on each records

第二，用例/何时在age_range字段上构建您自己的逻辑

queryset.annotate(age=age).annotate(
            age_range=Case(
                When(age__gte=datetime.timedelta(years=10), age__lt=datetime.timedelta(years=20), then=Value('teens')),
                When(age__gte=datetime.timedelta(years=20), age__lt=datetime.timedelta(years=30), then=Value('twenties')),
                When(age__gte=datetime.timedelta(years=30), age__lt=datetime.timedelta(years=40), then=Value('thirties')),
                When(age__gte=datetime.timedelta(years=40), age__lt=datetime.timedelta(years=50), then=Value('fourties')),
                When(age__gte=datetime.timedelta(years=50), then=Value('fifties')),
                default=Value('Unknow'),
                output_field=fields.CharField(),
        )) #=> This will add "age_range" field on your queryset

第三，只要按"age_range“聚合即可。总而言之：

import datetime 
from django.db.models import Case, When, Value, F, Count, ExpressionWrapper, fields

age = ExpressionWrapper(datetime.now() - F('created_at'), output_field=fields.DurationField())
counts = queryset.annotate(age=age).annotate(
            age_range=Case(
                When(age__gte=datetime.timedelta(years=10), age__lt=datetime.timedelta(years=20), then=Value('teens')),
                When(age__gte=datetime.timedelta(years=20), age__lt=datetime.timedelta(years=30), then=Value('twenties')),
                When(age__gte=datetime.timedelta(years=30), age__lt=datetime.timedelta(years=40), then=Value('thirties')),
                When(age__gte=datetime.timedelta(years=40), age__lt=datetime.timedelta(years=50), then=Value('fourties')),
                When(age__gte=datetime.timedelta(years=50), then=Value('fifties')),
                default=Value('Unknow'),
                output_field=fields.CharField(),
        )).order_by('age_range').values('age_range').annotate(count=Count('age_range'))