Classify by Average after Sum

Using Django
1

Hi,

I’m trying to create a query that will find how much each customer spend in purchases, and then classify the customers by their average purchases sum.

annotate return an error and aggregate return a dictionary.
My initial query was:

c=Customer.objects.annotate(
    total_sum=Sum(F('salesorder__salesorderitems__quantity')*F('salesorder__salesorderitems__prod_id__unit_price'), output_field=FloatField()))\
    .values('id','fname','lname','total_sum').order_by('-total_sum')\
    .annotate(level=Case(When(Q(total_sum__gt=Avg('total_sum')) ,then="good"),\
                          When(Q(total_sum__lte=Avg('total_sum')), then="bad"),\
                          default="None"))

I think I can do it with two DB hits:
One to calculate the Average and one to do the classification, but trying to avoid two DB hits.

Can someone clarify how to achieve my goal ?

2

I think you need to do the aggregation functions (e.g. Avg) within a subquery.

See the docs and example in Using aggregates within a Subquery expression. I think this is pretty close to what you’re trying to do.

1 Like
3

I think I figure it out:

customers = Customer.objects.filter(id=OuterRef('pk'))

customers_total = customers.annotate(total_sum=Sum(F('salesorder__salesorderitems__quantity')*F('salesorder__salesorderitems__prod_id__unit_price'), output_field=FloatField())).values('total_sum')

c=Customer.objects.annotate(
    total_sum=Sum(F('salesorder__salesorderitems__quantity')*F('salesorder__salesorderitems__prod_id__unit_price'), output_field=FloatField()))\
    .values('id','fname','lname','total_sum').order_by('-total_sum')\
    .annotate(level=Case(When(Q(total_sum__gt=Avg(Subquery(customers_total))) ,then=Value("prefered client")),\
                          When(Q(total_sum__lte=Avg(Subquery(customers_total))), then=Value("regular client")),\
                          default=Value("None"), output_field=CharField()))

Not really sure, but connection.queries shows only one db hit.

1 Like