benj created DRILL-7420:
---------------------------
Summary: window function improve ROWS clause/frame possibilities
Key: DRILL-7420
URL: https://issues.apache.org/jira/browse/DRILL-7420
Project: Apache Drill
Issue Type: New Feature
Affects Versions: 1.16.0
Reporter: benj
The possibility of window frame are currently limited in Apache Drill.
ROWS clauses is only possible with "BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW".
It will be useful to have possibilities to use:
* "BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
* "BETWEEN x PRECEDING AND y FOLLOWING"
{code:sql}
/* ROWS clause is only possible with "BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW" */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) FROM (SELECT 1 a, 1 b, 1 c);
+---+---+---+--------+
| a | b | c | EXPR$3 |
+---+---+---+--------+
| 1 | 1 | 1 | 1 |
+---+---+---+--------+
1 row selected (1.357 seconds)
/* ROWS is currently not possible with "BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING" (it's possible with RANGE but with single ORDER BY only )
*/
apache drill> SELECT *, sum(a) OVER(ORDER BY b, c ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
/* ROWS is currently not possible with "BETWEEN x PRECEDING AND y FOLLOWING" */
apache drill> SELECT *, sum(a) OVER(ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) FROM (SELECT 1 a, 1 b, 1 c);
Error: UNSUPPORTED_OPERATION ERROR: This type of window frame is currently not
supported
See Apache Drill JIRA: DRILL-3188
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
