From 3e2f61c439dab76133a49b7a16b03bf4071d4c4c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 19 Aug 2018 22:19:59 -0400 Subject: [PATCH] Add missing range_ / rows parameters to additional over() methods Added missing window function parameters :paramref:`.WithinGroup.over.range_` and :paramref:`.WithinGroup.over.rows` parameters to the :meth:`.WithinGroup.over` and :meth:`.FunctionFilter.over` methods, to correspond to the range/rows feature added to the "over" method of SQL functions as part of :ticket:`3049` in version 1.1. Fixes: #4322 Change-Id: I77dcdac65c699a4b52a3fc3ee09a100ffb4fc20e --- doc/build/changelog/unreleased_12/4322.rst | 10 +++++ lib/sqlalchemy/sql/elements.py | 12 ++++-- test/sql/test_compiler.py | 44 ++++++++++++++++++++++ test/sql/test_functions.py | 32 ++++++++++++++++ 4 files changed, 94 insertions(+), 4 deletions(-) create mode 100644 doc/build/changelog/unreleased_12/4322.rst diff --git a/doc/build/changelog/unreleased_12/4322.rst b/doc/build/changelog/unreleased_12/4322.rst new file mode 100644 index 0000000000..f1b1634ec0 --- /dev/null +++ b/doc/build/changelog/unreleased_12/4322.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, sql + :tickets: 4322 + + Added missing window function parameters + :paramref:`.WithinGroup.over.range_` and :paramref:`.WithinGroup.over.rows` + parameters to the :meth:`.WithinGroup.over` and + :meth:`.FunctionFilter.over` methods, to correspond to the range/rows + feature added to the "over" method of SQL functions as part of + :ticket:`3049` in version 1.1. diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 2a6fa323cf..5f9fd2ebf5 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -3368,7 +3368,7 @@ class WithinGroup(ColumnElement): *util.to_list(order_by), _literal_as_text=_literal_as_label_reference) - def over(self, partition_by=None, order_by=None): + def over(self, partition_by=None, order_by=None, range_=None, rows=None): """Produce an OVER clause against this :class:`.WithinGroup` construct. @@ -3376,7 +3376,9 @@ class WithinGroup(ColumnElement): :meth:`.FunctionElement.over`. """ - return Over(self, partition_by=partition_by, order_by=order_by) + return Over( + self, partition_by=partition_by, order_by=order_by, + range_=range_, rows=rows) @util.memoized_property def type(self): @@ -3477,7 +3479,7 @@ class FunctionFilter(ColumnElement): return self - def over(self, partition_by=None, order_by=None): + def over(self, partition_by=None, order_by=None, range_=None, rows=None): """Produce an OVER clause against this filtered function. Used against aggregate or so-called "window" functions, @@ -3495,7 +3497,9 @@ class FunctionFilter(ColumnElement): See :func:`~.expression.over` for a full description. """ - return Over(self, partition_by=partition_by, order_by=order_by) + return Over( + self, partition_by=partition_by, order_by=order_by, + range_=range_, rows=rows) @util.memoized_property def type(self): diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 0ef19e0cb5..7f2c44bf11 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2485,6 +2485,50 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): func.row_number().over, range_=(-5, 8), rows=(-2, 5) ) + def test_over_within_group(self): + from sqlalchemy import within_group + stmt = select([ + table1.c.myid, + within_group( + func.percentile_cont(0.5), + table1.c.name.desc() + ).over( + range_=(1, 2), + partition_by=table1.c.name, + order_by=table1.c.myid + ) + ]) + eq_ignore_whitespace( + str(stmt), + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name DESC) " + "OVER (PARTITION BY mytable.name ORDER BY mytable.myid " + "RANGE BETWEEN :param_1 FOLLOWING AND :param_2 FOLLOWING) " + "AS anon_1 FROM mytable" + ) + + stmt = select([ + table1.c.myid, + within_group( + func.percentile_cont(0.5), + table1.c.name.desc() + ).over( + rows=(1, 2), + partition_by=table1.c.name, + order_by=table1.c.myid + ) + ]) + eq_ignore_whitespace( + str(stmt), + "SELECT mytable.myid, percentile_cont(:percentile_cont_1) " + "WITHIN GROUP (ORDER BY mytable.name DESC) " + "OVER (PARTITION BY mytable.name ORDER BY mytable.myid " + "ROWS BETWEEN :param_1 FOLLOWING AND :param_2 FOLLOWING) " + "AS anon_1 FROM mytable" + ) + + + def test_date_between(self): import datetime table = Table('dt', metadata, diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index 3032c3ce3a..48d5fc37f8 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -513,6 +513,38 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "AS anon_1 FROM mytable" ) + def test_funcfilter_windowing_range(self): + self.assert_compile( + select([ + func.rank().filter( + table1.c.name > 'foo' + ).over( + range_=(1, 5), + partition_by=['description'] + ) + ]), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (PARTITION BY mytable.description RANGE BETWEEN :param_1 " + "FOLLOWING AND :param_2 FOLLOWING) " + "AS anon_1 FROM mytable" + ) + + def test_funcfilter_windowing_rows(self): + self.assert_compile( + select([ + func.rank().filter( + table1.c.name > 'foo' + ).over( + rows=(1, 5), + partition_by=['description'] + ) + ]), + "SELECT rank() FILTER (WHERE mytable.name > :name_1) " + "OVER (PARTITION BY mytable.description ROWS BETWEEN :param_1 " + "FOLLOWING AND :param_2 FOLLOWING) " + "AS anon_1 FROM mytable" + ) + def test_funcfilter_within_group(self): stmt = select([ table1.c.myid, -- 2.47.2