[
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)
