[
https://issues.apache.org/jira/browse/CALCITE-3402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16956811#comment-16956811
]
benj commented on CALCITE-3402:
-------------------------------
Your explanation make senses. I'm agree for the RANGE.
But it's seem's different with ROWS
# Postgres (10.10 (doesn't try in 11 nor 12 but the documentation doesn't
mention it)) doesn't implement the RANGE/ROWS *interval*
# Postgres doesn't implement _RANGE BETWEEN 3 PRECEDING and 5 FOLLOWING_
# BUT Postgres implement ROWS +offset+ PRECEDING/FOLLOWING. The +offset+
doesn't apply just on rows preceding/following in the order specified by ORDER
BY (taking only the position, not the value)
Ok, (1) and (2) are missing implementation for Postgres.
For the point (3) specifically about ROWS, I don't know if Postgres follow
rightly the standard (that is not fully clear for me)
{noformat}
b) If ROWS is specified, then the declared type of UVS shall be exact numeric
with scale 0 (zero).
{noformat}
But Note that at least sqlite3 (from 3.26) adopt the same behaviour (that
naturally allows compound ORDER BY)
Don't know if it's exactly the standard way, but for sure it's very useful. And
it makes sense that RANGE and ROWS do not work the same.
{code:sql}
a,b
1,1
4,3
7,5
8,7
10,11
SELECT a, sum(b) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS
x FROM t5
/* Postgres and sqlite3 (>=3.26) */
a;x
1;4
4;9
7;15
8;23
10;18
/* /!\ work with Sqlite3 (>=3.26) BUT does not work with Postgres*/
SELECT a, sum(b) OVER (ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS
x FROM t5 /* using OF RANGE instead of ROWS */
a;x
1;1
4;3
7;12 /* aggregation done with next rows (a(current)==7 && a==8) => 5+7*/
8;12 /* aggregation done with previous rows (a(current)==8 && a==7) => 5+7 */
10;11
{code}
So Sqlite3 implement 2 distinct behaviour for ROWS and RANGE...
I'd like to check for other database system to see how it behave.
> 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)
