Opened 5 years ago

Closed 4 years ago

Last modified 20 months ago

#26608 closed New feature (fixed)

Add a window function expression

Reported by: Jamie Cockburn Owned by: Mads Jensen
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: me@…, josh.smeaton@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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'),
)

Change History (20)

comment:1 Changed 5 years ago by Tim Graham

Component: UncategorizedDatabase layer (models, ORM)
Summary: Window functionsAdd a window function expression
Triage Stage: UnreviewedAccepted

comment:2 Changed 5 years ago by Adam Johnson

Cc: me@… added

comment:3 Changed 5 years ago by 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.

comment:4 Changed 5 years ago by 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.

comment:5 Changed 5 years ago by Mads Jensen

Owner: changed from nobody to Mads Jensen
Status: newassigned

comment:6 Changed 5 years ago by Mads Jensen

Has patch: set
Patch needs improvement: set

comment:7 Changed 5 years ago by Mads Jensen

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

comment:8 Changed 4 years ago by Josh Smeaton

Cc: josh.smeaton2 added

comment:9 Changed 4 years ago by Josh Smeaton

Cc: josh.smeaton@… added; josh.smeaton2 removed

comment:10 Changed 4 years ago by Mads Jensen

Version: 1.9master

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.

comment:11 Changed 4 years ago by Mads Jensen

Patch needs improvement: unset

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.

comment:12 Changed 4 years ago by Josh Smeaton

Patch needs improvement: set

comment:13 Changed 4 years ago by Mads Jensen

Patch needs improvement: unset

comment:14 Changed 4 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

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.

comment:15 Changed 3 years ago by Tim Graham <timograham@…>

In ab251fd:

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

comment:16 Changed 3 years ago by Tim Graham <timograham@…>

In f1fc7d6:

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

comment:17 Changed 3 years ago by Tim Graham <timograham@…>

In b505cef:

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

Backport of ab251fdad251cfb1e9fb61c42b5bfed9d0afe393 from master

comment:18 Changed 3 years ago by Tim Graham <timograham@…>

In 2ce830e:

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

Backport of ab251fdad251cfb1e9fb61c42b5bfed9d0afe393 from master

comment:19 Changed 2 years ago by Tim Graham <timograham@…>

In b8c48d06:

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

comment:20 Changed 20 months ago by 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+.

Note: See TracTickets for help on using tickets.
Back to Top