[CALCITE-2383] NTH_VALUE window function (Sergey Nuyanzin) Break SqlNthValueAggregateFunction out as a top-level class, and add tests to SqlValidatorTest. (Julian Hyde)
Close apache/calcite#742 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/df774b9e Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/df774b9e Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/df774b9e Branch: refs/heads/master Commit: df774b9efa97c0b11ed63036e829750aaa88b99d Parents: bc269aa Author: snuyanzin <[email protected]> Authored: Tue Jun 26 15:09:06 2018 +0300 Committer: Julian Hyde <[email protected]> Committed: Sun Jul 8 22:46:20 2018 -0700 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 1 + .../calcite/adapter/enumerable/RexImpTable.java | 58 ++++++++++++++++++++ .../java/org/apache/calcite/sql/SqlKind.java | 3 + .../calcite/sql/fun/SqlNthValueAggFunction.java | 38 +++++++++++++ .../calcite/sql/fun/SqlStdOperatorTable.java | 7 +++ .../apache/calcite/sql/test/SqlAdvisorTest.java | 1 + .../apache/calcite/test/SqlValidatorTest.java | 21 ++++--- core/src/test/resources/sql/winagg.iq | 25 +++++++++ site/_docs/reference.md | 3 +- 9 files changed, 148 insertions(+), 9 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/core/src/main/codegen/templates/Parser.jj ---------------------------------------------------------------------- diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 7fda2bc..a89352f 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -5163,6 +5163,7 @@ SqlIdentifier ReservedFunctionName() : | <MINUTE> | <MOD> | <MONTH> + | <NTH_VALUE> | <NTILE> | <NULLIF> | <OCTET_LENGTH> http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java index 1bc9de4..f806a11 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java @@ -178,6 +178,7 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_EQUALS; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_LIKE; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_SIMILAR_TO; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NOT_SUBMULTISET_OF; +import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NTH_VALUE; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.NTILE; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OR; import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OVERLAY; @@ -455,6 +456,7 @@ public class RexImpTable { winAggMap.put(ROW_NUMBER, constructorSupplier(RowNumberImplementor.class)); winAggMap.put(FIRST_VALUE, constructorSupplier(FirstValueImplementor.class)); + winAggMap.put(NTH_VALUE, constructorSupplier(NthValueImplementor.class)); winAggMap.put(LAST_VALUE, constructorSupplier(LastValueImplementor.class)); winAggMap.put(LEAD, constructorSupplier(LeadImplementor.class)); winAggMap.put(LAG, constructorSupplier(LagImplementor.class)); @@ -1517,6 +1519,62 @@ public class RexImpTable { } } + /** Implementor for the {@code NTH_VALUE} + * windowed aggregate function. */ + static class NthValueImplementor implements WinAggImplementor { + public List<Type> getStateType(AggContext info) { + return Collections.emptyList(); + } + + public void implementReset(AggContext info, AggResetContext reset) { + // no op + } + + public void implementAdd(AggContext info, AggAddContext add) { + // no op + } + + public boolean needCacheWhenFrameIntact() { + return true; + } + + public Expression implementResult(AggContext info, + AggResultContext result) { + WinAggResultContext winResult = (WinAggResultContext) result; + + List<RexNode> rexArgs = winResult.rexArguments(); + + ParameterExpression res = Expressions.parameter(0, info.returnType(), + result.currentBlock().newName("nth")); + + RexToLixTranslator currentRowTranslator = + winResult.rowTranslator( + winResult.computeIndex(Expressions.constant(0), SeekType.START)); + + Expression dstIndex = winResult.computeIndex( + Expressions.subtract( + currentRowTranslator.translate(rexArgs.get(1), int.class), + Expressions.constant(1)), SeekType.START); + + Expression rowInRange = winResult.rowInPartition(dstIndex); + + BlockBuilder thenBlock = result.nestBlock(); + Expression nthValue = winResult.rowTranslator(dstIndex) + .translate(rexArgs.get(0), res.type); + thenBlock.add(Expressions.statement(Expressions.assign(res, nthValue))); + result.exitBlock(); + BlockStatement thenBranch = thenBlock.toBlock(); + + Expression defaultValue = getDefaultValue(res.type); + + result.currentBlock().add(Expressions.declare(0, res, null)); + result.currentBlock().add( + Expressions.ifThenElse(rowInRange, thenBranch, + Expressions.statement(Expressions.assign(res, defaultValue)))); + return res; + } + } + /** Implementor for the {@code LEAD} and {@code LAG} windowed * aggregate functions. */ static class LeadLagImplementor implements WinAggImplementor { http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/core/src/main/java/org/apache/calcite/sql/SqlKind.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java index a2cb6d0..08cfbec 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -879,6 +879,9 @@ public enum SqlKind { /** The {@code NTILE} aggregate function. */ NTILE, + /** The {@code NTH_VALUE} aggregate function. */ + NTH_VALUE, + /** The {@code COLLECT} aggregate function. */ COLLECT, http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.java new file mode 100644 index 0000000..b1a2a86 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlNthValueAggFunction.java @@ -0,0 +1,38 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.sql.fun; + +import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.SqlFunctionCategory; +import org.apache.calcite.sql.SqlKind; +import org.apache.calcite.sql.type.OperandTypes; +import org.apache.calcite.sql.type.ReturnTypes; + +/** + * <code>NTH_VALUE</code> windowed aggregate function + * returns the value of an expression evaluated at the {@code n}th row of the + * window frame. + */ +public class SqlNthValueAggFunction extends SqlAggFunction { + public SqlNthValueAggFunction(SqlKind kind) { + super(kind.name(), null, kind, ReturnTypes.ARG0_NULLABLE_IF_EMPTY, + null, OperandTypes.ANY_NUMERIC, SqlFunctionCategory.NUMERIC, false, + true); + } +} + +// End SqlNthValueAggFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java index c9f8363..6ac1eb4 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java @@ -867,6 +867,12 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { new SqlFirstLastValueAggFunction(SqlKind.FIRST_VALUE); /** + * <code>NTH_VALUE</code> aggregate function. + */ + public static final SqlAggFunction NTH_VALUE = + new SqlNthValueAggFunction(SqlKind.NTH_VALUE); + + /** * <code>LEAD</code> aggregate function. */ public static final SqlAggFunction LEAD = @@ -2296,6 +2302,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { throw new AssertionError(comparisonKind); } } + } // End SqlStdOperatorTable.java http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java index e877b2a..c8476be 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java @@ -176,6 +176,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase { "KEYWORD(NEW)", "KEYWORD(NEXT)", "KEYWORD(NOT)", + "KEYWORD(NTH_VALUE)", "KEYWORD(NTILE)", "KEYWORD(NULL)", "KEYWORD(NULLIF)", http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index e96f1d2..b659009 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -3936,26 +3936,27 @@ public class SqlValidatorTest extends SqlValidatorTestCase { * <a href="https://issues.apache.org/jira/browse/CALCITE-1340";>[CALCITE-1340] * Window aggregates give invalid errors</a>. */ @Test public void testWindowFunctionsWithoutOver() { - winSql( - "select sum(empno) \n" - + "from emp \n" - + "group by deptno \n" + winSql("select sum(empno)\n" + + "from emp\n" + + "group by deptno\n" + "order by ^row_number()^") .fails("OVER clause is necessary for window functions"); - winSql( - "select ^rank()^ \n" + winSql("select ^rank()^\n" + "from emp") .fails("OVER clause is necessary for window functions"); // With [CALCITE-1340], the validator would see RANK without OVER, // mistakenly think this is an aggregating query, and wrongly complain // about the PARTITION BY: "Expression 'DEPTNO' is not being grouped" - winSql( - "select cume_dist() over w , ^rank()^\n" + winSql("select cume_dist() over w , ^rank()^\n" + "from emp \n" + "window w as (partition by deptno order by deptno)") .fails("OVER clause is necessary for window functions"); + + winSql("select ^nth_value(sal, 2)^\n" + + "from emp") + .fails("OVER clause is necessary for window functions"); } @Test public void testOverInPartitionBy() { @@ -4096,6 +4097,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase { winExp("rank() over (order by empno)").ok(); winExp("percent_rank() over (order by empno)").ok(); winExp("cume_dist() over (order by empno)").ok(); + winExp("nth_value(sal, 2) over (order by empno)").ok(); // rule 6a // ORDER BY required with RANK & DENSE_RANK @@ -7680,6 +7682,9 @@ public class SqlValidatorTest extends SqlValidatorTestCase { check("select FIRST_VALUE(sal) over (order by empno) from emp"); check("select FIRST_VALUE(ename) over (order by empno) from emp"); + + check("select NTH_VALUE(sal, 2) over (order by empno) from emp"); + check("select NTH_VALUE(ename, 2) over (order by empno) from emp"); } @Test public void testMinMaxFunctions() { http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/core/src/test/resources/sql/winagg.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/winagg.iq b/core/src/test/resources/sql/winagg.iq index 809db77..eac5822 100644 --- a/core/src/test/resources/sql/winagg.iq +++ b/core/src/test/resources/sql/winagg.iq @@ -430,4 +430,29 @@ limit 5; !ok +# NTH_VALUE +select emp."ENAME", emp."DEPTNO", + nth_value(emp."DEPTNO", 1) over() as "first_value", + nth_value(emp."DEPTNO", 2) over() as "second_value", + nth_value(emp."DEPTNO", 5) over() as "fifth_value", + nth_value(emp."DEPTNO", 8) over() as "eighth_value", + nth_value(emp."DEPTNO", 10) over() as "tenth_value" +from emp order by emp."ENAME"; ++-------+--------+-------------+--------------+-------------+--------------+-------------+ +| ENAME | DEPTNO | first_value | second_value | fifth_value | eighth_value | tenth_value | ++-------+--------+-------------+--------------+-------------+--------------+-------------+ +| Adam | 50 | 10 | 10 | 30 | 60 | | +| Alice | 30 | 10 | 10 | 30 | 60 | | +| Bob | 10 | 10 | 10 | 30 | 60 | | +| Eric | 20 | 10 | 10 | 30 | 60 | | +| Eve | 50 | 10 | 10 | 30 | 60 | | +| Grace | 60 | 10 | 10 | 30 | 60 | | +| Jane | 10 | 10 | 10 | 30 | 60 | | +| Susan | 30 | 10 | 10 | 30 | 60 | | +| Wilma | | 10 | 10 | 30 | 60 | | ++-------+--------+-------------+--------------+-------------+--------------+-------------+ +(9 rows) + +!ok + # End winagg.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/df774b9e/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index be3831f..3d1433f 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1538,6 +1538,7 @@ Not implemented: | LAST_VALUE(value) OVER window | Returns *value* evaluated at the row that is the last row of the window frame | LEAD(value, offset, default) OVER window | Returns *value* evaluated at the row that is *offset* rows after the current row within the partition; if there is no such row, instead returns *default*. Both *offset* and *default* are evaluated with respect to the current row. If omitted, *offset* defaults to 1 and *default* to NULL | LAG(value, offset, default) OVER window | Returns *value* evaluated at the row that is *offset* rows before the current row within the partition; if there is no such row, instead returns *default*. Both *offset* and *default* are evaluated with respect to the current row. If omitted, *offset* defaults to 1 and *default* to NULL +| NTH_VALUE(value, nth) OVER window | Returns *value* evaluated at the row that is the *n*th row of the window frame | NTILE(value) OVER window | Returns an integer ranging from 1 to *value*, dividing the partition as equally as possible Not implemented: @@ -1548,7 +1549,7 @@ Not implemented: * LAST_VALUE(value) IGNORE NULLS OVER window * PERCENT_RANK(value) OVER window * CUME_DIST(value) OVER window -* NTH_VALUE(value, nth) OVER window +* NTH_VALUE(value, nth) [ FROM { FIRST | LAST } ] IGNORE NULLS OVER window ### Grouping functions
