You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2018/07/09 07:49:51 UTC
[29/30] calcite git commit: [CALCITE-2383] NTH_VALUE window function
(Sergey Nuyanzin)
[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 <sn...@gmail.com>
Authored: Tue Jun 26 15:09:06 2018 +0300
Committer: Julian Hyde <jh...@apache.org>
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