Opened 8 years ago

Closed 7 years ago

Last modified 5 years 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 by Tim Graham, 8 years ago

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

comment:2 by Adam Johnson, 8 years ago

Cc: me@… added

comment:3 by Mads Jensen, 8 years ago

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 by Adam Johnson, 8 years ago

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 by Mads Jensen, 8 years ago

Owner: changed from nobody to Mads Jensen
Status: newassigned

comment:6 by Mads Jensen, 8 years ago

Has patch: set
Patch needs improvement: set

comment:7 by Mads Jensen, 8 years ago

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

comment:8 by Josh Smeaton, 8 years ago

Cc: josh.smeaton2 added

comment:9 by Josh Smeaton, 8 years ago

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

comment:10 by Mads Jensen, 8 years ago

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 by Mads Jensen, 7 years ago

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 by Josh Smeaton, 7 years ago

Patch needs improvement: set

comment:13 by Mads Jensen, 7 years ago

Patch needs improvement: unset

comment:14 by Tim Graham <timograham@…>, 7 years ago

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 by Tim Graham <timograham@…>, 6 years ago

In ab251fd:

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

comment:16 by Tim Graham <timograham@…>, 6 years ago

In f1fc7d6:

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

comment:17 by Tim Graham <timograham@…>, 6 years ago

In b505cef:

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

Backport of ab251fdad251cfb1e9fb61c42b5bfed9d0afe393 from master

comment:18 by Tim Graham <timograham@…>, 6 years ago

In 2ce830e:

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

Backport of ab251fdad251cfb1e9fb61c42b5bfed9d0afe393 from master

comment:19 by Tim Graham <timograham@…>, 6 years ago

In b8c48d06:

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

comment:20 by GitHub <noreply@…>, 5 years ago

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