Add a window function expression

[Jamie Cockburn]

Add the ability to use SQL window functions (SELECT ... OVER (...) FROM ...) to the Django ORM.

I had a go at writing a window function Expression myself. I was mostly guessing at how the API is supposed to work, so improvements would be welcome. This code came about in response to #26602.

class Window(Expression):
    template = '%(expression)s OVER (%(window)s)'

    def __init__(self, expression, partition_by=None, order_by=None, output_field=None):
        self.order_by = order_by
        if isinstance(order_by, six.string_types):
            if order_by.startswith('-'):
                self.order_by = OrderBy(F(self.order_by[1:]), descending=True)
            else:
                self.order_by = OrderBy(F(self.order_by))

        self.partition_by = partition_by
        if self.partition_by:
            self.partition_by = self._parse_expressions(partition_by)[0]

        super(Window, self).__init__(output_field=output_field)
        self.source_expression = self._parse_expressions(expression)[0]
        if not getattr(self.source_expression, 'contains_aggregate', False):
            raise FieldError("Window function expressions must be aggregate functions")

    def _resolve_output_field(self):
        if self._output_field is None:
            self._output_field = self.source_expression.output_field

    def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
        c = self.copy()
        c.source_expression = c.source_expression.resolve_expression(query, allow_joins, reuse, summarize, for_save)
        if c.partition_by:
            c.partition_by = c.partition_by.resolve_expression(query, allow_joins, reuse, summarize, for_save)
        if c.order_by:
            c.order_by = c.order_by.resolve_expression(query, allow_joins, reuse, summarize, for_save)
        c.is_summary = summarize
        c.for_save = for_save
        return c

    def as_sql(self, compiler, connection, function=None, template=None):
        connection.ops.check_expression_support(self)
        expr_sql, params = compiler.compile(self.source_expression)

        window_sql = []
        if self.partition_by:
            window_sql.append('PARTITION BY ')
            order_sql, order_params = compiler.compile(self.partition_by)
            window_sql.append(order_sql)
            params.extend(order_params)
        if self.order_by:
            window_sql.append(' ORDER BY ')
            order_sql, order_params = compiler.compile(self.order_by)
            window_sql.append(order_sql)
            params.extend(order_params)
        template = template or self.template
        return template % {'expression': expr_sql, 'window': "".join(window_sql)}, params

    def copy(self):
        copy = super(Window, self).copy()
        copy.source_expression = self.source_expression.copy()
        copy.partition_by = self.partition_by if self.partition_by else None
        copy.order_by = self.order_by.copy() if self.order_by else None
        return copy

    def get_group_by_cols(self):
        return []

Allowing you to write:

class A(models.Model):
    account = models.IntegerField()
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    created = models.DateTimeField()

A.objects.annotate(
    balance=Window(Sum('amount'), partition_by='account', order_by='created'),
)

[Mads Jensen]

There is some (pretty ugly, rudimentary and unfinished) attempt at this at ​https://github.com/atombrella/django/tree/ticket_26608 I'm struggling with a good way of modelling the frames.

Also, since Oracle supports this, I'm looking for a good place to put this. The backend feature flag is added to indicate support in a backend.

[Adam Johnson]

The next version of MariaDB (10.2) also supports window expressions ( ​https://mariadb.com/kb/en/mariadb/window-functions/ ) so yes this shouldn't be just Postgres specific.

[Mads Jensen]

​PR contains a pretty rough outline (tests are currently failing) of an implementation.

[Mads Jensen]

Current PR aims to add support for MariaDB (it seems that MariaDB will be an officially supported backend in a future release), annotations are added in the test suite for functions that aren't implemented; it has been tested locally against a release candidate of MariaDB 10.2.

All backends have some limitations, e.g., PostgreSQL disallows use together with FOR SHARE/UPDATE. Uncertain how much of these backend restrictions that can be highlighted in the documentation, and how much that should be handled by raising warnings.

Improvement ideas are welcome. I'm content with some parts, such as the actual functions and the filterable-check. However, the syntax for ordering seems a bit clumsy, although it's difficult to see a different way than to use a wrapper (ExpressionList) to add support for DESC/ASC for both expressions and columns.

[Mads Jensen]

Unmarking "Patch needs improvement" to put it in the review queue to trigger more feedback. There are very likely places where it can be written more succinctly.

[Tim Graham <timograham@…>]

In d549b880:

Fixed #26608 -- Added support for window expressions (OVER clause).

Thanks Josh Smeaton, Mariusz Felisiak, Sergey Fedoseev, Simon Charettes,
Adam Chainz/Johnson and Tim Graham for comments and reviews and Jamie
Cockburn for initial patch.

[Tim Graham <timograham@…>]

In ab251fd:

Refs #26608 -- Removed incorrect sentence in Expression.contains_over_clause docs.

[Tim Graham <timograham@…>]

In f1fc7d6:

Refs #26608 -- Removed unneeded name attribute in window functions.

[Tim Graham <timograham@…>]

In b505cef:

[2.1.x] Refs #26608 -- Removed incorrect sentence in Expression.contains_over_clause docs.

Backport of ab251fdad251cfb1e9fb61c42b5bfed9d0afe393 from master

[Tim Graham <timograham@…>]

In 2ce830e:

[2.0.x] Refs #26608 -- Removed incorrect sentence in Expression.contains_over_clause docs.

Backport of ab251fdad251cfb1e9fb61c42b5bfed9d0afe393 from master

[Tim Graham <timograham@…>]

In b8c48d06:

Refs #26608 -- Added a database feature for fixed frame range distance support.

[GitHub <noreply@…>]

In 02c63b8f:

Refs #26608 -- Fixed DatabaseFeatures.supports_frame_range_fixed_distance on SQLite 3.28+, MariaDB 10.2+, and MySQL 8.0.2+.