bchong95 opened a new pull request, #2953:
URL: https://github.com/apache/calcite/pull/2953
QUALIFY is to Window functions what HAVING is to GROUP BY.
For example:
```
SELECT a, b, c, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) as row_num
FROM t1
QUALIFY row_num = 1
```
Is the same as:
```
SELECT *
FROM (
SELECT a, b, c, ROW_NUMBER() OVER (PARTITION BY b ORDER BY c) as row_num
FROM t1)
WHERE row_num = 1
```
The first change to note is in Parser.jj where we add support for a QUALIFY
keyword.
The remainder of the changes is in RelToSqlConverter.java.
Here is the plan generated by some queries:
* QUALIFY not referencing a derived column:
```
SELECT empno, ename, deptno
FROM emp
QUALIFY ROW_NUMBER() over (partition by ename order by deptno) = 1
```
```
LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2])
LogicalFilter(condition=[$3])
LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7],
QualifyExpression=[=(ROW_NUMBER() OVER (PARTITION BY $1 ORDER BY $7), 1)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
```
* QUALIFY referencing a derived column:
```
SELECT empno, ename, deptno, ROW_NUMBER() over (partition by ename order by
deptno) as row_num
FROM emp
QUALIFY row_num = 1
```
```
LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], ROW_NUM=[$3])
LogicalFilter(condition=[$4])
LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7],
ROW_NUM=[ROW_NUMBER() OVER (PARTITION BY $1 ORDER BY $7)],
QualifyExpression=[=(ROW_NUMBER() OVER (PARTITION BY $1 ORDER BY $7), 1)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
