#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 , 8 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Summary: | Window functions → Add a window function expression |
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 8 years ago
Cc: | added |
---|
comment:3 by , 8 years ago
comment:4 by , 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 , 8 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:6 by , 8 years ago
Has patch: | set |
---|---|
Patch needs improvement: | set |
comment:7 by , 8 years ago
PR contains a pretty rough outline (tests are currently failing) of an implementation.
comment:8 by , 8 years ago
Cc: | added |
---|
comment:9 by , 8 years ago
Cc: | added; removed |
---|
comment:10 by , 8 years ago
Version: | 1.9 → master |
---|
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 , 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 , 7 years ago
Patch needs improvement: | set |
---|
comment:13 by , 7 years ago
Patch needs improvement: | unset |
---|
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.