Advanced Querying — Aquilia Documentation
Comprehensive guide and documentation for Advanced Querying in the Aquilia framework. View API reference, examples, and implementation patterns.
Docs / Models / Window Functions Window Functions Compute values across related rows without collapsing them — rankings, running totals, moving averages, and more. v1.3.3+ Concept A window function operates on a set of rows related to the current row — defined by an OVER clause — and returns a value for each row individually. Unlike aggregate functions used with GROUP BY, window functions do not collapse rows. Every input row receives its own output row, with the window function result added as an annotation. The canonical use cases are: Ranking — RANK(), DENSE_RANK(), ROW_NUMBER() per partition Running totals — SUM() OVER ordered window Moving averages — AVG() OVER frame clause Lead/lag comparisons — LAG() and LEAD() for period-over-period Top-N per group — rank within partition, then filter in Python or subquery Key difference from GROUP BY: GROUP BY collapses many rows into one aggregate row. Window functions keep all rows and add a computed column. Use GROUP BY when you want one row per group; use window functions when you want all rows with per-row context. Imports # Top-level import — everything exported from aquilia.models from aquilia.models import ( Window, Rank, DenseRank, RowNumber, Ntile, Lag, Lead, FirstValue, LastValue, NthValue, FrameType, FrameBound, WindowFrame, Sum, Avg, Count, # aggregates work as window functions too F, OrderBy, ) # Or import from the window module directly from aquilia.models.window import Window, Rank, FrameBound, WindowFrame The Window() Wrapper Window wraps any window function or aggregate expression with an OVER clause. It is used inside .annotate() just like any other expression. Parameter Type Description ))} # Window generates: RANK() OVER (PARTITION BY "country" ORDER BY "score" DESC) result = await User.objects.annotate( rank=Window(Rank(), partition_by=['country'], order_by='-score') ).all() # Access annotation on each row: for user in result: print(user.rank, user.name) # 1 Alice, 2 Bob, ... Ranking Functions Ranking functions assign a position to each row within its partition. They require an ORDER BY clause in the window to be meaningful; without it, all rows receive the same rank. Class SQL Tie Handling ))} from aquilia.models import Rank, DenseRank, RowNumber, Ntile, Window # Leaderboard: rank players per game, ordered by score DESC leaderboard = await PlayerScore.objects.annotate( rank=Window(Rank(), partition_by=['game_id'], order_by='-score'), dense_rank=Window(DenseRank(), partition_by=['game_id'], order_by='-score'), row_num=Window(RowNumber(), partition_by=['game_id'], order_by='-score'), ).order('game_id', 'rank').all() # Percentile buckets: split each department into 4 salary quartiles quartiles = await Employee.objects.annotate( quartile=Window(Ntile(4), partition_by=['department_id'], order_by='salary') ).all() # quartile=1 → lowest 25%, quartile=4 → top 25% Value Functions: Lag, Lead, First/Last/Nth Value functions look at other rows in the window relative to the current row or access boundary values. Class SQL Description ))} from aquilia.models import Lag, Lead, FirstValue, LastValue, NthValue, Window, F # Month-over-month revenue change monthly = await MonthlySales.objects.annotate( prev_month_revenue=Window( Lag(F('revenue'), offset=1, default=0), partition_by=['product_id'], order_by='month', ) ).all() # pct_change computed in Python after fetching: for row in monthly: if row.prev_month_revenue: row.pct_change = (row.revenue - row.prev_month_revenue) / row.prev_month_revenue # Rolling 3-day lookahead orders = await DailyOrder.objects.annotate( next_3_days=Window( Lead(F('order_count'), offset=3, default=0), partition_by=['region'], order_by='date', ) ).all() # Cheapest item in department (frame: all rows in partition) products = await Product.objects.annotate( cheapest_in_dept=Window( FirstValue(F('price')), partition_by=['department_id'], order_by='price', ) ).all() Aggregates as Window Functions Any aggregate expression (Sum, Avg, Count, Max, Min) can be placed inside Window(). The aggregate then computes over the rows in the window frame instead of the whole group. from aquilia.models import Sum, Avg, Count, Window, F # Running total of sales per region, ordered by date sales = await Sale.objects.annotate( running_total=Window( Sum(F('amount')), partition_by=['region'], order_by='sale_date', ) ).order('region', 'sale_date').all() # 7-day moving average of page views from aquilia.models import WindowFrame, FrameType, FrameBound seven_day_avg = await PageView.objects.annotate( moving_avg=Window( Avg(F('views')), partition_by=['page_id'], order_by='date', frame=WindowFrame( FrameType.ROWS, start=FrameBound.preceding(6), end=FrameBound.current_row(), ) ) ).all() # Cumulative count of orders per customer up to current order orders = await Order.objects.annotate( cumulative_orders=Window( Count(F('id')), partition_by=['customer_id'], order_by='created_at', ) ).all() PARTITION BY & ORDER BY partition_by divides rows into independent groups; the window function resets at each group boundary. order_by within the window controls how rows are sequenced inside each partition. from aquilia.models import Window, Rank, F, OrderBy # Single partition column (string shorthand) Window(Rank(), partition_by='department_id', order_by='-salary') # Multiple partition columns Window(Rank(), partition_by=['country', 'city'], order_by='-score') # F() objects instead of strings Window(Rank(), partition_by=[F('country'), F('city')], order_by='-score') # Mixed ascending/descending with explicit OrderBy Window( Rank(), partition_by=['department_id'], order_by=[OrderBy(F('salary'), descending=True), 'name'], ) # No partition — window spans entire result set Window(Rank(), order_by='-score') # Global rank across all rows Frame Clauses Frame clauses restrict which rows within the ordered window participate in the aggregate. They only apply to aggregate-style window functions (Sum, Avg, etc.) — pure ranking functions ignore frames. FrameType Meaning Support ))} from aquilia.models import WindowFrame, FrameType, FrameBound, Window, Avg, F # Unbounded running total (from first row to current row) WindowFrame( FrameType.ROWS, start=FrameBound.unbounded_preceding(), end=FrameBound.current_row(), ) # 7-row sliding window (3 before, current, 3 after) WindowFrame( FrameType.ROWS, start=FrameBound.preceding(3), end=FrameBound.following(3), ) # Entire partition WindowFrame( FrameType.ROWS, start=FrameBound.unbounded_preceding(), end=FrameBound.unbounded_following(), ) # Example: 30-day moving average thirty_day_ma = await DailyStat.objects.annotate( ma_30=Window( Avg(F('value')), order_by='date', frame=WindowFrame( FrameType.ROWS, start=FrameBound.preceding(29), end=FrameBound.current_row(), ) ) ).all() Production Scenarios Top-N Per Group SQL does not allow filtering on window annotation aliases directly in WHERE (SQL engine restriction — window functions execute after WHERE). Fetch via a CTE or subquery, then filter in Python or wrap in a CTE: from aquilia.models import Window, RowNumber, F # Fetch top-3 posts per author (filter in Python after fetch) posts = await Post.objects.annotate( rn=Window(RowNumber(), partition_by=['author_id'], order_by='-likes') ).all() top3 = [p for p in posts if p.rn Year-over-Year Comparison ))} Limitations & Gotchas ⚠ Cannot filter on window annotations in WHERE — SQL evaluates window functions after WHERE and GROUP BY. Use a CTE or subquery to filter on window results. ⚠ Cannot use window annotations in GROUP BY — Window expressions may not appear in GROUP BY columns. ⚠ Ntile parameter is a bind value — Ntile(n) passes n as a parameter, not inlined SQL. This is correct and safe. ✓ Placeholders use ? — All Aquilia backends use positional ? placeholders. The backend layer translates as needed. Aggregation Common Table Expressions );
Go to Homepage