10.15 Window expressions: `Window`, `WindowFrame` and the QUALIFY rewrite

Window is an expression whose function is applied over a partition of the rows and written as function OVER (...); WindowFrame, with its two classes RowRange and ValueRange, says which rows of the partition the function sees. A condition on a window cannot stand in a statement’s "WHERE", so where a filter names one, WhereNode.split_having_qualify sets the condition aside and SQLCompiler.get_qualify_sql writes the statement inside another and tests the condition there.

An aggregate folds a group of rows into one value, and the statement has one row for each group. A window applies a function to a set of rows too, but reports the answer on every row: the number of each sailor within the crew of its ship, the sum of the rows before this one, the value in the row above. So a window has to carry three things beside its function: the partition, which rows are reckoned together; an ordering, in which they are reckoned; and a frame, which rows of the partition, counted from the current one, the function sees. It has to say two things about itself: that it is not an aggregate, so that no "GROUP BY" is made for it although its function may be Sum or Count; and that it is a window, so that a condition on it is kept out of the "WHERE", where SQL has no window yet, and tested in a statement wrapped around this one. And it has to be of its function’s type. The source is Window, and the functions made for windows are the classes of django/db/models/functions/window.py.

The recordings below were made from a running Django, on SQLite, over the models of the chapter’s opening page (From QuerySet to SQL). A line at the left is Python that was run, with its value after -> or the exception it raised; the lines set in under it are the calls it set going, nested as they were made, with -> what each returned; and a line that begins SQL is a statement as Django handed it to its cursor.

What a `Window` holds

Window.__init__ takes the expression, and the three clauses, partition_by, order_by and frame, each None unless given, and an output_field. It refuses, with ValueError, an expression whose window_compatible is not true. The attribute is False on BaseExpression, so a column, a plain function or an arithmetic expression is refused; Aggregate sets it True, every class of django/db/models/functions/window.py has it True, and of Django’s own classes no other sets it, so the expression of a window is an aggregate or a window function. One aggregate takes itself out again: AnyValue.window_compatible is False.

Window(F('name'))  ->  raises ValueError: Expression 'F' isn't compatible with OVER clauses.
Window(RowNumber(), order_by=5)  ->  raises ValueError: Window.order_by must be either a string reference to a field, an expression, or a list or tuple of them not 5.
...
Window(RowNumber()).get_source_expressions(), Window(RowNumber(), partition_by='ship', order_by='name').get_source_expressions()  ->  ([RowNumber(), None, None, None], [RowNumber(), ExpressionList(F(ship)), OrderByList(F(name)), None])
Window(RowNumber(), partition_by='ship', order_by='name').resolve_expression(Sailor.objects.all().query).get_group_by_cols(), Window(Sum('id')).contains_aggregate, Window(Sum('id')).contains_over_clause  ->  ([Col(crew_sailor, crew.Sailor.ship), Col(crew_sailor, crew.Sailor.name)], False, True)
connection.features.supports_over_clause, connection.features.supports_frame_exclusion  ->  (True, True)

The two clauses are turned into expressions as the window is made, which is why the question about get_source_expressions shows them as objects. A partition given as one thing is made a tuple of one, and the tuple becomes an ExpressionList, a Func whose template is its arguments joined by commas; a string among them becomes an F as any argument of a Func does, so partition_by="ship" is ExpressionList(F("ship")). The ordering goes through OrderByList.from_param, which accepts None, a string, an expression, or a list or tuple of them, an empty one counting as none, and refuses anything else with the ValueError the question with order_by=5 shows. OrderByList is an ExpressionList whose template begins "ORDER BY", and its constructor turns a string that begins with "-" into an OrderBy of the rest, descending; a string without the sign stays an F and is written with no direction after it. The two list classes are told with the rest of the small expressions in Expressions: resolve_expression and as_sql, and OrderBy in ORDER BY and DISTINCT: get_order_by, find_ordering_name and get_distinct. The frame is kept as it was given, and the expression itself goes through _parse_expressions last, which leaves it as it is: anything without window_compatible, a string or a plain value among them, was refused before it got there.

Window.get_source_expressions returns the four in that order, the expression first, with None standing for a clause that was not given; set_source_expressions takes four back. That is the shape by which resolving, relabelling and replace_expressions reach into a window: each resolves whichever of the four is not None, and None is left as it is. Window._resolve_output_field answers with the expression’s output field, so a window over Sum("tonnage") is of the sum’s type and one over RowNumber is an integer, unless an output_field was given.

