Calculate the Average delivery time (days) Django ORM-CodePudding

I want to calculate the average delivery time (in days) of products using ORM single query (The reason of using single query is, I've 10000 records in db and don't want to iterate them over loops). Here is the example of models file, I have:

class Product(models.Model):
    name = models.CharField(max_length=10)


class ProductEvents(models.Model):
    
    class Status(models.TextChoices):
        IN_TRANSIT = ("in_transit", "In Transit")
        DELIVERED = ("delivered", "Delivered")

    product = models.ForiegnKey(Product, on_delete=models.CASCADE)
    status = models.CharField(max_length=255, choices=Status.choices)
    created = models.DateTimeField(blank=True)

To calculate the delivery time for 1 product is:

product = Product.objects.first()
# delivered_date - in_transit_date = days_taken
duration = product.productevent_set.get(status='delivered').created - product.productevent_set.get(status='in_transit').created

I'm here to get your help to getting started myself over this so, that I can calculate the average time between all of the Products. I'd prefer it to done in a single query because of the performance.

CodePudding user response：

A basic solution is to annotate each Product with the minimum created time for related events that have the status "in-transit and select the maximum time for events with the delivered status then annotate the diff and aggregate the average of the diffs

from django.db.models import Min, Max, Q, F, Avg

Product.objects.annotate(
    start=Min('productevents__created', filter=Q(productevents__status=ProductEvents.Status.IN_TRANSIT)),
    end=Max('productevents__created', filter=Q(productevents__status=ProductEvents.Status.DELIVERED))
).annotate(
    diff=F('end') - F('start')
).aggregate(
    Avg('diff')
)

Returns a dictionary that should look like

{'diff__avg': datetime.timedelta(days=x, seconds=x)}