[
https://issues.apache.org/jira/browse/CALCITE-7595?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18088097#comment-18088097
]
Julian Hyde edited comment on CALCITE-7595 at 6/10/26 9:35 PM:
---------------------------------------------------------------
DuckDB agrees with you.
{code:java}
select ename, job, hiredate,
avg(sal) over (order by hiredate rows 3 preceding) as avg_sal,
avg(sal) filter (where job = 'MANAGER') over (order by hiredate rows 3
preceding)
as avg_mgr_sal
from emp
order by hiredate;
┌─────────┬───────────┬────────────┬────────────────┬────────────────┐
│ ename │ job │ hiredate │ avg_sal │ avg_mgr_sal │
│ varchar │ varchar │ date │ double │ double │
├─────────┼───────────┼────────────┼────────────────┼────────────────┤
│ SMITH │ CLERK │ 1980-12-17 │ 800.0 │ NULL │
│ ALLEN │ SALESMAN │ 1981-02-20 │ 1200.0 │ NULL │
│ WARD │ SALESMAN │ 1981-02-22 │ 1216.666666667 │ NULL │
│ JONES │ MANAGER │ 1981-04-02 │ 1656.25 │ 2975.0 │
│ BLAKE │ MANAGER │ 1981-05-01 │ 2168.75 │ 2912.5 │
│ CLARK │ MANAGER │ 1981-06-09 │ 2381.25 │ 2758.333333333 │
│ TURNER │ SALESMAN │ 1981-09-08 │ 2443.75 │ 2758.333333333 │
│ MARTIN │ SALESMAN │ 1981-09-28 │ 2012.5 │ 2650.0 │
│ KING │ PRESIDENT │ 1981-11-17 │ 2550.0 │ 2450.0 │
│ FORD │ ANALYST │ 1981-12-03 │ 2687.5 │ NULL │
│ JAMES │ CLERK │ 1981-12-03 │ 2550.0 │ NULL │
│ MILLER │ CLERK │ 1982-01-23 │ 2562.5 │ NULL │
│ SCOTT │ ANALYST │ 1987-04-19 │ 2062.5 │ NULL │
│ ADAMS │ CLERK │ 1987-05-23 │ 1587.5 │ NULL │
└─────────┴───────────┴────────────┴────────────────┴────────────────┘
14 rows 5 columns {code}
was (Author: julianhyde):
DuckDB agrees with you.
{code:java}
select ename, job, hiredate,
avg(sal) over (order by hiredate rows 3 preceding) as avg_sal,
avg(sal) filter (where job = 'MANAGER') over (order by hiredate rows 3
preceding)
as avg_mgr_sal
from emp
order by hiredate;
┌─────────┬───────────┬────────────┬────────────────────┬────────────────────┐
│ ename │ job │ hiredate │ avg_sal │ avg_mgr_sal │
│ varchar │ varchar │ date │ double │ double │
├─────────┼───────────┼────────────┼────────────────────┼────────────────────┤
│ SMITH │ CLERK │ 1980-12-17 │ 800.0 │ NULL │
│ ALLEN │ SALESMAN │ 1981-02-20 │ 1200.0 │ NULL │
│ WARD │ SALESMAN │ 1981-02-22 │ 1216.6666666666667 │ NULL │
│ JONES │ MANAGER │ 1981-04-02 │ 1656.25 │ 2975.0 │
│ BLAKE │ MANAGER │ 1981-05-01 │ 2168.75 │ 2912.5 │
│ CLARK │ MANAGER │ 1981-06-09 │ 2381.25 │ 2758.3333333333335 │
│ TURNER │ SALESMAN │ 1981-09-08 │ 2443.75 │ 2758.3333333333335 │
│ MARTIN │ SALESMAN │ 1981-09-28 │ 2012.5 │ 2650.0 │
│ KING │ PRESIDENT │ 1981-11-17 │ 2550.0 │ 2450.0 │
│ FORD │ ANALYST │ 1981-12-03 │ 2687.5 │ NULL │
│ JAMES │ CLERK │ 1981-12-03 │ 2550.0 │ NULL │
│ MILLER │ CLERK │ 1982-01-23 │ 2562.5 │ NULL │
│ SCOTT │ ANALYST │ 1987-04-19 │ 2062.5 │ NULL │
│ ADAMS │ CLERK │ 1987-05-23 │ 1587.5 │ NULL │
└─────────┴───────────┴────────────┴────────────────────┴────────────────────┘
14 rows 5 columns
{code}
> 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)
