[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16954354#comment-16954354
]
benj commented on CALCITE-3402:
-------------------------------
Yes of course, these 2 expressions are well equivalent
* RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
* ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
I'm not sure of what you would expect with _RANGE BETWEEN INTERVAL '1' DAY
PRECEDING AND '2' DAY FOLLOWING_, but I like the ideas I imagine.
But not, for Postgres you can only do _value PRECEDING_ where +_value_+ is a
positive integer.
RANGE is only supported with the clause _UNBOUNDED PRECEDING/FOLLOWING_ and the
clause_CURRENT ROW_.
So As you say first, it will be ok if it will be possible to do _ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING_ with DRILL.
Unfortunately Apache Drill give an error with this expression:
{code:sql}
SELECT a
, last_value(c) OVER(PARTITION BY a
ORDER BY c, b DESC
ROWS 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: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
{code}
Apparently, totday, with Drill, it's only possible to do :
* *ROWS* BETWEEN UNBOUNDED PRECEDING AND *CURRENT ROW* - with possibility of
+several ORDER BY+ clause
* *RANGE* BETWEEN UNBOUNDED PRECEDING AND *CURRENT ROW* - with possibility of
+only 1 ORDER BY+ clause
* *RANGE* BETWEEN UNBOUNDED PRECEDING AND *UNBOUNDED FOLLOWING* - with
possibility of +only 1 ORDER BY+ clause
The missing possibility is "_... AND UNBOUNDED FOLLOWING_" in relation with
several _ORDER BY_ clause
> 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)
