[
https://issues.apache.org/jira/browse/CALCITE-7595?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18087849#comment-18087849
]
Yu Xu commented on CALCITE-7595:
--------------------------------
In my view FILTER is applied AFTER the ROWS window：
Execution order:{*}{*}
1. Define window rows by PARTITION BY / ORDER BY
2. Apply ROWSbounds to determine window frame
3. Apply FILTER condition to rows within that frame
4. Calculate aggregate on filtered rows
ROWS defines the physical window; FILTER defines which rows within that window
participate in aggregation. [~julianhyde]
> Support FILTER clause with window functions
> -------------------------------------------
>
> Key: CALCITE-7595
> URL: https://issues.apache.org/jira/browse/CALCITE-7595
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.42.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
> Fix For: 1.43.0
>
>
> Currently Calcite not support FILTER OVER syntax，but PostgreSQL support
> it：[https://www.postgresql.org/docs/current/sql-expressions.html]
> SQL
> {code:java}
> SELECT COUNT(*) FILTER (WHERE x > 5) OVER (PARTITION BY deptno) FROM t; {code}
> error
> {code:java}
> -- ERROR: SqlValidatorException: OVER must be applied to aggregate function
> {code}
> Maybe we can improve the filter over support.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
