[
https://issues.apache.org/jira/browse/CALCITE-6240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-6240:
--------------------------------
Summary: Remove ROW_NUMBER if OVER PARTITION BY unique key (was: Removed
ROW_NUMBER if OVER PARTITION BY unique key)
> Remove ROW_NUMBER if OVER PARTITION BY unique key
> -------------------------------------------------
>
> Key: CALCITE-6240
> URL: https://issues.apache.org/jira/browse/CALCITE-6240
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Jiajun Xie
> Assignee: Jiajun Xie
> Priority: Major
>
> The SQL Only have ROW_NUMBER function.
> {code:java}
> SELECT empno, ename FROM (
> SELECT ROW_NUMBER() OVER (PARTITION BY empno) rn, empno, ename
> FROM emp
> ) WHERE rn <= 5{code}
> to
> {code:java}
> SELECT empno, ename FROM emp{code}
>
> If we know every partition count is less than filter count, we can optimize
> {code:java}
> SELECT deptno, ename FROM (
> SELECT ROW_NUMBER() OVER (PARTITION BY deptno) rn, deptno, ename
> FROM emp
> ) WHERE rn <= N
> -- Statistic let we know every department count is less than N{code}
> to
> {code:java}
> SELECT empno, ename FROM emp {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