Two marks are set on the class. Window.contains_aggregate is False, and the comment beside it gives the reason: the expression may be an aggregate, or contain one, and the "GROUP BY" that an aggregate brings into a query is not wanted. Window.contains_over_clause is True, and it is the only class of django/db/models/expressions.py that sets it so; BaseExpression.contains_over_clause is a cached property that is true where any source expression’s is, and WhereNode.contains_over_clause walks a tree’s children in the same way, so the mark climbs from a window through a lookup to the where node that holds it. The two marks together are how a window stands beside an aggregate in a query that groups: the grouping is the aggregate’s doing, and the window’s function adds nothing to it.

tail(Ship.objects.annotate(hands=Count('crew'), fleet=Window(Count('id'))))  ->  ('FROM "fleet_ship" LEFT OUTER JOIN "crew_sailor" ON ("fleet_ship"."id" = "crew_sailor"."ship_id") GROUP BY "fleet_ship"."id", "fleet_ship"."name", "fleet_ship"."tonnage", "fleet_ship"."home_id", "fleet_ship"."captain_id"', ())

Window.get_group_by_cols is what a window contributes when the compiler gathers the grouping: the columns of its partition and of its ordering, and not those of its expression. Where the query groups, each selected expression is asked for its columns, and a window partitioned by the ship puts the ship’s column among them (GROUP BY and HAVING: set_group_by, get_group_by and split_having_qualify).

`Window.as_sql`: the partition, the ordering and the frame after `"OVER"`

sql_of(Sailor.objects.annotate(n=Window(RowNumber(), partition_by=F('ship'), order_by='name')))[0][:200]  ->  'SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n" FROM '
list(Sailor.objects.annotate(n=Window(RowNumber(), partition_by=F('ship'), order_by='name')).values_list('name', 'n'))  ->  [('Ada', 1), ('Bram', 2), ('Cora', 1), ('Dag', 2)]
  SQL SELECT "crew_sailor"."name" AS "name", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n" FROM "crew_sailor"
...
sql_of(Sailor.objects.annotate(n=Window(Rank(), order_by=F('signed_on').desc())))[0][:160]  ->  'SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on", RANK() OVER (ORDER BY "crew_sailor"."signed_on" DESC) AS "n'

Window.as_sql first asks the connection’s operations to check the expression, and then refuses, with NotSupportedError, a backend whose features say it has no "OVER" clause. The base features say False; SQLite’s, as the recording asked, say True. Then it compiles its parts and puts them into Window.template, which is "%(expression)s OVER (%(window)s)". The expression is compiled through the compiler. The partition is written by calling ExpressionList.as_sql directly with the template "PARTITION BY %(expressions)s"; the ordering and the frame are compiled through the compiler, so a vendor method of either is honoured, and the "ORDER BY" is the OrderByList’s own template. The three pieces are joined with one space and stripped, so a window with none of them is written "OVER ()". The parameters are the expression’s followed by the window’s, in the order the pieces were written. In the first statement above, the ordering "name" was given as a string and stands with no direction; in the last, F("signed_on").desc() made an OrderBy, and the direction is written.

SQLite gets a method of its own for one case. Window.as_sqlite looks at the window’s output field, and where it is a DecimalField makes a copy whose expression is given a FloatField as its output field, and compiles the copy through SQLiteNumericMixin.as_sqlite, which puts "CAST(... AS NUMERIC)" round the whole. The comment says why the cast must go round the whole: it has to stand outside the window expression. The copy’s expression is given a FloatField so that its own as_sqlite, an aggregate being a Func on the same mixin, adds no cast of its own, and the one cast is the outer one. For any other output field the method is as_sql under another name.

sql_of(Sailor.objects.annotate(w=Window(Sum('id'), output_field=models.DecimalField())).values('w'))  ->  ('SELECT (CAST(SUM("crew_sailor"."id") OVER () AS NUMERIC)) AS "w" FROM "crew_sailor"', ())

The frame: `WindowFrame`, `RowRange` and `ValueRange`

A frame names the rows of the partition the function sees from the current row: from a start to an end, each a number of rows or of values away, or unbounded. WindowFrame is the base, and the two classes differ in one attribute and one method: RowRange.frame_type is "ROWS", a frame counted in rows, and ValueRange.frame_type is "RANGE", a frame counted in values of the ordering, so that under "RANGE" the rows whose value equals the current row’s are its peers and stand in the frame with it. What the two kinds mean to the database is the documentation’s subject and not the source’s.

