#26608: Window functions
-------------------------------+--------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.2bf922cecaed2e329c70373f91db338b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
