[Django] #26608: Window functions
Django
-------------------------------+--------------------
Reporter: daggaz | Owner: nobody
Type: New feature | Status: new
Component: Uncategorized | Version: 1.9
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
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.
{{{#!python
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:
{{{#!python
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'),
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26608>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: daggaz | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* component: Uncategorized => Database layer (models, ORM)
* needs_tests: => 0
* needs_docs: => 0
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:1>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by adamchainz):
* cc: me@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:2>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:3>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Adam Chainz):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:4>
Django
-------------------------------------+-------------------------------------
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mads Jensen):
* owner: nobody => Mads Jensen
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Mads Jensen):
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Mads Jensen):
[https://github.com/django/django/pull/7611 PR] contains a pretty rough
outline (tests are currently failing) of an implementation.
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Josh Smeaton):
* cc: josh.smeaton2 (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Josh Smeaton):
* cc: josh.smeaton2 (removed)
* cc: josh.smeaton@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Mads Jensen):
* version: 1.9 => master
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mads Jensen):
* needs_better_patch: 1 => 0
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Josh Smeaton):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mads Jensen):
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"d549b8805053d4b064bf492ba90e90db5d7e2a6b" d549b880]:
{{{
#!CommitTicketReference repository=""
revision="d549b8805053d4b064bf492ba90e90db5d7e2a6b"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"ab251fdad251cfb1e9fb61c42b5bfed9d0afe393" ab251fd]:
{{{
#!CommitTicketReference repository=""
revision="ab251fdad251cfb1e9fb61c42b5bfed9d0afe393"
Refs #26608 -- Removed incorrect sentence in
Expression.contains_over_clause docs.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"f1fc7d6b78186171923a9351eb7af3b5b7565156" f1fc7d6]:
{{{
#!CommitTicketReference repository=""
revision="f1fc7d6b78186171923a9351eb7af3b5b7565156"
Refs #26608 -- Removed unneeded name attribute in window functions.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"b505cef63db39abe989ac05c3cf20544b61d8bf0" b505cef]:
{{{
#!CommitTicketReference repository=""
revision="b505cef63db39abe989ac05c3cf20544b61d8bf0"
[2.1.x] Refs #26608 -- Removed incorrect sentence in
Expression.contains_over_clause docs.
Backport of ab251fdad251cfb1e9fb61c42b5bfed9d0afe393 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:17>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"2ce830e683ad2ce60cfdc0d77866fc2e55a48b9f" 2ce830e]:
{{{
#!CommitTicketReference repository=""
revision="2ce830e683ad2ce60cfdc0d77866fc2e55a48b9f"
[2.0.x] Refs #26608 -- Removed incorrect sentence in
Expression.contains_over_clause docs.
Backport of ab251fdad251cfb1e9fb61c42b5bfed9d0afe393 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:18>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"b8c48d06fab3f1c9d52c28422a4a1b8350f5537f" b8c48d06]:
{{{
#!CommitTicketReference repository=""
revision="b8c48d06fab3f1c9d52c28422a4a1b8350f5537f"
Refs #26608 -- Added a database feature for fixed frame range distance
support.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:19>
Django
-------------------------------------+-------------------------------------
Reporter: Jamie Cockburn | Owner: Mads
| Jensen
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by GitHub <noreply@…>):
In [changeset:"02c63b8f2ff48935c0fa2df5e0c8c1699d32b362" 02c63b8f]:
{{{
#!CommitTicketReference repository=""
revision="02c63b8f2ff48935c0fa2df5e0c8c1699d32b362"
Refs #26608 -- Fixed DatabaseFeatures.supports_frame_range_fixed_distance
on SQLite 3.28+, MariaDB 10.2+, and MySQL 8.0.2+.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26608#comment:20>