Django Aggregate Min Max Dynamic Ranges

user_14689289 0
Viewed 165

I have following model:

class Claim:
      amount = models.PositiveIntegerField()

I am trying to create API that dynamically sends response in such way that amount ranges are dynamic.

For example my minimum Claim amount is 100 and max is 1000 I wanted to show JSON in this way:

{
    "100-150": 2,
    "150-250": 3,
    "250-400": 1,
    "400-500": 5,
    "above_500": 12
}

I tried doing this way assuming my data range is between 1 and 2000 but this becomes of no use if my minimum amount lies in between 10000 and 100000.

d = Claim.objects.aggregate(upto_500=Count('pk', filter=Q(amount__lte=500)),
                                    above_500__below_1000=Count('pk', filter=Q(amount__range=[501, 999])),
                                    above_1000__below_2000=Count('pk', filter=Q(amount__range=[1000, 2000])),
                                    above_2000=Count('pk', filter=Q(amount__gte=2000))
                                    )

Any idea how we can make dynamic way of getting amount ranges and throwing it to frontend?

django-rest-framework django-queryset django-models django python
3 Answers

I think this is what are you looking for:

from django.db.models import Max, Min, Count, Q

# Retrieve min and max of amount
claim_min_max = Claim.objects.aggregate(Min("amount"), Max("amount"))

amount_min = claim_min_max["amount__min"]
amount_max = claim_min_max["amount__max"]
step = 100

# Create a list of pairs of size "step"
elements = range(amount_min, amount_max, step)
pairs = []
for i in range(len(elements)):
    try:
        pairs.append((elements[i], elements[i + 1]))
    except IndexError:
        break

# Add the last pair until the end
pairs.append(pairs[:-1][1], amount_max)


aggregate_pairs = {
    f"from_{_from}_to_{_to}": Count("pk", filter=Q(amount__range=[_from, _to]))
    for _from, _to in pairs
}

queryset = Claim.objects.aggregate(**aggregate_pairs)

A dynamic way to count elements in batches

user_22083100

Let a single value act as an identifier for a range, and then group by that value. That identifier can be quotient, when divided by the step size.

For example, if you have values: [121, 131, 170, 215, 390], transform this to [100, 100, 150, 200, 350] (assuming step=50) and count the values.

from django.db.models import Count, F, IntegerField
from django.db.models.functions import Cast, Floor

step = 50

# Ignore the min/max logic altogether if you don't care about getting these explicitly
min_value = 100
max_value = 500


claims = Claim.objects.all()
claims = claims.filter(amount__gte=min_value, amount__lt=max_value)
counts = (
    claims.annotate(
        value=Cast(
            Floor(F('amount') / (step * 1.0)) * step,
            output_field=IntegerField(),
        )
    )
    .values('value')
    .annotate(count=Count('*'))
    .values_list('value', 'count')
)

ranges = {}
for value, count in counts:
    range_name = f"{value}-{value + step}"
    ranges[range_name] = count

ranges[f"<{min_value}"] = Claim.objects.filter(amount__lt=min_value).count()
ranges[f">={max_value}"] = Claim.objects.filter(amount__gte=max_value).count()
user_17547520 
from django.db.models import Q

upto_500=Count('pk', filter=Q(amount__lte=500))
                            

above_500__below_1000=Count('pk', filter=Q(amount__range=(501, 999)))
                            

above_1000__below_2000=Count('pk', filter=Q(amount__range=(1000, 2000)))
                            

above_2000=Count('pk', filter=Q(amount__gte=2000))

claim = Claim.objects.annotate(upto_500=upto_500).annotate(above_500__below_1000=above_500__below_1000).annotate(above_1000__below_2000=above_1000__below_2000).annotate(above_2000=above_2000)

print(claim[0].upto_500)

this is a count of less than 500 and so on.

now you can easily create JSON from it.

user_191018880
Related