This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 4a1da22e6e [CALCITE-5855] Implement frame exclusion in window functions
4a1da22e6e is described below
commit 4a1da22e6e223440de65f0130a7f947c85b8ff5b
Author: Itiel Sadeh <iti...@sqreamtech.com>
AuthorDate: Mon Jun 3 15:39:09 2024 +0300
[CALCITE-5855] Implement frame exclusion in window functions
---
core/src/main/codegen/templates/Parser.jj | 26 ++++-
.../adapter/enumerable/EnumerableWindow.java | 46 ++++++++-
.../calcite/adapter/enumerable/RexImpTable.java | 3 +-
.../calcite/adapter/enumerable/WinAggContext.java | 4 +
.../java/org/apache/calcite/rel/core/Window.java | 15 ++-
.../apache/calcite/rel/externalize/RelJson.java | 29 +++++-
.../apache/calcite/rel/logical/LogicalWindow.java | 16 ++-
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 17 +++-
.../rel/rules/ProjectWindowTransposeRule.java | 2 +-
.../calcite/rel/rules/ReduceExpressionsRule.java | 2 +-
.../java/org/apache/calcite/rex/RexBuilder.java | 50 +++++++++-
.../java/org/apache/calcite/rex/RexShuttle.java | 3 +-
.../java/org/apache/calcite/rex/RexWindow.java | 22 ++++-
.../org/apache/calcite/rex/RexWindowExclusion.java | 64 ++++++++++++
.../java/org/apache/calcite/sql/SqlWindow.java | 109 +++++++++++++++++++--
.../apache/calcite/sql2rel/SqlToRelConverter.java | 9 +-
.../java/org/apache/calcite/tools/RelBuilder.java | 32 ++++--
.../rel/logical/ToLogicalConverterTest.java | 27 +++++
core/src/test/resources/sql/winagg.iq | 64 ++++++++++++
.../calcite/piglet/PigRelToSqlConverter.java | 1 +
site/_docs/algebra.md | 1 +
site/_docs/reference.md | 17 +++-
.../apache/calcite/sql/parser/SqlParserTest.java | 16 +++
23 files changed, 532 insertions(+), 43 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index e8c59ba374..c11606d589 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -2736,6 +2736,7 @@ SqlWindow WindowSpecification() :
final SqlNodeList orderList;
final SqlLiteral isRows;
final SqlNode lowerBound, upperBound;
+ final SqlLiteral exclude;
final Span s, s1, s2;
final SqlLiteral allowPartial;
}
@@ -2768,9 +2769,11 @@ SqlWindow WindowSpecification() :
lowerBound = WindowRange()
{ upperBound = null; }
)
+ exclude = WindowExclusion()
|
{
isRows = SqlLiteral.createBoolean(false, SqlParserPos.ZERO);
+ exclude = SqlWindow.createExcludeNoOthers(getPos());
lowerBound = upperBound = null;
}
)
@@ -2787,7 +2790,7 @@ SqlWindow WindowSpecification() :
<RPAREN>
{
return SqlWindow.create(null, id, partitionList, orderList,
- isRows, lowerBound, upperBound, allowPartial, s.end(this));
+ isRows, lowerBound, upperBound, allowPartial, exclude,
s.end(this));
}
}
@@ -2826,6 +2829,27 @@ SqlNode WindowRange() :
)
}
+/** Parses an exclusion clause for WINDOW FRAME. */
+SqlLiteral WindowExclusion() :
+{
+}
+{
+ (
+ <EXCLUDE>
+ (
+ <CURRENT> <ROW> { return
SqlWindow.createExcludeCurrentRow(getPos()); }
+ |
+ <NO> <OTHERS> { return SqlWindow.createExcludeNoOthers(getPos()); }
+ |
+ <GROUP> { return SqlWindow.createExcludeGroup(getPos()); }
+ |
+ <TIES> { return SqlWindow.createExcludeTies(getPos()); }
+ )
+ |
+ { return SqlWindow.createExcludeNoOthers(SqlParserPos.ZERO); }
+ )
+}
+
/** Parses a QUALIFY clause for SELECT. */
SqlNode Qualify() :
{
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
index 3ee72a35d1..7a4b2687be 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
@@ -46,6 +46,7 @@ import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexWindowBound;
+import org.apache.calcite.rex.RexWindowExclusion;
import org.apache.calcite.runtime.SortedMultiMap;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.validate.SqlConformance;
@@ -301,7 +302,7 @@ public class EnumerableWindow extends Window implements
EnumerableRel {
}
declareAndResetState(typeFactory, builder, result, windowIdx, aggs,
- outputPhysType, outputRow);
+ outputPhysType, outputRow, group.exclude);
// There are assumptions that minX==0. If ever change this, look for
// frameRowCount, bounds checking, etc
@@ -403,8 +404,14 @@ public class EnumerableWindow extends Window implements
EnumerableRel {
group.lowerBound.isUnbounded() && group.lowerBound.isPreceding()
? Expressions.constant(false)
: Expressions.notEqual(startX, prevStart);
+
+ Expression isExcluding =
+ Expressions.constant(group.exclude !=
RexWindowExclusion.EXCLUDE_NO_OTHER);
+
+ // If there's exclude clause we need to recompute the window every time,
as rows can affect
+ // differently for the same frame.
Expression needRecomputeWindow =
- Expressions.orElse(lowerBoundCanChange,
+ Expressions.orElse(Expressions.orElse(isExcluding,
lowerBoundCanChange),
Expressions.lessThan(endX, prevEnd));
BlockStatement resetWindowState = builder6.toBlock();
@@ -458,15 +465,17 @@ public class EnumerableWindow extends Window implements
EnumerableRel {
};
implementAdd(aggs, builder7, resultContextBuilder, rexArguments, jDecl);
-
BlockStatement forBlock = builder7.toBlock();
+
+ // Don't run the aggregate function if current row is excluded
+ Statement exclude = buildExcludeGuard(group, comparator_, i_, jDecl,
rows_, forBlock);
if (!forBlock.statements.isEmpty()) {
// For instance, row_number does not use for loop to compute the value
Statement forAggLoop =
Expressions.for_(Arrays.asList(jDecl),
Expressions.lessThanOrEqual(jDecl.parameter, endX),
Expressions.preIncrementAssign(jDecl.parameter),
- forBlock);
+ exclude);
if (!hasRows.equals(Expressions.constant(true))) {
forAggLoop = Expressions.ifThen(hasRows, forAggLoop);
}
@@ -520,6 +529,29 @@ public class EnumerableWindow extends Window implements
EnumerableRel {
return implementor.result(inputPhysType, builder.toBlock());
}
+ private Statement buildExcludeGuard(Group group, Expression comparator,
+ ParameterExpression currentRow,
+ DeclarationStatement jDecl, Expression rows, BlockStatement forBlock) {
+ if (group.exclude == RexWindowExclusion.EXCLUDE_CURRENT_ROW) {
+ return Expressions.ifThen(Expressions.notEqual(currentRow,
jDecl.parameter), forBlock);
+ } else if (group.exclude == RexWindowExclusion.EXCLUDE_GROUP) {
+ return Expressions.ifThen(
+ Expressions.notEqual(Expressions.constant(0),
+ Expressions.call(comparator,
BuiltInMethod.COMPARATOR_COMPARE.method,
+ Expressions.arrayIndex(rows, currentRow),
+ Expressions.arrayIndex(rows, jDecl.parameter))), forBlock);
+ } else if (group.exclude == RexWindowExclusion.EXCLUDE_TIES) {
+ return Expressions.ifThen(
+ Expressions.or(Expressions.equal(currentRow, jDecl.parameter),
+ Expressions.notEqual(Expressions.constant(0),
+ Expressions.call(comparator,
BuiltInMethod.COMPARATOR_COMPARE.method,
+ Expressions.arrayIndex(rows, currentRow),
+ Expressions.arrayIndex(rows, jDecl.parameter)))),
forBlock);
+ } else {
+ return forBlock;
+ }
+ }
+
private static Function<BlockBuilder, WinAggFrameResultContext>
getBlockBuilderWinAggFrameResultContextFunction(
final JavaTypeFactory typeFactory, final SqlConformance conformance,
@@ -741,7 +773,7 @@ public class EnumerableWindow extends Window implements
EnumerableRel {
private void declareAndResetState(final JavaTypeFactory typeFactory,
BlockBuilder builder, final Result result, int windowIdx,
List<AggImpState> aggs, PhysType outputPhysType,
- List<Expression> outputRow) {
+ List<Expression> outputRow, RexWindowExclusion exclusion) {
for (final AggImpState agg : aggs) {
agg.context =
new WinAggContext() {
@@ -782,6 +814,10 @@ public class EnumerableWindow extends Window implements
EnumerableRel {
@Override public List<? extends Type> keyTypes() {
throw new UnsupportedOperationException();
}
+
+ @Override public RexWindowExclusion getExclude() {
+ return exclusion;
+ }
};
String aggName = "a" + agg.aggIdx;
if (CalciteSystemProperty.DEBUG.value()) {
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 a76eb0aa01..18a745e7c8 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
@@ -44,6 +44,7 @@ import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexPatternFieldRef;
+import org.apache.calcite.rex.RexWindowExclusion;
import org.apache.calcite.runtime.FlatLists;
import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.schema.FunctionContext;
@@ -1441,7 +1442,7 @@ public class RexImpTable {
break;
}
}
- if (!hasNullable) {
+ if (!hasNullable && info.getExclude() ==
RexWindowExclusion.EXCLUDE_NO_OTHER) {
justFrameRowCount = true;
return Collections.emptyList();
}
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/WinAggContext.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/WinAggContext.java
index aa96d09b77..28fe9a96f5 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/WinAggContext.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/WinAggContext.java
@@ -16,10 +16,14 @@
*/
package org.apache.calcite.adapter.enumerable;
+import org.apache.calcite.rex.RexWindowExclusion;
+
/**
* Marker interface to allow
* {@link org.apache.calcite.adapter.enumerable.AggImplementor}
* to tell if it is used in regular or windowed context.
*/
public interface WinAggContext extends AggContext {
+ /** The exclude clause of the group of the window function. */
+ RexWindowExclusion getExclude();
}
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Window.java
b/core/src/main/java/org/apache/calcite/rel/core/Window.java
index 4f76256930..86a8822dc7 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Window.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Window.java
@@ -39,6 +39,7 @@ import org.apache.calcite.rex.RexLocalRef;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexSlot;
import org.apache.calcite.rex.RexWindowBound;
+import org.apache.calcite.rex.RexWindowExclusion;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.ImmutableIntList;
@@ -223,8 +224,8 @@ public abstract class Window extends SingleRel implements
Hintable {
/**
* Group of windowed aggregate calls that have the same window specification.
*
- * <p>The specification is defined by an upper and lower bound, and
- * also has zero or more partitioning columns.
+ * <p>The specification is defined by an upper and lower bound, exclusion
clause,
+ * and also has zero or more partitioning columns.
*
* <p>A window is either logical or physical. A physical window is measured
* in terms of row count. A logical window is measured in terms of rows
@@ -247,6 +248,7 @@ public abstract class Window extends SingleRel implements
Hintable {
public final boolean isRows;
public final RexWindowBound lowerBound;
public final RexWindowBound upperBound;
+ public final RexWindowExclusion exclude;
public final RelCollation orderKeys;
private final String digest;
@@ -262,12 +264,14 @@ public abstract class Window extends SingleRel implements
Hintable {
boolean isRows,
RexWindowBound lowerBound,
RexWindowBound upperBound,
+ RexWindowExclusion exclude,
RelCollation orderKeys,
List<RexWinAggCall> aggCalls) {
this.keys = Objects.requireNonNull(keys, "keys");
this.isRows = isRows;
this.lowerBound = Objects.requireNonNull(lowerBound, "lowerBound");
this.upperBound = Objects.requireNonNull(upperBound, "upperBound");
+ this.exclude = exclude;
this.orderKeys = Objects.requireNonNull(orderKeys, "orderKeys");
this.aggCalls = ImmutableList.copyOf(aggCalls);
this.digest = computeString();
@@ -319,6 +323,9 @@ public abstract class Window extends SingleRel implements
Hintable {
buf.append(lowerBound);
buf.append(" and ");
buf.append(upperBound);
+ if (exclude != RexWindowExclusion.EXCLUDE_NO_OTHER) {
+ buf.append(" ").append(exclude);
+ }
}
if (!aggCalls.isEmpty()) {
if (buf.length() > i) {
@@ -358,7 +365,9 @@ public abstract class Window extends SingleRel implements
Hintable {
public boolean isAlwaysNonEmpty() {
int lowerKey = lowerBound.getOrderKey();
int upperKey = upperBound.getOrderKey();
- return lowerKey > -1 && lowerKey <= upperKey;
+ return lowerKey > -1 && lowerKey <= upperKey
+ && (exclude == RexWindowExclusion.EXCLUDE_NO_OTHER
+ || exclude == RexWindowExclusion.EXCLUDE_TIES);
}
/**
diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
index 3f7cbbca97..c1bcab8616 100644
--- a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
+++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java
@@ -50,6 +50,7 @@ import org.apache.calcite.rex.RexSlot;
import org.apache.calcite.rex.RexWindow;
import org.apache.calcite.rex.RexWindowBound;
import org.apache.calcite.rex.RexWindowBounds;
+import org.apache.calcite.rex.RexWindowExclusion;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlIdentifier;
@@ -772,11 +773,18 @@ public class RelJson {
upperBound = null;
physical = false;
}
+ final RexWindowExclusion exclude;
+ if (window.get("exclude") != null) {
+ exclude = toRexWindowExclusion((Map) window.get("exclude"));
+ } else {
+ exclude = RexWindowExclusion.EXCLUDE_NO_OTHER;
+ }
final boolean distinct = get((Map<String, Object>) map, "distinct");
return rexBuilder.makeOver(type, operator, rexOperands,
partitionKeys,
ImmutableList.copyOf(orderKeys),
requireNonNull(lowerBound, "lowerBound"),
requireNonNull(upperBound, "upperBound"),
+ requireNonNull(exclude, "exclude"),
physical,
true, false, distinct, false);
} else {
@@ -969,6 +977,25 @@ public class RelJson {
}
}
+ private @Nullable RexWindowExclusion toRexWindowExclusion(@Nullable
Map<String, Object> map) {
+ if (map == null) {
+ return null;
+ }
+ final String type = get(map, "type");
+ switch (type) {
+ case "CURRENT_ROW":
+ return RexWindowExclusion.EXCLUDE_CURRENT_ROW;
+ case "GROUP":
+ return RexWindowExclusion.EXCLUDE_GROUP;
+ case "TIES":
+ return RexWindowExclusion.EXCLUDE_TIES;
+ case "NO OTHERS":
+ return RexWindowExclusion.EXCLUDE_NO_OTHER;
+ default:
+ throw new UnsupportedOperationException(
+ "cannot convert " + type + " to rex window exclusion");
+ }
+ }
private @Nullable RexWindowBound toRexWindowBound(RelInput input,
@Nullable Map<String, Object> map) {
if (map == null) {
@@ -988,7 +1015,7 @@ public class RelJson {
case "FOLLOWING":
return RexWindowBounds.following(toRex(input, get(map, "offset")));
default:
- throw new UnsupportedOperationException("cannot convert type to rex
window bound " + type);
+ throw new UnsupportedOperationException("cannot convert " + type + " to
rex window bound");
}
}
diff --git
a/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
b/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
index 3bc23b31f8..3ea854331c 100644
--- a/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
+++ b/core/src/main/java/org/apache/calcite/rel/logical/LogicalWindow.java
@@ -34,6 +34,7 @@ import org.apache.calcite.rex.RexProgram;
import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.rex.RexWindow;
import org.apache.calcite.rex.RexWindowBound;
+import org.apache.calcite.rex.RexWindowExclusion;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Litmus;
@@ -68,8 +69,8 @@ public final class LogicalWindow extends Window {
*
* @param cluster Cluster
* @param traitSet Trait set
- * @param hints Hints for this node
- * @param input Input relational expression
+ * @param hints Hints for this node
+ * @param input Input relational expression
* @param constants List of constants that are additional inputs
* @param rowType Output row type
* @param groups Window groups
@@ -199,6 +200,7 @@ public final class LogicalWindow extends Window {
windowKey.isRows,
windowKey.lowerBound.accept(toInputRefs),
windowKey.upperBound.accept(toInputRefs),
+ windowKey.exclude,
windowKey.orderKeys,
aggCalls));
}
@@ -334,22 +336,25 @@ public final class LogicalWindow extends Window {
private final boolean isRows;
private final RexWindowBound lowerBound;
private final RexWindowBound upperBound;
+ private final RexWindowExclusion exclude;
WindowKey(
ImmutableBitSet groupSet,
RelCollation orderKeys,
boolean isRows,
RexWindowBound lowerBound,
- RexWindowBound upperBound) {
+ RexWindowBound upperBound,
+ RexWindowExclusion exclude) {
this.groupSet = groupSet;
this.orderKeys = orderKeys;
this.isRows = isRows;
this.lowerBound = lowerBound;
this.upperBound = upperBound;
+ this.exclude = exclude;
}
@Override public int hashCode() {
- return Objects.hash(groupSet, orderKeys, isRows, lowerBound, upperBound);
+ return Objects.hash(groupSet, orderKeys, isRows, lowerBound, upperBound,
exclude);
}
@Override public boolean equals(@Nullable Object obj) {
@@ -359,6 +364,7 @@ public final class LogicalWindow extends Window {
&& orderKeys.equals(((WindowKey) obj).orderKeys)
&& Objects.equals(lowerBound, ((WindowKey) obj).lowerBound)
&& Objects.equals(upperBound, ((WindowKey) obj).upperBound)
+ && exclude == ((WindowKey) obj).exclude
&& isRows == ((WindowKey) obj).isRows;
}
}
@@ -390,7 +396,7 @@ public final class LogicalWindow extends Window {
WindowKey windowKey =
new WindowKey(
groupSet, orderKeys, aggWindow.isRows(),
- aggWindow.getLowerBound(), aggWindow.getUpperBound());
+ aggWindow.getLowerBound(), aggWindow.getUpperBound(),
aggWindow.getExclude());
windowMap.put(windowKey, over);
}
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index 9950674ea1..23ca30e952 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -58,6 +58,7 @@ import org.apache.calcite.rex.RexUnknownAs;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexWindow;
import org.apache.calcite.rex.RexWindowBound;
+import org.apache.calcite.rex.RexWindowExclusion;
import org.apache.calcite.sql.JoinType;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlBasicCall;
@@ -958,6 +959,16 @@ public abstract class SqlImplementor {
}
}
+ public SqlLiteral toSql(RexWindowExclusion exclude) {
+ switch (exclude) {
+ case EXCLUDE_CURRENT_ROW: return SqlWindow.createExcludeCurrentRow(POS);
+ case EXCLUDE_GROUP: return SqlWindow.createExcludeGroup(POS);
+ case EXCLUDE_TIES: return SqlWindow.createExcludeTies(POS);
+ case EXCLUDE_NO_OTHER: return SqlWindow.createExcludeNoOthers(POS);
+ default: throw new AssertionError("Unsupported Window Exclusion: " +
exclude);
+ }
+ }
+
public List<SqlNode> toSql(Window.Group group, ImmutableList<RexLiteral>
constants,
int inputFieldCount) {
final List<SqlNode> rexOvers = new ArrayList<>();
@@ -981,7 +992,8 @@ public abstract class SqlImplementor {
SqlWindow.create(null, null,
new SqlNodeList(partitionKeys, POS),
new SqlNodeList(orderByKeys, POS),
- isRows, lowerBound, upperBound, allowPartial, POS);
+ isRows, lowerBound, upperBound, allowPartial,
+ toSql(group.exclude), POS);
if (aggFunction.allowsFraming()) {
lowerBound = createSqlWindowBound(group.lowerBound);
upperBound = createSqlWindowBound(group.upperBound);
@@ -1040,6 +1052,7 @@ public abstract class SqlImplementor {
SqlNode lowerBound = null;
SqlNode upperBound = null;
+ SqlLiteral exclude = toSql(rexWindow.getExclude());
if (sqlAggregateFunction.allowsFraming()) {
lowerBound = createSqlWindowBound(rexWindow.getLowerBound());
@@ -1048,7 +1061,7 @@ public abstract class SqlImplementor {
final SqlWindow sqlWindow =
SqlWindow.create(null, null, partitionList,
- orderList, isRows, lowerBound, upperBound, allowPartial, POS);
+ orderList, isRows, lowerBound, upperBound, allowPartial,
exclude, POS);
final List<SqlNode> nodeList = toSql(program, rexOver.getOperands());
return createOverCall(sqlAggregateFunction, nodeList, sqlWindow,
rexOver.isDistinct());
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java
index 385e2dbba4..1576bda75a 100644
---
a/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java
+++
b/core/src/main/java/org/apache/calcite/rel/rules/ProjectWindowTransposeRule.java
@@ -168,7 +168,7 @@ public class ProjectWindowTransposeRule
groups.add(
new Window.Group(keys.build(), group.isRows, group.lowerBound,
- group.upperBound, RelCollations.of(orderKeys), aggCalls));
+ group.upperBound, group.exclude, RelCollations.of(orderKeys),
aggCalls));
}
final LogicalWindow newLogicalWindow =
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
index 1cec66baf1..54dc204885 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/ReduceExpressionsRule.java
@@ -612,7 +612,7 @@ public abstract class ReduceExpressionsRule<C extends
ReduceExpressionsRule.Conf
: group.orderKeys;
groups.add(
new Window.Group(keys, group.isRows, group.lowerBound,
- group.upperBound, relCollation, aggCalls));
+ group.upperBound, group.exclude, relCollation, aggCalls));
}
if (reduced) {
call.transformTo(LogicalWindow
diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
index 7a7f247749..3ec66e94e5 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
@@ -394,13 +394,36 @@ public class RexBuilder {
boolean nullWhenCountZero,
boolean distinct,
boolean ignoreNulls) {
+ return makeOver(type, operator, exprs, partitionKeys, orderKeys,
lowerBound, upperBound,
+ RexWindowExclusion.EXCLUDE_NO_OTHER, rows, allowPartial,
nullWhenCountZero, distinct,
+ ignoreNulls);
+ }
+
+ /**
+ * Creates a call to a windowed agg.
+ */
+ public RexNode makeOver(
+ RelDataType type,
+ SqlAggFunction operator,
+ List<RexNode> exprs,
+ List<RexNode> partitionKeys,
+ ImmutableList<RexFieldCollation> orderKeys,
+ RexWindowBound lowerBound,
+ RexWindowBound upperBound,
+ RexWindowExclusion exclude,
+ boolean rows,
+ boolean allowPartial,
+ boolean nullWhenCountZero,
+ boolean distinct,
+ boolean ignoreNulls) {
final RexWindow window =
makeWindow(
partitionKeys,
orderKeys,
lowerBound,
upperBound,
- rows);
+ rows,
+ exclude);
final RexOver over =
new RexOver(type, operator, exprs, window, distinct, ignoreNulls);
RexNode result = over;
@@ -467,6 +490,28 @@ public class RexBuilder {
RexWindowBound lowerBound,
RexWindowBound upperBound,
boolean rows) {
+ return makeWindow(partitionKeys, orderKeys, lowerBound,
+ upperBound, rows, RexWindowExclusion.EXCLUDE_NO_OTHER);
+ }
+
+ /**
+ * Creates a window specification.
+ *
+ * @param partitionKeys Partition keys
+ * @param orderKeys Order keys
+ * @param lowerBound Lower bound
+ * @param upperBound Upper bound
+ * @param rows Whether physical. True if row-based, false if
+ * range-based
+ * @return window specification
+ */
+ public RexWindow makeWindow(
+ List<RexNode> partitionKeys,
+ ImmutableList<RexFieldCollation> orderKeys,
+ RexWindowBound lowerBound,
+ RexWindowBound upperBound,
+ boolean rows,
+ RexWindowExclusion exclude) {
if (lowerBound.isUnbounded() && lowerBound.isPreceding()
&& upperBound.isUnbounded() && upperBound.isFollowing()) {
// RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
@@ -480,7 +525,8 @@ public class RexBuilder {
orderKeys,
lowerBound,
upperBound,
- rows);
+ rows,
+ exclude);
}
/**
diff --git a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
index 0e14159fef..a836a4ac65 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexShuttle.java
@@ -89,7 +89,8 @@ public class RexShuttle implements RexVisitor<RexNode> {
clonedOrderKeys,
lowerBound,
upperBound,
- rows);
+ rows,
+ window.getExclude());
}
@Override public RexNode visitSubQuery(RexSubQuery subQuery) {
diff --git a/core/src/main/java/org/apache/calcite/rex/RexWindow.java
b/core/src/main/java/org/apache/calcite/rex/RexWindow.java
index ecf5391163..9c1b39f5b7 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexWindow.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexWindow.java
@@ -40,6 +40,7 @@ public class RexWindow {
public final ImmutableList<RexFieldCollation> orderKeys;
private final RexWindowBound lowerBound;
private final RexWindowBound upperBound;
+ private final RexWindowExclusion exclude;
private final boolean isRows;
private final String digest;
public final int nodeCount;
@@ -76,11 +77,13 @@ public class RexWindow {
List<RexFieldCollation> orderKeys,
RexWindowBound lowerBound,
RexWindowBound upperBound,
- boolean isRows) {
+ boolean isRows,
+ RexWindowExclusion exclude) {
this.partitionKeys = ImmutableList.copyOf(partitionKeys);
this.orderKeys = ImmutableList.copyOf(orderKeys);
this.lowerBound = Objects.requireNonNull(lowerBound, "lowerBound");
this.upperBound = Objects.requireNonNull(upperBound, "upperBound");
+ this.exclude = exclude;
this.isRows = isRows;
this.nodeCount = computeCodeCount();
this.digest = computeDigest();
@@ -90,6 +93,16 @@ public class RexWindow {
"use RANGE for unbounded, not ROWS");
}
+ RexWindow(
+ List<RexNode> partitionKeys,
+ List<RexFieldCollation> orderKeys,
+ RexWindowBound lowerBound,
+ RexWindowBound upperBound,
+ boolean isRows) {
+ this(partitionKeys, orderKeys, lowerBound, upperBound, isRows,
+ RexWindowExclusion.EXCLUDE_NO_OTHER);
+ }
+
//~ Methods ----------------------------------------------------------------
@Override public String toString() {
@@ -178,6 +191,9 @@ public class RexWindow {
.append(" AND ")
.append(upperBound);
}
+ if (exclude != RexWindowExclusion.EXCLUDE_NO_OTHER) {
+ sb.append(" ").append(exclude).append(" ");
+ }
return sb;
}
@@ -189,6 +205,10 @@ public class RexWindow {
return upperBound;
}
+ public RexWindowExclusion getExclude() {
+ return exclude;
+ }
+
public boolean isRows() {
return isRows;
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexWindowExclusion.java
b/core/src/main/java/org/apache/calcite/rex/RexWindowExclusion.java
new file mode 100644
index 0000000000..81df89b821
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rex/RexWindowExclusion.java
@@ -0,0 +1,64 @@
+/*
+ * 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.rex;
+
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlWindow;
+
+/**
+ * Representation of different kinds of exclude clause in window functions.
+ */
+public enum RexWindowExclusion {
+ EXCLUDE_NO_OTHER("EXCLUDE NO OTHER"),
+ EXCLUDE_CURRENT_ROW("EXCLUDE CURRENT ROW"),
+ EXCLUDE_TIES("EXCLUDE TIES"),
+ EXCLUDE_GROUP("EXCLUDE GROUP");
+
+ private final String sql;
+
+ RexWindowExclusion(String sql) {
+ this.sql = sql;
+ }
+
+ @Override public String toString() {
+ return sql;
+ }
+
+ /**
+ * Creates a window exclusion from a {@link SqlNode}.
+ *
+ * @param node SqlLiteral of the exclusion clause
+ * @return RexWindowExclusion
+ */
+ public static RexWindowExclusion create(SqlNode node) {
+ SqlLiteral exclude = (SqlLiteral) node;
+ if (SqlWindow.isExcludeCurrentRow(exclude)) {
+ return EXCLUDE_CURRENT_ROW;
+ }
+ if (SqlWindow.isExcludeNoOthers(exclude)) {
+ return EXCLUDE_NO_OTHER;
+ }
+ if (SqlWindow.isExcludeGroup(exclude)) {
+ return EXCLUDE_GROUP;
+ }
+ if (SqlWindow.isExcludeTies(exclude)) {
+ return EXCLUDE_TIES;
+ }
+ throw new AssertionError("Unexpected Exclusion clause");
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
b/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
index 7318241223..3f25c4d6e1 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlWindow.java
@@ -99,7 +99,10 @@ public class SqlWindow extends SqlCall {
/** The upper bound of the window. */
@Nullable SqlNode upperBound;
- /** Whether to allow partial results. It may be null. */
+ /** Exclude rows from the frame. */
+ SqlLiteral exclude;
+
+ /** Whether to allow partial results. It may be null. */
@Nullable SqlLiteral allowPartial;
private @Nullable SqlCall windowCall = null;
@@ -113,6 +116,17 @@ public class SqlWindow extends SqlCall {
@Nullable SqlIdentifier refName, SqlNodeList partitionList, SqlNodeList
orderList,
SqlLiteral isRows, @Nullable SqlNode lowerBound, @Nullable SqlNode
upperBound,
@Nullable SqlLiteral allowPartial) {
+ this(pos, declName, refName, partitionList, orderList,
+ isRows, lowerBound, upperBound, allowPartial,
createExcludeNoOthers(SqlParserPos.ZERO));
+ }
+
+ /**
+ * Creates a window.
+ */
+ public SqlWindow(SqlParserPos pos, @Nullable SqlIdentifier declName,
+ @Nullable SqlIdentifier refName, SqlNodeList partitionList, SqlNodeList
orderList,
+ SqlLiteral isRows, @Nullable SqlNode lowerBound, @Nullable SqlNode
upperBound,
+ @Nullable SqlLiteral allowPartial, SqlLiteral exclude) {
super(pos);
this.declName = declName;
this.refName = refName;
@@ -122,7 +136,10 @@ public class SqlWindow extends SqlCall {
this.lowerBound = lowerBound;
this.upperBound = upperBound;
this.allowPartial = allowPartial;
+ this.exclude = exclude;
+ assert exclude.symbolValue(Exclusion.class) == Exclusion.EXCLUDE_NO_OTHER
+ || (lowerBound != null || upperBound != null);
assert declName == null || declName.isSimple();
assert partitionList != null;
assert orderList != null;
@@ -132,6 +149,14 @@ public class SqlWindow extends SqlCall {
SqlNodeList partitionList, SqlNodeList orderList, SqlLiteral isRows,
@Nullable SqlNode lowerBound, @Nullable SqlNode upperBound, @Nullable
SqlLiteral allowPartial,
SqlParserPos pos) {
+ return create(declName, refName, partitionList, orderList, isRows,
lowerBound, upperBound,
+ allowPartial, createExcludeNoOthers(SqlParserPos.ZERO), pos);
+ }
+
+ public static SqlWindow create(@Nullable SqlIdentifier declName, @Nullable
SqlIdentifier refName,
+ SqlNodeList partitionList, SqlNodeList orderList, SqlLiteral isRows,
+ @Nullable SqlNode lowerBound, @Nullable SqlNode upperBound, @Nullable
SqlLiteral allowPartial,
+ SqlLiteral exclude, SqlParserPos pos) {
// If there's only one bound and it's 'FOLLOWING', make it the upper
// bound.
if (upperBound == null
@@ -141,7 +166,7 @@ public class SqlWindow extends SqlCall {
lowerBound = null;
}
return new SqlWindow(pos, declName, refName, partitionList, orderList,
- isRows, lowerBound, upperBound, allowPartial);
+ isRows, lowerBound, upperBound, allowPartial, exclude);
}
//~ Methods ----------------------------------------------------------------
@@ -157,7 +182,7 @@ public class SqlWindow extends SqlCall {
@SuppressWarnings("nullness")
@Override public List<SqlNode> getOperandList() {
return ImmutableNullableList.of(declName, refName, partitionList,
orderList,
- isRows, lowerBound, upperBound, allowPartial);
+ isRows, lowerBound, upperBound, allowPartial, exclude);
}
@SuppressWarnings("assignment.type.incompatible")
@@ -187,6 +212,9 @@ public class SqlWindow extends SqlCall {
case 7:
this.allowPartial = (SqlLiteral) operand;
break;
+ case 8:
+ this.exclude = (SqlLiteral) operand;
+ break;
default:
throw new AssertionError(i);
}
@@ -227,6 +255,10 @@ public class SqlWindow extends SqlCall {
this.upperBound = upperBound;
}
+ public SqlLiteral getExclude() {
+ return exclude;
+ }
+
/**
* Returns if the window is guaranteed to have rows.
* This is useful to refine data type of window aggregates.
@@ -364,6 +396,38 @@ public class SqlWindow extends SqlCall {
}
}
+ public static SqlLiteral createExcludeNoOthers(SqlParserPos pos) {
+ return Exclusion.EXCLUDE_NO_OTHER.symbol(pos);
+ }
+
+ public static SqlLiteral createExcludeCurrentRow(SqlParserPos pos) {
+ return Exclusion.EXCLUDE_CURRENT_ROW.symbol(pos);
+ }
+
+ public static SqlLiteral createExcludeTies(SqlParserPos pos) {
+ return Exclusion.EXCLUDE_TIES.symbol(pos);
+ }
+
+ public static SqlLiteral createExcludeGroup(SqlParserPos pos) {
+ return Exclusion.EXCLUDE_GROUP.symbol(pos);
+ }
+
+ public static boolean isExcludeNoOthers(SqlLiteral node) {
+ return node.symbolValue(Exclusion.class) == Exclusion.EXCLUDE_NO_OTHER;
+ }
+
+ public static boolean isExcludeCurrentRow(SqlLiteral node) {
+ return node.symbolValue(Exclusion.class) == Exclusion.EXCLUDE_CURRENT_ROW;
+ }
+
+ public static boolean isExcludeGroup(SqlLiteral node) {
+ return node.symbolValue(Exclusion.class) == Exclusion.EXCLUDE_GROUP;
+ }
+
+ public static boolean isExcludeTies(SqlLiteral node) {
+ return node.symbolValue(Exclusion.class) == Exclusion.EXCLUDE_TIES;
+ }
+
public static SqlNode createCurrentRow(SqlParserPos pos) {
return Bound.CURRENT_ROW.symbol(pos);
}
@@ -448,7 +512,9 @@ public class SqlWindow extends SqlCall {
// 711 rule 10e
final SqlNode lowerBound = that.getLowerBound();
final SqlNode upperBound = that.getUpperBound();
- if ((null != lowerBound) || (null != upperBound)) {
+ final SqlLiteral exclude = that.getExclude();
+ if ((null != lowerBound) || (null != upperBound)
+ || exclude.symbolValue(Exclusion.class) != Exclusion.EXCLUDE_NO_OTHER)
{
throw validator.newValidationError(that.isRows,
RESOURCE.refWindowWithFrame());
}
@@ -488,7 +554,8 @@ public class SqlWindow extends SqlCall {
isRowsNew,
lowerBoundNew,
upperBoundNew,
- allowPartialNew);
+ allowPartialNew,
+ exclude);
}
private static boolean setOperand(@Nullable SqlNode clonedOperand, @Nullable
SqlNode thatOperand,
@@ -793,6 +860,28 @@ public class SqlWindow extends SqlCall {
}
}
+ /**
+ * An enumeration of types of exclusion rows in a window: <code>EXCLUDE NO
OTHERS</code>,
+ * <code>EXCLUDE CURRENT ROW</code>, <code>EXCLUDE TIES</code> and
<code>EXCLUDE GROUP</code>.
+ */
+ enum Exclusion implements Symbolizable {
+ EXCLUDE_NO_OTHER("EXCLUDE NO OTHER"),
+ EXCLUDE_CURRENT_ROW("EXCLUDE CURRENT ROW"),
+ EXCLUDE_TIES("EXCLUDE TIES"),
+ EXCLUDE_GROUP("EXCLUDE GROUP");
+
+ private final String sql;
+
+ Exclusion(String sql) {
+ this.sql = sql;
+ }
+
+ @Override public String toString() {
+ return sql;
+ }
+
+ }
+
/**
* An enumeration of types of bounds in a window: <code>CURRENT ROW</code>,
* <code>UNBOUNDED PRECEDING</code>, and <code>UNBOUNDED FOLLOWING</code>.
@@ -831,7 +920,7 @@ public class SqlWindow extends SqlCall {
SqlParserPos pos,
@Nullable SqlNode... operands) {
assert functionQualifier == null;
- assert operands.length == 8;
+ assert operands.length == 9;
return create(
(SqlIdentifier) operands[0],
(SqlIdentifier) operands[1],
@@ -841,6 +930,7 @@ public class SqlWindow extends SqlCall {
operands[5],
operands[6],
(SqlLiteral) operands[7],
+ (SqlLiteral) operands[8],
pos);
}
@@ -893,6 +983,7 @@ public class SqlWindow extends SqlCall {
}
SqlNode lowerBound = window.lowerBound;
SqlNode upperBound = window.upperBound;
+ SqlLiteral exclude = window.exclude;
if (lowerBound == null) {
// No ROWS or RANGE clause
} else if (upperBound == null) {
@@ -902,6 +993,9 @@ public class SqlWindow extends SqlCall {
writer.sep("RANGE");
}
lowerBound.unparse(writer, 0, 0);
+ if (!isExcludeNoOthers(exclude)) {
+ exclude.unparse(writer, 0, 0);
+ }
} else {
if (window.isRows()) {
writer.sep("ROWS BETWEEN");
@@ -911,6 +1005,9 @@ public class SqlWindow extends SqlCall {
lowerBound.unparse(writer, 0, 0);
writer.keyword("AND");
upperBound.unparse(writer, 0, 0);
+ if (!isExcludeNoOthers(exclude)) {
+ exclude.unparse(writer, 0, 0);
+ }
}
// ALLOW PARTIAL/DISALLOW PARTIAL
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index e9b9eff95e..b29bf3884c 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -94,6 +94,7 @@ import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexWindowBound;
import org.apache.calcite.rex.RexWindowBounds;
+import org.apache.calcite.rex.RexWindowExclusion;
import org.apache.calcite.runtime.PairList;
import org.apache.calcite.schema.ColumnStrategy;
import org.apache.calcite.schema.ModifiableTable;
@@ -2286,6 +2287,7 @@ public class SqlToRelConverter {
"Relation should have sort key for implicit ORDER BY");
}
}
+ final RexWindowExclusion exclude =
RexWindowExclusion.create(window.getExclude());
final ImmutableList.Builder<RexNode> orderKeys =
ImmutableList.builder();
@@ -2318,6 +2320,7 @@ public class SqlToRelConverter {
new HistogramShuttle(partitionKeys.build(), orderKeys.build(), rows,
RexWindowBounds.create(sqlLowerBound, lowerBound),
RexWindowBounds.create(sqlUpperBound, upperBound),
+ exclude,
window.isAllowPartial(), isDistinct, ignoreNulls);
return rexAgg.accept(visitor);
} finally {
@@ -5982,6 +5985,7 @@ public class SqlToRelConverter {
private final ImmutableList<RexNode> orderKeys;
private final RexWindowBound lowerBound;
private final RexWindowBound upperBound;
+ private final RexWindowExclusion exclude;
private final boolean rows;
private final boolean allowPartial;
private final boolean distinct;
@@ -5989,12 +5993,13 @@ public class SqlToRelConverter {
HistogramShuttle(ImmutableList<RexNode> partitionKeys,
ImmutableList<RexNode> orderKeys, boolean rows,
- RexWindowBound lowerBound, RexWindowBound upperBound,
+ RexWindowBound lowerBound, RexWindowBound upperBound,
RexWindowExclusion exclude,
boolean allowPartial, boolean distinct, boolean ignoreNulls) {
this.partitionKeys = partitionKeys;
this.orderKeys = orderKeys;
this.lowerBound = lowerBound;
this.upperBound = upperBound;
+ this.exclude = exclude;
this.rows = rows;
this.allowPartial = allowPartial;
this.distinct = distinct;
@@ -6045,6 +6050,7 @@ public class SqlToRelConverter {
.let(c ->
rows ? c.rowsBetween(lowerBound, upperBound)
: c.rangeBetween(lowerBound, upperBound))
+ .exclude(exclude)
.allowPartial(allowPartial)
.toRex();
@@ -6085,6 +6091,7 @@ public class SqlToRelConverter {
.let(c ->
rows ? c.rowsBetween(lowerBound, upperBound)
: c.rangeBetween(lowerBound, upperBound))
+ .exclude(exclude)
.allowPartial(allowPartial)
.nullWhenCountZero(needSum0)
.toRex();
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 7456c2e1de..f0ae2f37cd 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -84,6 +84,7 @@ import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexWindowBound;
import org.apache.calcite.rex.RexWindowBounds;
+import org.apache.calcite.rex.RexWindowExclusion;
import org.apache.calcite.runtime.Hook;
import org.apache.calcite.runtime.ImmutablePairList;
import org.apache.calcite.runtime.PairList;
@@ -4524,6 +4525,9 @@ public class RelBuilder {
return rangeBetween(RexWindowBounds.UNBOUNDED_PRECEDING, upper);
}
+ /** Sets the frame to EXCLUDE rows; default to EXCLUDE_NO_OTHER. */
+ OverCall exclude(RexWindowExclusion exclude);
+
/** Sets a RANGE window with lower and upper bounds,
* equivalent to SQL {@code RANGE BETWEEN lower ROW AND upper}. */
OverCall rangeBetween(RexWindowBound lower, RexWindowBound upper);
@@ -4553,6 +4557,7 @@ public class RelBuilder {
private final boolean rows;
private final RexWindowBound lowerBound;
private final RexWindowBound upperBound;
+ private final RexWindowExclusion exclude;
private final ImmutableList<RexNode> partitionKeys;
private final ImmutableList<RexFieldCollation> sortKeys;
private final SqlAggFunction op;
@@ -4563,7 +4568,7 @@ public class RelBuilder {
@Nullable String alias, ImmutableList<RexNode> partitionKeys,
ImmutableList<RexFieldCollation> sortKeys, boolean rows,
RexWindowBound lowerBound, RexWindowBound upperBound,
- boolean nullWhenCountZero, boolean allowPartial) {
+ boolean nullWhenCountZero, boolean allowPartial, RexWindowExclusion
exclude) {
this.op = op;
this.distinct = distinct;
this.operands = operands;
@@ -4576,6 +4581,7 @@ public class RelBuilder {
this.rows = rows;
this.lowerBound = lowerBound;
this.upperBound = upperBound;
+ this.exclude = exclude;
}
/** Creates an OverCallImpl with default settings. */
@@ -4584,7 +4590,7 @@ public class RelBuilder {
@Nullable String alias) {
this(op, distinct, operands, ignoreNulls, alias, ImmutableList.of(),
ImmutableList.of(), true, RexWindowBounds.UNBOUNDED_PRECEDING,
- RexWindowBounds.UNBOUNDED_FOLLOWING, false, true);
+ RexWindowBounds.UNBOUNDED_FOLLOWING, false, true,
RexWindowExclusion.EXCLUDE_NO_OTHER);
}
@Override public OverCall partitionBy(
@@ -4599,13 +4605,13 @@ public class RelBuilder {
private OverCall partitionBy_(ImmutableList<RexNode> partitionKeys) {
return new OverCallImpl(op, distinct, operands, ignoreNulls, alias,
partitionKeys, sortKeys, rows, lowerBound, upperBound,
- nullWhenCountZero, allowPartial);
+ nullWhenCountZero, allowPartial, exclude);
}
private OverCall orderBy_(ImmutableList<RexFieldCollation> sortKeys) {
return new OverCallImpl(op, distinct, operands, ignoreNulls, alias,
partitionKeys, sortKeys, rows, lowerBound, upperBound,
- nullWhenCountZero, allowPartial);
+ nullWhenCountZero, allowPartial, exclude);
}
@Override public OverCall orderBy(Iterable<? extends RexNode> sortKeys) {
@@ -4626,32 +4632,38 @@ public class RelBuilder {
RexWindowBound upperBound) {
return new OverCallImpl(op, distinct, operands, ignoreNulls, alias,
partitionKeys, sortKeys, true, lowerBound, upperBound,
- nullWhenCountZero, allowPartial);
+ nullWhenCountZero, allowPartial, exclude);
}
@Override public OverCall rangeBetween(RexWindowBound lowerBound,
RexWindowBound upperBound) {
return new OverCallImpl(op, distinct, operands, ignoreNulls, alias,
partitionKeys, sortKeys, false, lowerBound, upperBound,
- nullWhenCountZero, allowPartial);
+ nullWhenCountZero, allowPartial, exclude);
+ }
+
+ @Override public OverCall exclude(RexWindowExclusion exclude) {
+ return new OverCallImpl(op, distinct, operands, ignoreNulls, alias,
+ partitionKeys, sortKeys, rows, lowerBound, upperBound,
+ nullWhenCountZero, allowPartial, exclude);
}
@Override public OverCall allowPartial(boolean allowPartial) {
return new OverCallImpl(op, distinct, operands, ignoreNulls, alias,
partitionKeys, sortKeys, rows, lowerBound, upperBound,
- nullWhenCountZero, allowPartial);
+ nullWhenCountZero, allowPartial, exclude);
}
@Override public OverCall nullWhenCountZero(boolean nullWhenCountZero) {
return new OverCallImpl(op, distinct, operands, ignoreNulls, alias,
partitionKeys, sortKeys, rows, lowerBound, upperBound,
- nullWhenCountZero, allowPartial);
+ nullWhenCountZero, allowPartial, exclude);
}
@Override public RexNode as(String alias) {
return new OverCallImpl(op, distinct, operands, ignoreNulls, alias,
partitionKeys, sortKeys, rows, lowerBound, upperBound,
- nullWhenCountZero, allowPartial).toRex();
+ nullWhenCountZero, allowPartial, exclude).toRex();
}
@Override public RexNode toRex() {
@@ -4665,7 +4677,7 @@ public class RelBuilder {
final RelDataType type = op.inferReturnType(bind);
final RexNode over = getRexBuilder()
.makeOver(type, op, operands, partitionKeys, sortKeys,
- lowerBound, upperBound, rows, allowPartial, nullWhenCountZero,
+ lowerBound, upperBound, exclude, rows, allowPartial,
nullWhenCountZero,
distinct, ignoreNulls);
return aliasMaybe(over, alias);
}
diff --git
a/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
index a2a486570f..accd611a86 100644
---
a/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/logical/ToLogicalConverterTest.java
@@ -46,6 +46,8 @@ import com.google.common.collect.ImmutableSet;
import org.checkerframework.checker.nullness.qual.Nullable;
import org.junit.jupiter.api.Test;
+import java.util.Locale;
+
import static org.apache.calcite.test.Matchers.hasTree;
import static org.hamcrest.CoreMatchers.is;
@@ -467,6 +469,31 @@ class ToLogicalConverterTest {
verify(rel(sql), expectedPhysical, expectedLogical);
}
+ void testWindowExcludeImp(String excludeClause, String
expectedExcludeString) {
+ // forbiddenApiTest requires to use Locale even though it's no needed here.
+ String sql = String.format(Locale.ROOT, "SELECT sum(\"salary\") over
(order by \"hire_date\" "
+ + "rows between unbounded preceding and current row %s) FROM
\"employee\"", excludeClause);
+ String expectedPhysical =
+ String.format(Locale.ROOT, "EnumerableProject($0=[$17])\n"
+ + " EnumerableWindow(window#0=[window(order by [9] rows between"
+ + " UNBOUNDED PRECEDING and CURRENT ROW %saggs [SUM($11)])])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcTableScan(table=[[foodmart, employee]])\n",
expectedExcludeString);
+ String expectedLogical =
+ String.format(Locale.ROOT, "LogicalProject($0=[$17])\n"
+ + " LogicalWindow(window#0=[window(order by [9] rows between"
+ + " UNBOUNDED PRECEDING and CURRENT ROW %saggs [SUM($11)])])\n"
+ + " LogicalTableScan(table=[[foodmart, employee]])\n",
expectedExcludeString);
+ verify(rel(sql), expectedPhysical, expectedLogical);
+ }
+
+ @Test void testWindowExclude() {
+ testWindowExcludeImp("exclude current row", "EXCLUDE CURRENT ROW ");
+ testWindowExcludeImp("exclude group", "EXCLUDE GROUP ");
+ testWindowExcludeImp("exclude ties", "EXCLUDE TIES ");
+ testWindowExcludeImp("exclude no others", "");
+ }
+
@Test void testTableModify() {
final String sql = "insert into \"employee\" select * from \"employee\"";
final String expectedPhysical = ""
diff --git a/core/src/test/resources/sql/winagg.iq
b/core/src/test/resources/sql/winagg.iq
index ed02f556f4..963c38eb67 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -839,5 +839,69 @@ EnumerableCalc(expr#0..3=[{inputs}], GENDER=[$t2],
$1=[$t3])
+--------+--------+
(6 rows)
+!ok
+
+# [CALCITE-5855] Implement frame exclusion in window functions
+# Verified against PostgreSQL
+!use scott
+
+select empno,
+ avg(comm) over (order by empno rows 2 preceding exclude current row) as e1,
+ avg(comm) over (order by empno rows 2 preceding exclude group) as e2,
+ avg(comm) over (order by empno rows 2 preceding exclude ties) as e3,
+ avg(comm) over (order by empno rows 2 preceding exclude no others) as e4,
+ avg(comm) over (order by empno rows 2 preceding) as e5
+from emp
+order by 1;
++-------+---------+---------+---------+---------+---------+
+| EMPNO | E1 | E2 | E3 | E4 | E5 |
++-------+---------+---------+---------+---------+---------+
+| 7369 | | | | | |
+| 7499 | | | 300.00 | 300.00 | 300.00 |
+| 7521 | 300.00 | 300.00 | 400.00 | 400.00 | 400.00 |
+| 7566 | 400.00 | 400.00 | 400.00 | 400.00 | 400.00 |
+| 7654 | 500.00 | 500.00 | 950.00 | 950.00 | 950.00 |
+| 7698 | 1400.00 | 1400.00 | 1400.00 | 1400.00 | 1400.00 |
+| 7782 | 1400.00 | 1400.00 | 1400.00 | 1400.00 | 1400.00 |
+| 7788 | | | | | |
+| 7839 | | | | | |
+| 7844 | | | 0.00 | 0.00 | 0.00 |
+| 7876 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
+| 7900 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
+| 7902 | | | | | |
+| 7934 | | | | | |
++-------+---------+---------+---------+---------+---------+
+(14 rows)
+
+!ok
+
+select empno,
+ stddev(comm) over (order by empno rows between 2 preceding and 5 following
exclude current row) as e1,
+ count(*) over (order by empno rows between 2 preceding and 5 following
exclude group) as e2,
+ sum(sal) over (order by empno rows between 2 preceding and unbounded
following exclude ties) as e3,
+ sum(sal) over (order by empno rows between unbounded preceding and unbounded
following exclude no others) as e4,
+ sum(sal) over (order by empno rows between unbounded preceding and unbounded
following) as e5
+from emp
+order by 1;
++-------+-------------------------------------------------+----+----------+----------+----------+
+| EMPNO | E1 | E2 | E3 | E4
| E5 |
++-------+-------------------------------------------------+----+----------+----------+----------+
+| 7369 | 585.9465277082316561063635163009166717529296875 | 5 | 29025.00 |
29025.00 | 29025.00 |
+| 7499 | 636.3961030678927954795653931796550750732421875 | 6 | 29025.00 |
29025.00 | 29025.00 |
+| 7521 | 777.8174593052023055861354805529117584228515625 | 7 | 29025.00 |
29025.00 | 29025.00 |
+| 7566 | 585.9465277082316561063635163009166717529296875 | 7 | 28225.00 |
29025.00 | 29025.00 |
+| 7654 | 353.5533905932737752664252184331417083740234375 | 7 | 26625.00 |
29025.00 | 29025.00 |
+| 7698 | 989.949493661166570745990611612796783447265625 | 7 | 25375.00 |
29025.00 | 29025.00 |
+| 7782 | 989.949493661166570745990611612796783447265625 | 7 | 22400.00 |
29025.00 | 29025.00 |
+| 7788 | | 7 | 21150.00 |
29025.00 | 29025.00 |
+| 7839 | | 7 | 18300.00 |
29025.00 | 29025.00 |
+| 7844 | | 6 | 15850.00 |
29025.00 | 29025.00 |
+| 7876 | | 5 | 12850.00 |
29025.00 | 29025.00 |
+| 7900 | | 4 | 7850.00 |
29025.00 | 29025.00 |
+| 7902 | | 3 | 6350.00 |
29025.00 | 29025.00 |
+| 7934 | | 2 | 5250.00 |
29025.00 | 29025.00 |
++-------+-------------------------------------------------+----+----------+----------+----------+
+(14 rows)
+
!ok
# End winagg.iq
diff --git
a/piglet/src/main/java/org/apache/calcite/piglet/PigRelToSqlConverter.java
b/piglet/src/main/java/org/apache/calcite/piglet/PigRelToSqlConverter.java
index 752cd7ccf9..2b6ea23de2 100644
--- a/piglet/src/main/java/org/apache/calcite/piglet/PigRelToSqlConverter.java
+++ b/piglet/src/main/java/org/apache/calcite/piglet/PigRelToSqlConverter.java
@@ -114,6 +114,7 @@ public class PigRelToSqlConverter extends RelToSqlConverter
{
builder.context.toSql(winGroup.lowerBound),
builder.context.toSql(winGroup.upperBound),
null, // allowPartial
+ builder.context.toSql(winGroup.exclude),
POS);
for (Window.RexWinAggCall winFunc : winGroup.aggCalls) {
diff --git a/site/_docs/algebra.md b/site/_docs/algebra.md
index bc78331a04..30a6b8175b 100644
--- a/site/_docs/algebra.md
+++ b/site/_docs/algebra.md
@@ -540,6 +540,7 @@ To further modify the `OverCall`, call its methods:
| `rowsFrom(lower)` | Creates a row-based window bounded below, `ROWS
BETWEEN lower AND CURRENT ROW`
| `rowsTo(upper)` | Creates a row-based window bounded above, `ROWS
BETWEEN CURRENT ROW AND upper`
| `rowsBetween(lower, upper)` | Creates a rows-based window, `ROWS BETWEEN
lower AND upper`
+| `exclude(excludeType)` | Exclude certain rows from the frame (see SQL
`EXCLUDE`)
| `partitionBy(expr...)`<br/>`partitionBy(exprList)` | Partitions the window
on the given expressions (see SQL `PARTITION BY`)
| `orderBy(expr...)`<br/>`sort(exprList)` | Sorts the rows in the window (see
SQL `ORDER BY`)
| `allowPartial(b)` | Sets whether to allow partial width windows; default
true
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index c74832c9ac..f9edf9c4b9 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -331,10 +331,17 @@ windowSpec:
[ ORDER BY orderItem [, orderItem ]* ]
[ PARTITION BY expression [, expression ]* ]
[
- RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING }
- | ROWS numericExpression { PRECEDING | FOLLOWING }
+ RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING [exclude]}
+ | ROWS numericExpression { PRECEDING | FOLLOWING [exclude] }
]
')'
+
+exclude:
+ EXCLUDE NO OTHERS
+ | EXCLUDE CURRENT ROW
+ | EXCLUDE GROUP
+ | EXCLUDE TIES
+
{% endhighlight %}
In *insert*, if the INSERT or UPSERT statement does not specify a
@@ -1968,6 +1975,12 @@ windowedAggregateCall:
where *agg* is one of the operators in the following table, or a user-defined
aggregate function.
+The *exclude* clause can be one of:
+- EXCLUDE NO OTHER: Does not exclude any row from the frame. This is the
default.
+- EXCLUDE CURRENT ROW: Exclude the current row from the frame.
+- EXCLUDE GROUP: Exclude the current row and its ordering peers from the frame.
+- EXCLUDE TIES: Exclude all the ordering peers of the current row, but not the
current row itself.
+
`DISTINCT`, `FILTER` and `WITHIN GROUP` are as described for aggregate
functions.
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 092690cd9b..1fdf6c95a5 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -5780,6 +5780,22 @@ public class SqlParserTest {
+ "FROM `BIDS`");
}
+ @Test void testWindowSpecExclusion() {
+ sql("select sum(x) over (order by x rows between 2 preceding and 2
following exclude group) "
+ + "from emp")
+ .ok("SELECT (SUM(`X`) OVER (ORDER BY `X` ROWS BETWEEN 2 PRECEDING AND
2 "
+ + "FOLLOWING EXCLUDE GROUP))\n"
+ + "FROM `EMP`");
+ // doesn't parse without frame definition
+ sql("select sum(x) over (order by x ^exclude^ current row) from bids")
+ .fails("(?s).*Encountered \"exclude\".*");
+ // EXCLUDE NO OTHERS is the default behavior, and omitted from UNPARSE
+ sql("select sum(x) over (order by x rows between 2 preceding and 2
following exclude no others)"
+ + " from emp")
+ .ok("SELECT (SUM(`X`) OVER (ORDER BY `X` ROWS BETWEEN 2 PRECEDING AND
2 FOLLOWING))\n"
+ + "FROM `EMP`");
+ }
+
@Test void testQualify() {
final String sql = "SELECT empno, ename,\n"
+ " ROW_NUMBER() over (partition by ename order by deptno) as rn\n"