sql_of(Sailor.objects.annotate(n=Window(Sum('id'), order_by='id', frame=RowRange(start=-1, end=0))))[0][:200]  ->  'SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on", SUM("crew_sailor"."id") OVER (ORDER BY "crew_sailor"."id" ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)'
sql_of(Sailor.objects.annotate(n=Window(Sum('id'), order_by='id', frame=ValueRange(start=None, end=0, exclusion=WindowFrameExclusion.CURRENT_ROW))))[0][:230]  ->  'SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on", SUM("crew_sailor"."id") OVER (ORDER BY "crew_sailor"."id" RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) '
...
RowRange(start=1, exclusion='ties')  ->  raises TypeError: RowRange.exclusion must be a WindowFrameExclusion instance.
str(RowRange(start=-1, end=0)), str(ValueRange()), ValueRange().as_sql(compiler, connection)  ->  ('ROWS BETWEEN 1 PRECEDING AND CURRENT ROW', 'RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING', ('RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING', ()))

WindowFrame.__init__ takes a start, an end and an exclusion, each None by default, keeps the start and the end as a Value each, and refuses with TypeError an exclusion that is neither None nor a member of WindowFrameExclusion, the enumeration of "CURRENT ROW", "GROUP", "TIES" and "NO OTHERS". A number is read as a distance from the current row: zero is the current row, a negative number is that many rows or values before it, a positive number that many after, and None is unbounded, backwards for the start and forwards for the end. The words are the backend’s: WindowFrame.as_sql hands the two numbers to WindowFrame.window_frame_start_end, which the two classes define as a call of BaseDatabaseOperations.window_frame_rows_start_end or of BaseDatabaseOperations.window_frame_range_start_end, and the operations object turns each number into text with BaseDatabaseOperations.window_frame_value from its own constants, PRECEDING, FOLLOWING, CURRENT_ROW, UNBOUNDED_PRECEDING and UNBOUNDED_FOLLOWING. The template is "%(frame_type)s BETWEEN %(start)s AND %(end)s%(exclude)s", and the end is always written, "UNBOUNDED FOLLOWING" where none was given, as the answer to str(ValueRange()) shows. The frame has no parameters: the numbers are written into the text.

The two operations methods check the numbers, and not alike. For "ROWS", each must be an integer or None, and the start may not be greater than the end, each refusal a ValueError. For "RANGE", the start must be zero, negative or None and the end zero, positive or None: a range that begins after the current row or ends before it is refused, where a row frame may do either. After that window_frame_range_start_end reads one feature, only_supports_unbounded_with_preceding_and_following, and where it is set and a distance was given refuses with NotSupportedError, naming the backend: of Django’s own backends PostgreSQL alone sets it. The exclusion is appended by WindowFrame.get_exclusion as " EXCLUDE " and the member’s value, and as_sql refuses one with NotSupportedError where supports_frame_exclusion is off; the base features say False, and SQLite’s say True. WindowFrame.__str__ works the same words out for itself from the default connection’s constants, which is why str(RowRange(start=-1, end=0)) could be answered without a compiler, and WindowFrame.get_group_by_cols returns nothing, so a frame never reaches a "GROUP BY".

The window functions

The classes of django/db/models/functions/window.py are each a Func with window_compatible set, a function name on all but the base LagLeadFunction, and for some an output_field of their own; none has a template of its own, so each is written as its function name and its arguments in parentheses, and RowNumber() is "ROW_NUMBER()". The ones that take an expression answer with that expression’s type: LagLeadFunction and NthValue define _resolve_output_field to return the first source’s output field, and FirstValue and LastValue leave the inference to Func, which for one argument comes to the same. The output fields named below are of IntegerField and FloatField.

Table 10.17 The window functions, with what each writes on SQLite and what its constructor refuses with ValueError. A number given to a constructor becomes a Value and is sent as a parameter, which is the %s in the second column.

