时间戳

首页 > 解决方案 > 有没有办法查询来自生日的人口统计数据？

django - 有没有办法查询来自生日的人口统计数据？

问题描述

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

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()

有没有办法通过一个查询来做到这一点？

标签： djangodjango-queryset

解决方案

首先，将您的年龄注释到您的查询集，使用：

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

二、使用Case/When来组成自己的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'))

推荐阅读