Python django.db.models.Avg() Examples

def handle(self, *args, **options):
        cutoff_date = datetime.date.today() - datetime.timedelta(days=(30 * MONTHS))
        counts = User.objects.filter(date_joined__gt=cutoff_date)
        counts = counts.annotate(follow_count=Count('following')).order_by('follow_count')

        avg = counts.aggregate(Avg('follow_count'))['follow_count__avg']

        print
        print 'Following counts for users who signed up in the last {} months'.format(MONTHS)
        print '----------------'
        print 'Average: {:.3} per user'.format(avg)

        try:
            std_dev = counts.aggregate(StdDev('follow_count'))['follow_count__stddev']
            print 'StdDev:  {:.3}'.format(std_dev)
        except DatabaseError:
            print "(can't get standard deviation with SQLite)"
        counts = counts.values_list('follow_count', flat=True)
        print 'Median: {}'.format(percentile(counts, 0.5))
        print 

def get(cls, as_dataframe: bool = True, **filter_kwargs):
        """
        Return grouped by method/name statistic with AVG time and N calls
        :param as_dataframe: bool - whether return pandas.dataframe or plain QuerySet
        :param filter_kwargs: positional arguments represents options for filter() qs method
        :return: pandas Dataframe OR QuerySet
        """
        # .filter(has_error=False)\
        qs = cls.objects\
            .values('method', 'path', 'name')\
            .annotate(calls=Count('id'),
                      errors=Count(Case(
                          When(has_error=True, then=1),
                          output_field=IntegerField(),
                      )),
                      avg_time=Avg('time'), max_time=Max('time'))\
            .filter(**filter_kwargs)
        qs = list(qs)
        qs.sort(key=lambda m: -m['calls'])
        if as_dataframe:
            return pd.DataFrame.from_records(qs, columns=['name', 'method',
                                                          'calls', 'errors',
                                                          'avg_time', 'max_time'])
        return qs 

def resolve_clubAttendance(self, info, **kwargs):
        start = kwargs.get('startDate')
        end = kwargs.get('endDate')
        logs = Log.objects.all()
        if start is not None:
            logs = logs.filter(date__gte=start)
        else:
            raise Exception('Start date required')
        if end is not None:
            logs = logs.filter(date__lte=end)
        else:
            end = date.today()
        data = {
            'logs': logs,
            'avgDuration': logs.aggregate(Avg('duration')),
            'start': start,
            'end': end
        }
        return data 

def get_item_report(qs, total_log_count, **kwargs):
    if qs.count() == 0:
        return None

    min_count = kwargs.get('min_count', max(1, int(MINIMUM_THRESHOLD * total_log_count)))

    results = list(
        qs.values(
            'item',
            name=F('item__name'),
            icon=F('item__icon'),
        ).annotate(
            count=Count('pk'),
            min=Min('quantity'),
            max=Max('quantity'),
            avg=Avg('quantity'),
            drop_chance=Cast(Count('pk'), FloatField()) / total_log_count * 100,
            qty_per_100=Cast(Sum('quantity'), FloatField()) / total_log_count * 100,
        ).filter(count__gt=min_count).order_by('-count')
    )

    return results 

def update_teams_score():
    """
    Update teams score
    """
    logger.debug("'accounts:tasks:update_teams_score' worker up")

    for team in Team.objects.all():
        team.avg_reputation = UserProfile.objects.filter(team=team).\
            aggregate(Avg('reputation'))['reputation__avg']
        team.avg_total_cash = UserProfile.objects.filter(team=team).\
            aggregate(Avg('total_cash'))['total_cash__avg']
        team.avg_portfolio_value = UserProfile.objects.filter(team=team).\
            aggregate(Avg('portfolio_value'))['portfolio_value__avg']
        team.avg_weekly_result = UserProfile.objects.filter(team=team).\
            aggregate(Avg('weekly_result'))['weekly_result__avg']
        team.avg_monthly_result = UserProfile.objects.filter(team=team).\
            aggregate(Avg('monthly_result'))['monthly_result__avg']
        team.save() 

