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

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

    [ https://issues.apache.org/jira/browse/DRILL-7420?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17248419#comment-17248419 ] 

Joshua Pedrick commented on DRILL-7420:
---------------------------------------

I would love to see this feature supported in Drill. Without this any kind of rolling window operations(7 day moving average) are impossible with Drill as far as I know.

> 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
>            Priority: Major
>
> 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)