calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [3/3] calcite git commit: [CALCITE-1615] Support HOP and SESSION in the GROUP BY clause (Julian Hyde and Haohui Mai)
Date Tue, 28 Feb 2017 03:15:26 GMT
[CALCITE-1615] Support HOP and SESSION in the GROUP BY clause (Julian Hyde and Haohui Mai)

Add documentation for grouped window functions (also known as group functions) and grouped
auxiliary functions.

Close apache/calcite#375


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/49888a6c
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/49888a6c
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/49888a6c

Branch: refs/heads/master
Commit: 49888a6c581f6243a5b3f1a48ea2c9edd6aac9d5
Parents: 2218343
Author: Haohui Mai <wheat9@apache.org>
Authored: Thu Feb 16 16:09:58 2017 -0800
Committer: Julian Hyde <jhyde@apache.org>
Committed: Mon Feb 27 16:32:40 2017 -0800

----------------------------------------------------------------------
 .../java/org/apache/calcite/sql/SqlKind.java    | 26 ++++++++++-
 .../calcite/sql/fun/SqlStdOperatorTable.java    | 45 +++++++++++++++++++-
 .../calcite/sql/validate/SqlValidatorUtil.java  |  7 ++-
 .../apache/calcite/test/SqlValidatorTest.java   | 33 ++++++++++++++
 .../calcite/test/SqlToRelConverterTest.xml      | 35 +++++++++++++++
 site/_docs/reference.md                         | 30 +++++++++++++
 6 files changed, 171 insertions(+), 5 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/49888a6c/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 ec7053a..e3cff7c 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -780,13 +780,35 @@ public enum SqlKind {
   TUMBLE,
 
   /** The {@code TUMBLE_START} auxiliary function of
-   * the {@link #TUMBLE} function. */
+   * the {@link #TUMBLE} group function. */
   TUMBLE_START,
 
   /** The {@code TUMBLE_END} auxiliary function of
-   * the {@link #TUMBLE} function. */
+   * the {@link #TUMBLE} group function. */
   TUMBLE_END,
 
+  /** The {@code HOP} group function. */
+  HOP,
+
+  /** The {@code HOP_START} auxiliary function of
+   * the {@link #HOP} group function. */
+  HOP_START,
+
+  /** The {@code HOP_END} auxiliary function of
+   * the {@link #HOP} group function. */
+  HOP_END,
+
+  /** The {@code SESSION} group function. */
+  SESSION,
+
+  /** The {@code SESSION_START} auxiliary function of
+   * the {@link #SESSION} group function. */
+  SESSION_START,
+
+  /** The {@code SESSION_END} auxiliary function of
+   * the {@link #SESSION} group function. */
+  SESSION_END,
+
   // DDL and session control statements follow. The list is not exhaustive: feel
   // free to add more.
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/49888a6c/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 8326f58..764a04f 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
@@ -1852,19 +1852,54 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable
{
         }
       };
 
+  /** The {@code TUMBLE} group function. */
   public static final SqlGroupFunction TUMBLE =
       new SqlGroupFunction(SqlKind.TUMBLE, null,
           OperandTypes.or(OperandTypes.DATETIME_INTERVAL,
               OperandTypes.DATETIME_INTERVAL_TIME));
 
-  /** The TUMBLE_START auxiliary function of the TUMBLE group function. */
+  /** The {@code TUMBLE_START} auxiliary function of
+   * the {@code TUMBLE} group function. */
   public static final SqlFunction TUMBLE_START =
       TUMBLE.auxiliary(SqlKind.TUMBLE_START);
 
-  /** The TUMBLE_END auxiliary function of the TUMBLE group function. */
+  /** The {@code TUMBLE_END} auxiliary function of
+   * the {@code TUMBLE} group function. */
   public static final SqlFunction TUMBLE_END =
       TUMBLE.auxiliary(SqlKind.TUMBLE_END);
 
+  /** The {@code HOP} group function. */
+  public static final SqlGroupFunction HOP =
+      new SqlGroupFunction(SqlKind.HOP, null,
+          OperandTypes.or(OperandTypes.DATETIME_INTERVAL_INTERVAL,
+              OperandTypes.DATETIME_INTERVAL_INTERVAL_TIME));
+
+  /** The {@code HOP_START} auxiliary function of
+   * the {@code HOP} group function. */
+  public static final SqlFunction HOP_START =
+      HOP.auxiliary(SqlKind.HOP_START);
+
+  /** The {@code HOP_END} auxiliary function of
+   * the {@code HOP} group function. */
+  public static final SqlFunction HOP_END =
+      HOP.auxiliary(SqlKind.HOP_END);
+
+  /** The {@code SESSION} group function. */
+  public static final SqlGroupFunction SESSION =
+      new SqlGroupFunction(SqlKind.SESSION, null,
+          OperandTypes.or(OperandTypes.DATETIME_INTERVAL,
+              OperandTypes.DATETIME_INTERVAL_TIME));
+
+  /** The {@code SESSION_START} auxiliary function of
+   * the {@code SESSION} group function. */
+  public static final SqlFunction SESSION_START =
+      SESSION.auxiliary(SqlKind.SESSION_START);
+
+  /** The {@code SESSION_END} auxiliary function of
+   * the {@code SESSION} group function. */
+  public static final SqlFunction SESSION_END =
+      SESSION.auxiliary(SqlKind.SESSION_END);
+
   //~ Methods ----------------------------------------------------------------
 
   /**
@@ -1888,6 +1923,12 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable
{
     case TUMBLE_START:
     case TUMBLE_END:
       return TUMBLE;
+    case HOP_START:
+    case HOP_END:
+      return HOP;
+    case SESSION_START:
+    case SESSION_END:
+      return SESSION;
     default:
       return null;
     }

http://git-wip-us.apache.org/repos/asf/calcite/blob/49888a6c/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
index 315c41f..c643a83 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorUtil.java
@@ -564,7 +564,8 @@ public class SqlValidatorUtil {
   /** Analyzes an expression in a GROUP BY clause.
    *
    * <p>It may be an expression, an empty list (), or a call to
-   * {@code GROUPING SETS}, {@code CUBE}, {@code ROLLUP} or {@code TUMBLE}.
+   * {@code GROUPING SETS}, {@code CUBE}, {@code ROLLUP},
+   * {@code TUMBLE}, {@code HOP} or {@code SESSION}.
    *
    * <p>Each group item produces a list of group sets, which are written to
    * {@code topBuilder}. To find the grouping sets of the query, we will take
@@ -600,7 +601,9 @@ public class SqlValidatorUtil {
         return;
       }
       // fall through
+    case HOP:
     case TUMBLE:
+    case SESSION:
     case GROUPING_SETS:
     default:
       builder = ImmutableList.builder();
@@ -728,7 +731,9 @@ public class SqlValidatorUtil {
     }
 
     switch (expr.getKind()) {
+    case HOP:
     case TUMBLE:
+    case SESSION:
       groupAnalyzer.extraExprs.add(expr);
       break;
     }

http://git-wip-us.apache.org/repos/asf/calcite/blob/49888a6c/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 09a6077..8c0f268 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -8902,6 +8902,39 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
         + "  tumble(timestamp '1990-03-04 12:34:56', interval '2' hour)^")
         .fails(STR_AGG_REQUIRES_MONO);
   }
+
+  @Test public void testStreamHop() {
+    // HOP
+    sql("select stream\n"
+        + "  hop_start(rowtime, interval '1' hour, interval '3' hour) as rowtime,\n"
+        + "  count(*) as c\n"
+        + "from orders\n"
+        + "group by hop(rowtime, interval '1' hour, interval '3' hour)").ok();
+    sql("select stream\n"
+        + "  ^hop_start(rowtime, interval '1' hour, interval '2' hour)^,\n"
+        + "  count(*) as c\n"
+        + "from orders\n"
+        + "group by hop(rowtime, interval '1' hour, interval '3' hour)")
+        .fails("Call to auxiliary group function 'HOP_START' must have "
+            + "matching call to group function 'HOP' in GROUP BY clause");
+    // HOP with align
+    sql("select stream\n"
+        + "  hop_start(rowtime, interval '1' hour, interval '3' hour,\n"
+        + "    time '12:34:56') as rowtime,\n"
+        + "  count(*) as c\n"
+        + "from orders\n"
+        + "group by hop(rowtime, interval '1' hour, interval '3' hour,\n"
+        + "    time '12:34:56')").ok();
+  }
+
+  @Test public void testStreamSession() {
+    // SESSION
+    sql("select stream session_start(rowtime, interval '1' hour) as rowtime,\n"
+        + "  session_end(rowtime, interval '1' hour),\n"
+        + "  count(*) as c\n"
+        + "from orders\n"
+        + "group by session(rowtime, interval '1' hour)").ok();
+  }
 }
 
 // End SqlValidatorTest.java

http://git-wip-us.apache.org/repos/asf/calcite/blob/49888a6c/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index e58617d..aa6f106 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2395,6 +2395,41 @@ LogicalDelta
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testHopTable">
+        <Resource name="sql">
+            <![CDATA[select stream hop_start(rowtime, interval '1' hour, interval '3'
hour) as rowtime,
+count(*) as c
+from orders
+group by hop(rowtime, interval '1' hour, interval '3' hour)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalDelta
+  LogicalProject(ROWTIME=[HOP_START($0, 3600000, 10800000)], C=[$1])
+    LogicalAggregate(group=[{0}], C=[COUNT()])
+      LogicalProject($f0=[HOP($0, 3600000, 10800000)])
+        LogicalTableScan(table=[[STREAM_JOINS, ORDERS]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testSessionTable">
+        <Resource name="sql">
+            <![CDATA[select stream session_start(rowtime, interval '1' hour) as rowtime,
+session_end(rowtime, interval '1' hour),
+count(*) as c
+from orders
+group by session(rowtime, interval '1' hour)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalDelta
+  LogicalProject(ROWTIME=[SESSION_START($0, 3600000)], EXPR$1=[SESSION_END($0, 3600000)],
C=[$1])
+    LogicalAggregate(group=[{0}], C=[COUNT()])
+      LogicalProject($f0=[SESSION($0, 3600000)])
+        LogicalTableScan(table=[[STREAMS, ORDERS]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testGroupByCaseIn">
         <Resource name="sql">
             <![CDATA[select

http://git-wip-us.apache.org/repos/asf/calcite/blob/49888a6c/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 2127a40..53d00c5 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1279,6 +1279,36 @@ Not implemented:
 | GROUP_ID()           | Returns an integer that uniquely identifies the combination of grouping
keys
 | GROUPING_ID(expression [, expression ] * ) | Synonym for `GROUPING`
 
+### Grouped window functions
+
+Grouped window functions occur in the `GROUP BY` clause and define a key value
+that represents a window containing several rows.
+
+In some window functions, a row may belong to more than one window.
+For example, if a query is grouped using
+`HOP(t, INTERVAL '2' HOUR, INTERVAL '1' HOUR)`, a row with timestamp '10:15:00'
+ will occur in both the 10:00 - 11:00 and 11:00 - 12:00 totals.
+
+| Operator syntax      | Description
+|:-------------------- |:-----------
+| HOP(dateTime, slide, size [, time ]) | Indicates a hopping window for *dateTime*, covering
rows within the interval of *size*, shifting every *slide*, and optionally aligned at *time*
+| SESSION(dateTime, interval [, time ]) | Indicates a session window of *interval* for *dateTime*,
optionally aligned at *time*
+| TUMBLE(dateTime, interval [, time ]) | Indicates a tumbling window of *interval* for *dateTime*,
optionally aligned at *time*
+
+### Grouped auxiliary functions
+
+Grouped auxiliary functions allow you to access properties of a window defined
+by a grouped window function.
+
+| Operator syntax      | Description
+|:-------------------- |:-----------
+| HOP_END(expression, slide, size [, time ]) | Returns the value of *expression* at the end
of the window defined by a `HOP` function call
+| HOP_START(expression, slide, size [, time ]) | Returns the value of *expression* at the
beginning of the window defined by a `HOP` function call
+| SESSION_END(expression, interval [, time]) | Returns the value of *expression* at the end
of the window defined by a `SESSION` function call
+| SESSION_START(expression, interval [, time]) | Returns the value of *expression* at the
beginning of the window defined by a `SESSION` function call
+| TUMBLE_END(expression, interval [, time ]) | Returns the value of *expression* at the end
of the window defined by a `TUMBLE` function call
+| TUMBLE_START(expression, interval [, time ]) | Returns the value of *expression* at the
beginning of the window defined by a `TUMBLE` function call
+
 ### User-defined functions
 
 Calcite is extensible. You can define each kind of function using user code.


Mime
View raw message