def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        user = SocialProfile.objects.get(slug=self.kwargs['slug']).owner
        context['posts_list'] = Post.objects.filter(owner=user).order_by("-modified")
        context['reviews_list'] = Review.objects.filter(reviewee=user).order_by("-modified")
        if len(context['reviews_list']) > 0:
            average = context['reviews_list'].aggregate(Avg('score'))['score__avg']
        else:
            average = 0
        average_str = []
        val = 0.00
        while val < 5:
            if val + 1 <= average:
                average_str.append('f')
            elif val + 0.5 <= average:
                average_str.append('h')
            else:
                average_str.append('e')
            val += 1
        context['average_str'] = average_str
        context['average'] = average
        return context

# Update a profile. 

def recs_using_association_rules(request, user_id, take=6):
    events = Log.objects.filter(user_id=user_id)\
                        .order_by('created')\
                        .values_list('content_id', flat=True)\
                        .distinct()

    seeds = set(events[:20])

    rules = SeededRecs.objects.filter(source__in=seeds) \
        .exclude(target__in=seeds) \
        .values('target') \
        .annotate(confidence=Avg('confidence')) \
        .order_by('-confidence')

    recs = [{'id': '{0:07d}'.format(int(rule['target'])),
             'confidence': rule['confidence']} for rule in rules]

    print("recs from association rules: \n{}".format(recs[:take]))
    return JsonResponse(dict(data=list(recs[:take]))) 

def update_click_rate(self) -> float:
        qs = self.get_active_subscribers() \
            .exclude(last_sent=None) \
            .aggregate(Avg('click_rate'))
        self.click_rate = round(qs.get('click_rate__avg', 0.9), 4)
        self.save(update_fields=['click_rate'])
        return self.click_rate 

def average_rating(self):
        # all_ratings = map(lambda x: x.rating, self.reviews.all())
        # return np.mean(all_ratings)
        return self.reviews.aggregate(Avg('rating'))['rating__avg'] 

def get(self, request, subject=None):
        # subjects = Subject.objects.annotate(total_courses=Count('courses'))
        # courses = Course.objects.annotate(total_modules=Count('modules'))

        # if subject:
            # subject = get_object_or_404(Subject, slug=subject)
            # courses = courses.filter(subject=subject)
        subjects = cache.get('all_subjects')

        if not subjects:
            subjects = Subject.objects.annotate(total_courses=Count('courses'))
            cache.set('all_subjects', subjects)
        all_courses = Course.objects.annotate(total_modules=Count('modules', distinct=True)).annotate(total_reviews=Count('reviews', distinct=True)).annotate(average_rating=Avg(F('reviews__rating'), distinct=True))
        page = request.GET.get('page', 1)

        # subjects = Course.objects.annotate(total_modules=Count('courses'))
        # courses = Course.objects.annotate(total_modules=Count('modules'))

        if subject:
            subject = get_object_or_404(Subject, slug=subject)
            key = 'subject_{}_courses'.format(subject.id)
            courses = cache.get(key)
            if not courses:
                courses = all_courses.filter(subject=subject)
                cache.set(key, courses)
            paginator = Paginator(courses, 10)
        else:
            courses = cache.get('all_courses')
            if not courses:
                courses = all_courses
                cache.set('all_courses', courses)
            paginator = Paginator(courses, 10)

        try:
            courses = paginator.page(page)
        except PageNotAnInteger:
            courses = paginator.page(1)
        except EmptyPage:
            courses = paginator.page(paginator.num_pages)
        
        return self.render_to_response({'subjects': subjects, 'subject': subject, 'courses': courses}) 

def get_queryset(self):
        return super().get_queryset().annotate(
            favorite_avg=models.Avg('favorite_books__favorite_thing_id')
        ) 

def test_aggregation(self):
        """
        Raise NotImplementedError when aggregating on date/time fields (#19360).
        """
        for aggregate in (Sum, Avg, Variance, StdDev):
            with self.assertRaises(NotSupportedError):
                Item.objects.all().aggregate(aggregate('time'))
            with self.assertRaises(NotSupportedError):
                Item.objects.all().aggregate(aggregate('date'))
            with self.assertRaises(NotSupportedError):
                Item.objects.all().aggregate(aggregate('last_modified'))
            with self.assertRaises(NotSupportedError):
                Item.objects.all().aggregate(
                    **{'complex': aggregate('last_modified') + aggregate('last_modified')}
                ) 

