Django workaround to use window function call in an aggregate function?

Question

I'm trying to calculate customer order frequency.

First use a window function to get the previous order date then annotate the days since the last order.

from django.db.models import Avg, F, Window
from django.db.models.functions import ExtractDay, Lag, TruncDate

orders = (
    Order.objects
    .annotate(
        prev_order_date=Window(
            expression=Lag('paid_at', 1),
            partition_by=[F('customer_email')],
            order_by=F('paid_at').asc(),
        ),
        days_since_last=ExtractDay(
            TruncDate('paid_at') - TruncDate('prev_order_date')
        ),
    )
)

Then group by customer_email before calculating the average frequency.

customer_data = (
    orders.values('customer')
    .annotate(avg_frequency=Avg('days_since_last'))
)

Unfortunately this throws an error. Does anyone know of a workaround or know of an alternate way to calculate the average frequency?

psycopg2.errors.GroupingError: aggregate function calls cannot contain window function calls

score 0 · Accepted Answer · answered May 05 '21 at 14:23

I found the django-cte package through this answer.

Join on the order id then make sure to annotate the result of the window function before grouping.

from django_cte import CTEManager, With

class OrderCTE(Order):
    objects = CTEManager()

    class Meta:
        proxy = True

orders = With(
    Order.objects
    .annotate(
        prev_order_date=Window(
            expression=Lag('paid_at', 1),
            partition_by=[F('customer_email')],
            order_by=F('paid_at').asc(),
        ),
        days_since_last=ExtractDay(
            TruncDate('paid_at') - TruncDate('prev_order_date')
        ),
    )
)

customer_data = list(
    orders.join(OrderCTE, id=orders.col.id)
    .with_cte(orders)
    .annotate(days_since_last=orders.col.days_since_last)
    .values('customer_email')
    .order_by('customer_email')
    .annotate(
        avg_frequency=Avg('days_since_last'),
    )
    .values_list(
        'customer_email',
        'avg_frequency',
    )
)

Django workaround to use window function call in an aggregate function?

1 Answers1