class	writes	takes	output field
`RowNumber`	writes `"ROW_NUMBER()"`	takes nothing	output field an integer
`Rank`	writes `"RANK()"`	takes nothing	output field an integer
`DenseRank`	writes `"DENSE_RANK()"`	takes nothing	output field an integer
`PercentRank`	writes `"PERCENT_RANK()"`	takes nothing	output field a float
`CumeDist`	writes `"CUME_DIST()"`	takes nothing	output field a float
`Ntile`	writes `"NTILE(%s)"`	takes the number of buckets, 1 unless given; zero or less is refused	output field an integer
`FirstValue`	writes `"FIRST_VALUE(...)"`	takes one expression, `arity` 1	output field the expression’s
`LastValue`	writes `"LAST_VALUE(...)"`	takes one expression, `arity` 1	output field the expression’s
`NthValue`	writes `"NTH_VALUE(..., %s)"`	takes an expression and the position, 1 unless given; `None` for the expression or a position of zero or less is refused	output field the expression’s
`Lag`	writes `"LAG(..., %s[, %s])"`	takes an expression, an offset of 1 unless given, and a default written only when one is given; `None` for the expression or an offset of zero or less is refused	output field the expression’s
`Lead`	writes `"LEAD(..., %s[, %s])"`	takes the same as `Lag`	output field the expression’s

sql_of(Sailor.objects.annotate(n=Window(Lag('name', offset=1, default=Value('-')), order_by='id')))[0][:160]  ->  'SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on", LAG("crew_sailor"."name", %s, %s) OVER (ORDER BY "crew_sail'
sql_of(Sailor.objects.annotate(n=Window(Ntile(2), order_by='id')))[0][:120]  ->  'SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on", NTILE(%s) OVER (ORD'
sql_of(Sailor.objects.annotate(n=Window(DenseRank(), partition_by=['ship', 'signed_on__year'], order_by=['-name', 'id'])))[0][:260]  ->  'SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on", DENSE_RANK() OVER (PARTITION BY "crew_sailor"."ship_id", django_datetime_extract(%s, "crew_sailor"."signed_on", %s, %s) ORDER BY "crew_sailor"."name" DESC, "cr'

The default of the Lag is given as Value("-") and not as the string: a string argument of a Func is read as a field’s name, and a default of "-" would have been an F. The question with DenseRank shows a partition of two names, the second with a transform in it: "signed_on__year" became an F, and resolving the F against the query found the field and applied the transform, as it would in a filter (From a name to a column: names_to_path, setup_joins and build_lookup). The function it was written as, "django_datetime_extract", is one Django registers with SQLite, and another backend writes its own (Transforms and database functions: Transform and Func). The aggregates that may stand in a window, and what each writes, are in Aggregates: Aggregate, Count and get_aggregation.

A filter on a window

A window reaches the where tree as any annotation does: filter(n=1) on a queryset whose "n" is a window resolves the name to the window itself, and the lookup made of it, IntegerFieldExact with the window on its left, is the tree’s child (The where clause: build_filter, add_q and WhereNode). The lookup’s contains_over_clause is true through its left side, and so is the tree’s. The compiler does not write that lookup into the "WHERE". What Django does instead is write the statement without the condition, inside another statement that reads its rows as a table and tests the condition on them. The name it gives the device, in the code and in the alias of the inner statement, is "qualify".

numbered = Sailor.objects.annotate(n=Window(RowNumber(), partition_by='ship', order_by='name'))
numbered.filter(n=1).query.get_compiler('default').as_sql()
  SQLCompiler.as_sql
    SQLCompiler.pre_sql_setup
      SQLCompiler.get_select  ->  (5 columns, klass_info for Sailor, {'n': 4})
      SQLCompiler.get_order_by  ->  []
      WhereNode.split_having_qualify  ->  (None, None, (AND: IntegerFieldExact(<Window: RowNumber() OVER (PARTITION BY Col(crew_sailor, crew.Sailor.ship)Col(crew_sailor, crew.Sailor.name))>, 1)))
      -> (extra_select of 0, order_by of 0, group_by of 0)
    SQLCompiler.get_qualify_sql
      SQLCompiler.get_select  ->  (5 columns, klass_info for Sailor, {'n': 4})
      SQLCompiler.get_order_by  ->  []
      SQLCompiler.as_sql(with_col_aliases=True)
        SQLCompiler.pre_sql_setup
          SQLCompiler.get_select  ->  (5 columns, klass_info for Sailor, {'n': 4})
          SQLCompiler.get_order_by  ->  []
          WhereNode.split_having_qualify  ->  ((AND: ), None, None)
          -> (extra_select of 0, order_by of 0, group_by of 0)
        -> ('SELECT "crew_sailor"."id" AS "col1", "crew_sailor"."name" AS "col2", "crew_sailor"."ship_id" AS "col3", "crew_sailor"."signed_on" AS "col4", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n" FROM "crew_sailor"', ())
      -> ('SELECT * FROM ( SELECT "crew_sailor"."id" AS "col1", "crew_sailor"."name" AS "col2", "crew_sailor"."ship_id" AS "col3", "crew_sailor"."signed_on" AS "col4", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n" FROM "crew_sailor" ) "qualify" WHERE "n" = %s', (1,))