def test_expression_wrapped_with_parentheses_on_postgresql(self):
        """
        The SQL for the Cast expression is wrapped with parentheses in case
        it's a complex expression.
        """
        list(Author.objects.annotate(cast_float=Cast(Avg('age'), models.FloatField())))
        self.assertIn('(AVG("db_functions_author"."age"))::double precision', connection.queries[-1]['sql']) 

def get_queryset(self):
        return super().get_queryset().annotate(
            favorite_avg=models.Avg('favorite_books__favorite_thing_id')
        ) 

def test_aggregation(self):
        """
        Raise NotImplementedError when aggregating on date/time fields (#19360).
        """
        for aggregate in (Sum, Avg, Variance, StdDev):
            with self.assertRaises(NotSupportedError):
                Item.objects.all().aggregate(aggregate('time'))
            with self.assertRaises(NotSupportedError):
                Item.objects.all().aggregate(aggregate('date'))
            with self.assertRaises(NotSupportedError):
                Item.objects.all().aggregate(aggregate('last_modified'))
            with self.assertRaises(NotSupportedError):
                Item.objects.all().aggregate(
                    **{'complex': aggregate('last_modified') + aggregate('last_modified')}
                ) 

def prepare_rating(self, obj):
        ct = ContentType.objects.get_for_model(obj)
        try:
            rating = OverallRating.objects.filter(
                object_id=obj.pk,
                content_type=ct
            ).aggregate(r=Avg("rating"))["r"]
            return float(str(rating or "0"))
        except OverallRating.DoesNotExist:
            return 0.0 

def domanda_formativa_raggio_medio(self):
        from formazione.models import Aspirante
        aspiranti = self.circonferenze_contenenti(Aspirante.query_contattabili())
        return int(aspiranti.aggregate(raggio=Avg('raggio'))['raggio']) or 0 

def update_stats(self):
        # Sum all stats on the runes
        stat_bonuses = {}
        runes = self.runes.all()
        for stat, _ in RuneInstance.STAT_CHOICES:
            if stat not in stat_bonuses:
                stat_bonuses[stat] = 0

            for rune in runes:
                stat_bonuses[stat] += rune.get_stat(stat)

        # Add in any active set bonuses
        for active_set in self.active_rune_sets:
            stat = RuneInstance.RUNE_SET_BONUSES[active_set]['stat']
            if stat:
                stat_bonuses[stat] += RuneInstance.RUNE_SET_BONUSES[active_set]['value']

        self.hp = stat_bonuses.get(base.Stats.STAT_HP, 0)
        self.hp_pct = stat_bonuses.get(base.Stats.STAT_HP_PCT, 0)
        self.attack = stat_bonuses.get(base.Stats.STAT_ATK, 0)
        self.attack_pct = stat_bonuses.get(base.Stats.STAT_ATK_PCT, 0)
        self.defense = stat_bonuses.get(base.Stats.STAT_DEF, 0)
        self.defense_pct = stat_bonuses.get(base.Stats.STAT_DEF_PCT, 0)
        self.speed = stat_bonuses.get(base.Stats.STAT_SPD, 0)
        self.crit_rate = stat_bonuses.get(base.Stats.STAT_CRIT_RATE_PCT, 0)
        self.crit_damage = stat_bonuses.get(base.Stats.STAT_CRIT_DMG_PCT, 0)
        self.resistance = stat_bonuses.get(base.Stats.STAT_RESIST_PCT, 0)
        self.accuracy = stat_bonuses.get(base.Stats.STAT_ACCURACY_PCT, 0)
        self.avg_efficiency = self.runes.aggregate(Avg('efficiency'))['efficiency__avg'] or 0.0 

def predict_score_by_ratings(item_id, movies):
        item = Rating.objects.filter(movie_id=item_id).values('movie_id').annotate(Avg('rating')).first()
        if not item:
            return 0

        return Decimal(item['rating__avg']) 

