[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16952633#comment-16952633
]
benj edited comment on CALCITE-3402 at 10/17/19 12:47 PM:
----------------------------------------------------------
With Drill 1.16 and 1.17 (from 2019-10-14) ROWS is not either possible.
{code:sql}
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
{code}
More precisely with ORDER BY, the only possibilities to write ROWS clause are
(the 2 possibilities are identical) :
* ROWS BETWEEN UNBOUNDED PRECEDING AND current row
* ROWS UNBOUNDED PRECEDING
>>If you use {{RANGE}} with a composite {{ORDER BY}}, does PostgreSQL apply the
>>range to just the first key? The documentation you referenced does not say.
No Postgres apply all the key and then apply the range on
{noformat}
The ORDER BY option determines the order in which the rows of a partition are
processed by the window function. It works similarly to a query-level ORDER BY
clause, but likewise cannot use output-column names or numbers. Without ORDER
BY, rows are processed in an unspecified order.{noformat}
in practice, with a table _tex_
{noformat}
a,b,c
1,1,1
2,2,1
3,2,2
4,3,1
5,3,2
6,3,3{noformat}
you can do
{code:sql}
-- Postgres
SELECT *
, last_value(a) OVER(ORDER BY b RANGE BETWEEN unbounded PRECEDING AND unbounded
FOLLOWING) sort_b
, last_value(a) OVER(ORDER BY b, c DESC RANGE BETWEEN unbounded PRECEDING AND
unbounded FOLLOWING) sort_b_and_c
FROM tex
WHERE a = 4
-- =>
4;3;1;6;4
-- The result of columns 4 and 5 well depends of all compound of the ORDER BY
-- !Postgres
{code}
was (Author: benj641):
With Drill 1.16 and 1.17 (from 2019-10-14) ROWS is not either possible.
{code:sql}
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
{code}
More precisely with ORDER BY, the only possibilities to write ROWS clause are
(the 2 possibilities are identical) :
* ROWS BETWEEN UNBOUNDED PRECEDING AND current row
* ROWS UNBOUNDED PRECEDING
>>If you use {{RANGE}} with a composite {{ORDER BY}}, does PostgreSQL apply the
>>range to just the first key? The documentation you referenced does not say.
No Postgres apply all the key and then apply the range on
{noformat}
The ORDER BY option determines the order in which the rows of a partition are
processed by the window function. It works similarly to a query-level ORDER BY
clause, but likewise cannot use output-column names or numbers. Without ORDER
BY, rows are processed in an unspecified order.{noformat}
in practice, with a table _tex_
{noformat}
a,b,c
1,1,1
2,2,1
3,2,2
4,3,1
5,3,2
6,3,3{noformat}
you can do
{code:sql}
-- Postgres
SELECT *
, last_value(a) OVER(ORDER BY b RANGE BETWEEN unbounded PRECEDING AND unbounded
FOLLOWING) sort_b
, last_value(a) OVER(ORDER BY b, c DESC RANGE BETWEEN unbounded PRECEDING AND
unbounded FOLLOWING) sort_b_and_c
FROM tex
WHERE a = 4
-- =>
4;3;1;6;4
-- The result of columns 4 and 5 well depends of all compound of the ORDER BY
-- !Postgres
{code}
toto
> Allow RANGE with compoud ORDER BY clause
> ----------------------------------------
>
> Key: CALCITE-3402
> URL: https://issues.apache.org/jira/browse/CALCITE-3402
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.18.0, 1.19.0
> Reporter: benj
> Priority: Major
>
> It will be very useful to have the capacity to use compound ORDER BY clause
> with RANGE
> {code:sql}
> apache drill (dfs.tmp)> SELECT a
> , last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN
> UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
> FROM (SELECT 1 a, 'b' b, 3 c
> UNION SELECT 2, 'c', 4
> UNION SELECT 1, 'c', 4
> /* UNION ... */
> ) x;
> Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE
> clause cannot be used with compound ORDER BY clause
> {code}
> I know it's possible (for last_value) to rewrite with first_value with an
> reverse ORDER BY and without RANGE to obtain correct result.
> But it will become sometimes less readable and request write from other SGBDR
> will not be compatible and should be rewrite, and for some other function
> than last_value, the problem will not be solved like that.
> compound ORDER BY clause with RANGE is possible with some SGBDR like
> Postgres:
> [https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