The block’s first line makes numbered: every sailor, numbered within its ship’s crew by name, under the annotation "n". The filter asks for the first of each crew.

The split

SQLCompiler.pre_sql_setup has WhereNode.split_having_qualify divide the query’s where tree into its where part, its having part and its qualify part, the last being what refers to a window, and keeps the three on the compiler; how the tree is walked, and which nodes must stay whole, is told in GROUP BY and HAVING: set_group_by, get_group_by and split_having_qualify. The recording’s line for split_having_qualify shows the result for filter(n=1): nothing for the "WHERE", nothing for the "HAVING", and the lookup on the window as the qualify part.

Where a node’s children must stay together, as those of an "OR" that is not negated must, and a window is among them, the whole node goes to the qualify part, unless the node has plain children beside the window and the query groups, which the caller passes in as must_group_by=True: then the method raises NotImplementedError, with the message the documentation quotes. The comment beside the test says that such a disjunction can be pushed down to the qualify part as long as no conditional aggregation is involved, and admits the test is wider than it has to be: in theory only a plain condition on a multi-valued relation could change what the groups contain, but that is hard to tell.

sql_of(numbered.filter(Q(n=1) | Q(name='Dag')))[0][120:]  ->  '"crew_sailor"."signed_on" AS "col4", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n" FROM "crew_sailor" ) "qualify" WHERE ("n" = %s OR "col2" = %s)'
sql_of(numbered.filter(n=1, name__startswith='A'))[0][120:]  ->  '"crew_sailor"."signed_on" AS "col4", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n" FROM "crew_sailor" WHERE "crew_sailor"."name" LIKE %s ESCAPE \'\\\' ) "qualify" WHERE "n" = %s'

The statement with the "OR" is of a window and a plain condition on a query that does not group: the node went whole to the qualify part, and the condition on the name is tested outside, against the column’s alias in the inner statement. The one with "name__startswith" is an "AND": divided, the condition on the name stands in the inner statement’s "WHERE", and only the window’s in the outer one. The "LIKE ... ESCAPE" is SQLite’s way of writing "startswith" (Lookups: Lookup, BuiltinLookup and the standard comparisons).