def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['posts_list'] = Post.objects.filter(owner=self.request.profile).order_by("-modified")
        context['reviews_list'] = Review.objects.filter(reviewee=self.request.profile).order_by("-modified")
        if len(context['reviews_list']) > 0:
            average = context['reviews_list'].aggregate(Avg('score'))['score__avg']
        else:
            average = 0
        average_str = []
        val = 0.00
        ''' Django HTML has a hard time looping through numerical values, so to display the score
        we convert it to a string, (5 stars is 'fffff', 0 is 'eeeee') and then iterate through the
        string to display stars on a seller's page. Hacky to be sure, but I wasn't able to find a
        more elegant alternative'''
        while val < 5:
            if val + 1 <= average:
                average_str.append('f')
            elif val + 0.5 <= average:
                average_str.append('h')
            else:
                average_str.append('e')
            val += 1
        context['average_str'] = average_str
        context['average'] = average
        return context

# Same as the above, but for viewing another seller. 

def _avg_time_spent_on_queries(self, filters):
        taken__aggregate = models.Request.objects.filter(*filters).annotate(time_spent=Sum('queries__time_taken')).aggregate(num=Avg('time_spent'))
        return taken__aggregate['num'] 

def _avg_num_queries(self, filters):
        queries__aggregate = models.Request.objects.filter(*filters).annotate(num_queries=Count('queries')).aggregate(num=Avg('num_queries'))
        return queries__aggregate['num'] 

def get_course_average_days_to_complete_for_time_period(site, start_date, end_date, course_id):
    filter_args = dict(
        site=site,
        date_for__gt=prev_day(start_date),
        date_for__lt=next_day(end_date),
        course_id=course_id
    )

    qs = CourseDailyMetrics.objects.filter(**filter_args)
    if qs:
        return int(math.ceil(
            qs.aggregate(average=Avg('average_days_to_complete'))['average']
        ))
    else:
        return 0 

def _avg_overall_time(self, filters):
        taken__aggregate = models.Request.objects.filter(*filters).annotate(time_spent=Sum('time_taken')).aggregate(num=Avg('time_spent'))
        return taken__aggregate['num']

    # TODO: Find a more efficient way to do this. Currently has to go to DB num. views + 1 times and is prob quite expensive 

def __init__(self, save_path='./models/bpr/'):
        self.save_path = save_path
        self.model_loaded = False
        self.avg = list(Rating.objects.all().aggregate(Avg('rating')).values())[0] 

def _avg_time_spent_on_queries(self, filters):
        taken__aggregate = models.Request.objects.filter(*filters).annotate(time_spent=Sum('queries__time_taken')).aggregate(num=Avg('time_spent'))
        return taken__aggregate['num'] 

def _avg_num_queries(self, filters):
        queries__aggregate = models.Request.objects.filter(*filters).annotate(num_queries=Count('queries')).aggregate(num=Avg('num_queries'))
        return queries__aggregate['num'] 

def score(self):
        return self.exclude(score=NA).aggregate(models.Avg('score'))['score__avg'] 

def prepare_rating(self, obj):
        ct = ContentType.objects.get_for_model(obj)
        try:
            rating = OverallRating.objects.filter(
                object_id=obj.pk,
                content_type=ct
            ).aggregate(r=Avg("rating"))["r"]
            return float(str(rating or "0"))
        except OverallRating.DoesNotExist:
            return 0.0 

def test_aggregation(self):
        """
        #19360: Raise NotImplementedError when aggregating on date/time fields.
        """
        for aggregate in (Sum, Avg, Variance, StdDev):
            with self.assertRaises(NotImplementedError):
                Item.objects.all().aggregate(aggregate('time'))
            with self.assertRaises(NotImplementedError):
                Item.objects.all().aggregate(aggregate('date'))
            with self.assertRaises(NotImplementedError):
                Item.objects.all().aggregate(aggregate('last_modified'))
            with self.assertRaises(NotImplementedError):
                Item.objects.all().aggregate(
                    **{'complex': aggregate('last_modified') + aggregate('last_modified')}
                )