Python django.db.models.aggregates.Avg() Examples

def test_avg_salary_department(self):
        qs = Employee.objects.annotate(avg_salary=Window(
            expression=Avg('salary'),
            order_by=F('department').asc(),
            partition_by='department',
        )).order_by('department', '-salary', 'name')
        self.assertQuerysetEqual(qs, [
            ('Adams', 50000, 'Accounting', 44250.00),
            ('Jenson', 45000, 'Accounting', 44250.00),
            ('Jones', 45000, 'Accounting', 44250.00),
            ('Williams', 37000, 'Accounting', 44250.00),
            ('Wilkinson', 60000, 'IT', 47000.00),
            ('Moore', 34000, 'IT', 47000.00),
            ('Miller', 100000, 'Management', 90000.00),
            ('Johnson', 80000, 'Management', 90000.00),
            ('Johnson', 40000, 'Marketing', 39000.00),
            ('Smith', 38000, 'Marketing', 39000.00),
            ('Smith', 55000, 'Sales', 54000.00),
            ('Brown', 53000, 'Sales', 54000.00),
        ], transform=lambda row: (row.name, row.salary, row.department, row.avg_salary)) 

def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                    if isinstance(output_field, bad_fields):
                        raise NotImplementedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        )
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass 

def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                    if isinstance(output_field, bad_fields):
                        raise NotImplementedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        )
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass 

def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                    if isinstance(output_field, bad_fields):
                        raise NotImplementedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        )
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass 

def device_average_click_conversion_rate_for_period(self, start, finish):
            return self.within_period(start, finish).values('device').annotate(click_conversion_rate=Avg('click_conversion_rate')) 

def test_window_repr(self):
        self.assertEqual(
            repr(Window(expression=Sum('salary'), partition_by='department')),
            '<Window: Sum(F(salary)) OVER (PARTITION BY F(department))>'
        )
        self.assertEqual(
            repr(Window(expression=Avg('salary'), order_by=F('department').asc())),
            '<Window: Avg(F(salary)) OVER (ORDER BY OrderBy(F(department), descending=False))>'
        ) 

def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass
                else:
                    if isinstance(output_field, bad_fields):
                        raise utils.NotSupportedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        ) 

def top_by_conversion_rate(self, start, finish):
            return self.filter(metrics__day__gte=start, metrics__day__lte=finish) \
                       .annotate(conversions=Sum('metrics__conversions'),
                                 conv_rate=Avg('metrics__conv_rate'),
                                 cost_conv=Avg('metrics__cost_conv'),
                                 impressions=Sum('metrics__impressions'),
                                 clicks=Sum('metrics__clicks'),
                                 cost=Sum('metrics__cost'),
                                 ctr=Avg('metrics__ctr'),
                                 avg_cpc=Avg('metrics__avg_cpc')) \
                       .order_by('-conversions') 

def top_by_clicks(self, start, finish):
            return self.filter(metrics__day__gte=start, metrics__day__lte=finish) \
                       .annotate(clicks=Sum('metrics__clicks'),
                                 impressions=Sum('metrics__impressions'),
                                 ctr=Avg('metrics__ctr'),
                                 cost=Sum('metrics__cost'),
                                 avg_position=Avg('metrics__avg_position')) \
                       .order_by('-clicks') 

def top_by_clicks(self, start, finish):
            return self.filter(metrics__day__gte=start, metrics__day__lte=finish) \
                .annotate(clicks=Sum('metrics__clicks'),
                          impressions=Sum('metrics__impressions'),
                          ctr=Avg('metrics__ctr'),
                          cost=Sum('metrics__cost'),
                          avg_position=Avg('metrics__avg_position')) \
                .order_by('-clicks') 

def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            try:
                output_field = expression.input_field.output_field
                if isinstance(output_field, bad_fields):
                    raise NotImplementedError(
                        'You cannot use Sum, Avg, StdDev and Variance aggregations '
                        'on date/time fields in sqlite3 '
                        'since date/time is saved as text.')
            except FieldError:
                # not every sub-expression has an output_field which is fine to
                # ignore
                pass 

def average_search_lost_impression_share_budget(self, start, finish):
            return self.within_period(start, finish).aggregate(Avg('search_lost_is_budget')) 

def daily_average_cost_conv_for_period(self, start, finish, order_by='day'):
            return self.within_period(start, finish).order_by(order_by).values('day').annotate(cost_conv=Avg('cost_conv')) 

def average_cost_conv_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Avg('cost_conv')) 

def average_click_conversion_rate_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Avg('click_conversion_rate')) 

def daily_average_cpc_for_period(self, start, finish, order_by='day'):
            return self.within_period(start, finish).order_by(order_by).values('day').annotate(cpc=Avg('avg_cpc')) 

def average_cpc_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Avg('avg_cpc')) 

def daily_average_ctr_for_period(self, start, finish, order_by='day'):
            return self.within_period(start, finish).order_by(order_by).values('day').annotate(ctr=Avg('ctr')) 

def average_ctr_for_period(self, start, finish):
            return self.within_period(start, finish).aggregate(Avg('ctr')) 

def check_expression_support(self, expression):
        bad_fields = (fields.DateField, fields.DateTimeField, fields.TimeField)
        bad_aggregates = (aggregates.Sum, aggregates.Avg, aggregates.Variance, aggregates.StdDev)
        if isinstance(expression, bad_aggregates):
            for expr in expression.get_source_expressions():
                try:
                    output_field = expr.output_field
                except FieldError:
                    # Not every subexpression has an output_field which is fine
                    # to ignore.
                    pass
                else:
                    if isinstance(output_field, bad_fields):
                        raise NotImplementedError(
                            'You cannot use Sum, Avg, StdDev, and Variance '
                            'aggregations on date/time fields in sqlite3 '
                            'since date/time is saved as text.'
                        )