numbered.annotate(hands=Count('id')).filter(Q(n=1) | Q(hands__gt=1))  ->  <SailorQuerySet [<Sailor: Ada>, <Sailor: Cora>]>
  SQL SELECT * FROM ( SELECT "crew_sailor"."id" AS "col1", "crew_sailor"."name" AS "col2", "crew_sailor"."ship_id" AS "col3", "crew_sailor"."signed_on" AS "col4", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n", COUNT("crew_sailor"."id") AS "hands" FROM "crew_sailor" GROUP BY 1, 2, 3, 4 ) "qualify" WHERE ("n" = %s OR "hands" > %s) LIMIT 21 with params (1, 1)
...
sql_of(numbered.annotate(hands=Count('id')).filter(Q(n=1) | Q(name='Dag')))  ->  raises NotImplementedError: Heterogeneous disjunctive predicates against window functions are not implemented when performing conditional aggregation.

An "OR" of a window and an aggregate on a query that groups is allowed, since no plain condition is among the children: the node went to the qualify part whole, aggregate and all, and the inner statement groups while the outer one tests both. A plain condition under an "OR" with the window, on the same grouped query, is the NotImplementedError.

`get_qualify_sql`

With a qualify part set, SQLCompiler.as_sql takes the statement from SQLCompiler.get_qualify_sql and sets its own ordering aside, since the method has written one.

The whole method turns on one constraint: the outer statement can name only what the inner one selects, and only by alias. So the inner query is given an alias for every column, each expression the condition refers to is replaced by the alias of the column that holds it, and an expression the inner query does not select is added to it under an alias of its own for the test and masked out again afterwards.

get_qualify_sql begins with the inner query: a clone of the query, marked as a subquery, whose where tree is a new node holding the where part and the having part, so that the condition on the window is the one thing left out. It calls SQLCompiler.get_select with with_col_aliases=True, which gives every selected column an alias, "col1", "col2" and on, where the column has none of its own, and an annotation its own name (The SELECT clause: get_select, the select mask and klass_info), and makes a dictionary from each selected expression to its alias.

The function it defines next, over a list of expressions, works out the replacements. An expression that is selected is replaced by its alias. Otherwise a lookup is opened, its sources going on the list; a Ref to a selected alias is left as it is, and a Ref to anything else is opened like a lookup. Anything else is added to the inner query as an annotation under "qual0", "qual1" and on, with Query.add_annotation, and replaced by that alias; the comment at the top of the method says when this happens, a window that values or alias has masked out of the selection being filtered on. The function is run over the leaves of the qualify tree, which WhereNode.leaves yields, and the tree is rewritten with WhereNode.replace_expressions, each expression that has a replacement becoming a Ref to its alias. A Ref writes its alias, quoted, and nothing more, which is why the outer "WHERE" reads "n" = %s and "col2" = %s (Expressions: resolve_expression and as_sql).

The ordering is treated the same way. For each expression SQLCompiler.get_order_by returns, the function runs over its sources, and a copy of the expression with the replacements is kept to be written outside. The inner query then gets a compiler of its own, made with the same connection and the outer compiler’s elide_empty, and its as_sql is called with with_limits=False and with_col_aliases=True; the comments give both reasons: the limits must be applied to the outer query, or rows would be pruned before the condition is tested, and every column needs an alias of its own so that the outer statement can refer to it. The result is a list of pieces, "SELECT * FROM (", the inner statement, ")", the quoted name "qualify", "WHERE" and the compiled qualify part. Where any "qual" alias was added, the whole is wrapped once more, in a "SELECT" of the aliases the outer compiler’s own selection had, from the pieces as a subquery named "qualify_mask": the comment says that aliases unmasked for filtering must be masked back.

list(numbered.filter(n=1).values_list('name', flat=True))  ->  ['Ada', 'Cora']
  SQL SELECT "name" FROM ( SELECT * FROM ( SELECT "crew_sailor"."name" AS "name", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "qual0" FROM "crew_sailor" ) "qualify" WHERE "qual0" = %s ) "qualify_mask" with params (1,)

Here values_list("name") had masked the window out of the selection, "qual0" was added to the inner statement, and the outermost "SELECT" names "name" alone. Last, where there was an ordering, "ORDER BY" and the rewritten expressions are appended; the comment says why it is repeated outside when the inner statement already has it: the SQL specification does not say whether a derived table’s ordering carries through, so it is repeated on the outermost query to make sure.

sql_of(numbered.filter(n=1).order_by('-name'))[0][120:]  ->  '"crew_sailor"."signed_on" AS "col4", ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n" FROM "crew_sailor" ORDER BY "crew_sailor"."name" DESC ) "qualify" WHERE "n" = %s ORDER BY "col2" DESC'

Back in as_sql, the limit and the offset are appended as for any other statement, which The compiler: as_sql, execute_sql and results_iter tells; the LIMIT 21 of the statement above that filters on Q(n=1) | Q(hands__gt=1) is what a queryset’s repr asks for, and stands after the outer "WHERE".

Figure 10.11 The rewrite of a filter on a window. The statement the filter asks for, which no "WHERE" can hold, becomes an inner statement without the condition and an outer one that reads its rows by their aliases and tests the condition there; a window the selection had masked is selected under a "qual" alias for the test and masked again by a third statement round the two.

A compound query never reaches this branch: as_sql looks for a combinator first, and a combined queryset refuses a filter (Two queries into one: combine and get_combinator_sql).

A window in an aggregation, and from an outer query

Query.get_aggregation does not take the plain way, making the aggregates the query’s only annotations, where a window is involved. It looks for an aggregate that refers to an annotation with contains_over_clause set, and for a qualify part in the where tree, and for either sends the query inside an AggregateQuery, the window selected in the inner statement and the aggregate reading its alias outside. Aggregates: Aggregate, Count and get_aggregation has the method; the statement it makes here is this:

numbered.aggregate(m=Max('n'))  ->  {'m': 2}
  SQL SELECT MAX("n") FROM (SELECT ROW_NUMBER() OVER (PARTITION BY "crew_sailor"."ship_id" ORDER BY "crew_sailor"."name") AS "n" FROM "crew_sailor") subquery

A subquery may not reach an outer query’s window either. OuterRef resolves to a ResolvedOuterRef, and when that in turn is resolved against the outer query, ResolvedOuterRef.resolve_expression raises NotSupportedError where what it found has contains_over_clause set, naming the reference. The two classes, and how a query stands inside another, are Subqueries: Subquery, Exists, OuterRef and a query inside another.