[jira] [Created] (DRILL-7420) window function improve ROWS clause/frame possibilities

["benj (Jira)" <ji...@apache.org>]

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)