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 2015/05/29 21:47:56 UTC
svn commit: r1682538 [4/6] - in /incubator/calcite/site: develop/ docs/
news/ news/2015/01/31/release-1.0.0-incubating/
news/2015/03/13/release-1.1.0-incubating/
news/2015/04/07/release-1.2.0-incubating/ news/2015/04/24/new-committers/
news/2015/05/29/...
Modified: incubator/calcite/site/docs/reference.html
URL: http://svn.apache.org/viewvc/incubator/calcite/site/docs/reference.html?rev=1682538&r1=1682537&r2=1682538&view=diff
==============================================================================
--- incubator/calcite/site/docs/reference.html (original)
+++ incubator/calcite/site/docs/reference.html Fri May 29 19:47:55 2015
@@ -612,129 +612,127 @@ limitations under the License.
-->
<p>## SQL constructs</p>
-<p>```SQL
-statement:
- setStatement
- | explain
- | insert
- | update
- | merge
- | delete
- | query</p>
-
-<p>setStatement:
- ALTER ( SYSTEM | SESSION ) SET identifier = expression</p>
-
-<p>explain:
- EXPLAIN PLAN
- [ WITH TYPE | WITH IMPLEMENTATION | WITHOUT IMPLEMENTATION ]
- [ EXCLUDING ATTRIBUTES | INCLUDING [ ALL ] ATTRIBUTES ]
- FOR ( insert | update | merge | delete | query )</p>
-
-<p>insert:
- ( INSERT | UPSERT ) INTO tablePrimary
- [ â(â column [, column ]* â)â ]
- query</p>
-
-<p>update:
- UPDATE tablePrimary
- SET assign [, assign ]*
- [ WHERE booleanExpression ]</p>
-
-<p>assign:
- identifier â=â expression</p>
-
-<p>merge:
- MERGE INTO tablePrimary [ [ AS ] alias ]
- USING tablePrimary
- ON booleanExpression
- [ WHEN MATCHED THEN UPDATE SET assign [, assign ]* ]
- [ WHEN NOT MATCHED THEN INSERT VALUES â(â value [ , value ]* â)â ]</p>
-
-<p>delete:
- DELETE FROM tablePrimary [ [ AS ] alias ]
- [ WHERE booleanExpression ]</p>
-
-<p>query:
- [ WITH withItem [ , withItem ]* query ]
- | {
- select
- | query UNION [ ALL ] query
- | query EXCEPT query
- | query INTERSECT query
- }
- [ ORDER BY orderItem [, orderItem ]* ]
- [ LIMIT { count | ALL } ]
- [ OFFSET start { ROW | ROWS } ]
- [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ]</p>
-
-<p>withItem:
- name
- [ â(â column [, column ]* â)â ]
- AS â(â query â)â</p>
-
-<p>orderItem:
- expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]</p>
-
-<p>select:
- SELECT [ STREAM ] [ ALL | DISTINCT ]
- { * | projectItem [, projectItem ]* }
- FROM tableExpression
- [ WHERE booleanExpression ]
- [ GROUP BY { groupItem [, groupItem ]* } ]
- [ HAVING booleanExpression ]
- [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]</p>
-
-<p>projectItem:
- expression [ [ AS ] columnAlias ]
- | tableAlias . *</p>
-
-<p>tableExpression:
- tableReference [, tableReference ]*
- | tableExpression [ NATURAL ] [ LEFT | RIGHT | FULL ] JOIN tableExpression [ joinCondition ]</p>
-
-<p>joinCondition:
- ON booleanExpression
- | USING â(â column [, column ]* â)â</p>
-
-<p>tableReference:
- [ LATERAL ]
- tablePrimary
- [ [ AS ] alias [ â(â columnAlias [, columnAlias ]* â)â ] ]</p>
-
-<p>tablePrimary:
- [ TABLE ] [ [ catalogName . ] schemaName . ] tableName
- | â(â query â)â
- | values
- | UNNEST â(â expression â)â
- | â(â TABLE expression â)â</p>
-
-<p>values:
- VALUES expression [, expression ]*</p>
-
-<p>groupItem:
- expression
- | â(â â)â
- | â(â expression [, expression ]* â)â
- | CUBE â(â expression [, expression ]* â)â
- | ROLLUP â(â expression [, expression ]* â)â
- | GROUPING SETS â(â groupItem [, groupItem ]* â)â</p>
-
-<p>windowRef:
- windowName
- | windowSpec</p>
-
-<p>windowSpec:
- [ windowName ]
- â(â
- [ ORDER BY orderItem [, orderItem ]* ]
- [ PARTITION BY expression [, expression ]* ]
- [
- RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING }
- | ROWS numericExpression { PRECEDING | FOLLOWING }
- ]
- â)â
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">statement</span><span class="p">:</span>
+ <span class="n">setStatement</span>
+ <span class="o">|</span> <span class="k">explain</span>
+ <span class="o">|</span> <span class="k">insert</span>
+ <span class="o">|</span> <span class="k">update</span>
+ <span class="o">|</span> <span class="n">merge</span>
+ <span class="o">|</span> <span class="k">delete</span>
+ <span class="o">|</span> <span class="n">query</span>
+
+<span class="n">setStatement</span><span class="p">:</span>
+ <span class="k">ALTER</span> <span class="p">(</span> <span class="k">SYSTEM</span> <span class="o">|</span> <span class="k">SESSION</span> <span class="p">)</span> <span class="k">SET</span> <span class="n">identifier</span> <span class="o">=</span> <span class="n">expression</span>
+
+<span class="k">explain</span><span class="p">:</span>
+ <span class="k">EXPLAIN</span> <span class="n">PLAN</span>
+ <span class="p">[</span> <span class="k">WITH</span> <span class="k">TYPE</span> <span class="o">|</span> <span class="k">WITH</span> <span class="k">IMPLEMENTATION</span> <span class="o">|</span> <span class="k">WITHOUT</span> <span class="k">IMPLEMENTATION</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="k">EXCLUDING</span> <span class="n">ATTRIBUTES</span> <span class="o">|</span> <span class="k">INCLUDING</span> <span class="p">[</span> <span class="k">ALL</span> <span class="p">]</span> <span class="n">ATTRIBUTES</span> <span class="p">]</span>
+ <span class="k">FOR</span> <span class="p">(</span> <span class="k">insert</span> <span class="o">|</span> <span class="k">update</span> <span class="o">|</span> <span class="n">merge</span> <span class="o">|</span> <span class="k">delete</span> <span class="o">|</span> <span class="n">query</span> <span class="p">)</span>
+
+<span class="k">insert</span><span class="p">:</span>
+ <span class="p">(</span> <span class="k">INSERT</span> <span class="o">|</span> <span class="n">UPSERT</span> <span class="p">)</span> <span class="k">INTO</span> <span class="n">tablePrimary</span>
+ <span class="p">[</span> <span class="s1">'('</span> <span class="k">column</span> <span class="p">[,</span> <span class="k">column</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span>
+ <span class="n">query</span>
+
+<span class="k">update</span><span class="p">:</span>
+ <span class="k">UPDATE</span> <span class="n">tablePrimary</span>
+ <span class="k">SET</span> <span class="n">assign</span> <span class="p">[,</span> <span class="n">assign</span> <span class="p">]</span><span class="o">*</span>
+ <span class="p">[</span> <span class="k">WHERE</span> <span class="n">booleanExpression</span> <span class="p">]</span>
+
+<span class="n">assign</span><span class="p">:</span>
+ <span class="n">identifier</span> <span class="s1">'='</span> <span class="n">expression</span>
+
+<span class="n">merge</span><span class="p">:</span>
+ <span class="n">MERGE</span> <span class="k">INTO</span> <span class="n">tablePrimary</span> <span class="p">[</span> <span class="p">[</span> <span class="k">AS</span> <span class="p">]</span> <span class="k">alias</span> <span class="p">]</span>
+ <span class="k">USING</span> <span class="n">tablePrimary</span>
+ <span class="k">ON</span> <span class="n">booleanExpression</span>
+ <span class="p">[</span> <span class="k">WHEN</span> <span class="n">MATCHED</span> <span class="k">THEN</span> <span class="k">UPDATE</span> <span class="k">SET</span> <span class="n">assign</span> <span class="p">[,</span> <span class="n">assign</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="k">WHEN</span> <span class="k">NOT</span> <span class="n">MATCHED</span> <span class="k">THEN</span> <span class="k">INSERT</span> <span class="k">VALUES</span> <span class="s1">'('</span> <span class="n">value</span> <span class="p">[</span> <span class="p">,</span> <span class="n">value</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span>
+
+<span class="k">delete</span><span class="p">:</span>
+ <span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">tablePrimary</span> <span class="p">[</span> <span class="p">[</span> <span class="k">AS</span> <span class="p">]</span> <span class="k">alias</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="k">WHERE</span> <span class="n">booleanExpression</span> <span class="p">]</span>
+
+<span class="n">query</span><span class="p">:</span>
+ <span class="p">[</span> <span class="k">WITH</span> <span class="n">withItem</span> <span class="p">[</span> <span class="p">,</span> <span class="n">withItem</span> <span class="p">]</span><span class="o">*</span> <span class="n">query</span> <span class="p">]</span>
+ <span class="o">|</span> <span class="err">{</span>
+ <span class="k">select</span>
+ <span class="o">|</span> <span class="n">query</span> <span class="k">UNION</span> <span class="p">[</span> <span class="k">ALL</span> <span class="p">]</span> <span class="n">query</span>
+ <span class="o">|</span> <span class="n">query</span> <span class="k">EXCEPT</span> <span class="n">query</span>
+ <span class="o">|</span> <span class="n">query</span> <span class="k">INTERSECT</span> <span class="n">query</span>
+ <span class="err">}</span>
+ <span class="p">[</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">orderItem</span> <span class="p">[,</span> <span class="n">orderItem</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="k">LIMIT</span> <span class="err">{</span> <span class="k">count</span> <span class="o">|</span> <span class="k">ALL</span> <span class="err">}</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="k">OFFSET</span> <span class="k">start</span> <span class="err">{</span> <span class="k">ROW</span> <span class="o">|</span> <span class="k">ROWS</span> <span class="err">}</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="k">FETCH</span> <span class="err">{</span> <span class="k">FIRST</span> <span class="o">|</span> <span class="k">NEXT</span> <span class="err">}</span> <span class="p">[</span> <span class="k">count</span> <span class="p">]</span> <span class="err">{</span> <span class="k">ROW</span> <span class="o">|</span> <span class="k">ROWS</span> <span class="err">}</span> <span class="p">]</span>
+
+<span class="n">withItem</span><span class="p">:</span>
+ <span class="n">name</span>
+ <span class="p">[</span> <span class="s1">'('</span> <span class="k">column</span> <span class="p">[,</span> <span class="k">column</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span>
+ <span class="k">AS</span> <span class="s1">'('</span> <span class="n">query</span> <span class="s1">')'</span>
+
+<span class="n">orderItem</span><span class="p">:</span>
+ <span class="n">expression</span> <span class="p">[</span> <span class="k">ASC</span> <span class="o">|</span> <span class="k">DESC</span> <span class="p">]</span> <span class="p">[</span> <span class="n">NULLS</span> <span class="k">FIRST</span> <span class="o">|</span> <span class="n">NULLS</span> <span class="k">LAST</span> <span class="p">]</span>
+
+<span class="k">select</span><span class="p">:</span>
+ <span class="k">SELECT</span> <span class="p">[</span> <span class="n">STREAM</span> <span class="p">]</span> <span class="p">[</span> <span class="k">ALL</span> <span class="o">|</span> <span class="k">DISTINCT</span> <span class="p">]</span>
+ <span class="err">{</span> <span class="o">*</span> <span class="o">|</span> <span class="n">projectItem</span> <span class="p">[,</span> <span class="n">projectItem</span> <span class="p">]</span><span class="o">*</span> <span class="err">}</span>
+ <span class="k">FROM</span> <span class="n">tableExpression</span>
+ <span class="p">[</span> <span class="k">WHERE</span> <span class="n">booleanExpression</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="err">{</span> <span class="n">groupItem</span> <span class="p">[,</span> <span class="n">groupItem</span> <span class="p">]</span><span class="o">*</span> <span class="err">}</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="k">HAVING</span> <span class="n">booleanExpression</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="n">WINDOW</span> <span class="n">windowName</span> <span class="k">AS</span> <span class="n">windowSpec</span> <span class="p">[,</span> <span class="n">windowName</span> <span class="k">AS</span> <span class="n">windowSpec</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
+
+<span class="n">projectItem</span><span class="p">:</span>
+ <span class="n">expression</span> <span class="p">[</span> <span class="p">[</span> <span class="k">AS</span> <span class="p">]</span> <span class="n">columnAlias</span> <span class="p">]</span>
+ <span class="o">|</span> <span class="n">tableAlias</span> <span class="p">.</span> <span class="o">*</span>
+
+<span class="n">tableExpression</span><span class="p">:</span>
+ <span class="n">tableReference</span> <span class="p">[,</span> <span class="n">tableReference</span> <span class="p">]</span><span class="o">*</span>
+ <span class="o">|</span> <span class="n">tableExpression</span> <span class="p">[</span> <span class="k">NATURAL</span> <span class="p">]</span> <span class="p">[</span> <span class="k">LEFT</span> <span class="o">|</span> <span class="k">RIGHT</span> <span class="o">|</span> <span class="k">FULL</span> <span class="p">]</span> <span class="k">JOIN</span> <span class="n">tableExpression</span> <span class="p">[</span> <span class="n">joinCondition</span> <span class="p">]</span>
+
+<span class="n">joinCondition</span><span class="p">:</span>
+ <span class="k">ON</span> <span class="n">booleanExpression</span>
+ <span class="o">|</span> <span class="k">USING</span> <span class="s1">'('</span> <span class="k">column</span> <span class="p">[,</span> <span class="k">column</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
+
+<span class="n">tableReference</span><span class="p">:</span>
+ <span class="p">[</span> <span class="k">LATERAL</span> <span class="p">]</span>
+ <span class="n">tablePrimary</span>
+ <span class="p">[</span> <span class="p">[</span> <span class="k">AS</span> <span class="p">]</span> <span class="k">alias</span> <span class="p">[</span> <span class="s1">'('</span> <span class="n">columnAlias</span> <span class="p">[,</span> <span class="n">columnAlias</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span> <span class="p">]</span> <span class="p">]</span>
+
+<span class="n">tablePrimary</span><span class="p">:</span>
+ <span class="p">[</span> <span class="k">TABLE</span> <span class="p">]</span> <span class="p">[</span> <span class="p">[</span> <span class="n">catalogName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">schemaName</span> <span class="p">.</span> <span class="p">]</span> <span class="n">tableName</span>
+ <span class="o">|</span> <span class="s1">'('</span> <span class="n">query</span> <span class="s1">')'</span>
+ <span class="o">|</span> <span class="k">values</span>
+ <span class="o">|</span> <span class="k">UNNEST</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="s1">')'</span>
+ <span class="o">|</span> <span class="s1">'('</span> <span class="k">TABLE</span> <span class="n">expression</span> <span class="s1">')'</span>
+
+<span class="k">values</span><span class="p">:</span>
+ <span class="k">VALUES</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span>
+
+<span class="n">groupItem</span><span class="p">:</span>
+ <span class="n">expression</span>
+ <span class="o">|</span> <span class="s1">'('</span> <span class="s1">')'</span>
+ <span class="o">|</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
+ <span class="o">|</span> <span class="k">CUBE</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
+ <span class="o">|</span> <span class="k">ROLLUP</span> <span class="s1">'('</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
+ <span class="o">|</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="s1">'('</span> <span class="n">groupItem</span> <span class="p">[,</span> <span class="n">groupItem</span> <span class="p">]</span><span class="o">*</span> <span class="s1">')'</span>
+
+<span class="n">windowRef</span><span class="p">:</span>
+ <span class="n">windowName</span>
+ <span class="o">|</span> <span class="n">windowSpec</span>
+
+<span class="n">windowSpec</span><span class="p">:</span>
+ <span class="p">[</span> <span class="n">windowName</span> <span class="p">]</span>
+ <span class="s1">'('</span>
+ <span class="p">[</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">orderItem</span> <span class="p">[,</span> <span class="n">orderItem</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
+ <span class="p">[</span> <span class="n">PARTITION</span> <span class="k">BY</span> <span class="n">expression</span> <span class="p">[,</span> <span class="n">expression</span> <span class="p">]</span><span class="o">*</span> <span class="p">]</span>
+ <span class="p">[</span>
+ <span class="n">RANGE</span> <span class="n">numericOrIntervalExpression</span> <span class="err">{</span> <span class="n">PRECEDING</span> <span class="o">|</span> <span class="n">FOLLOWING</span> <span class="err">}</span>
+ <span class="o">|</span> <span class="k">ROWS</span> <span class="n">numericExpression</span> <span class="err">{</span> <span class="n">PRECEDING</span> <span class="o">|</span> <span class="n">FOLLOWING</span> <span class="err">}</span>
+ <span class="p">]</span>
+ <span class="s1">')'</span></code></pre></div>
<p>In <em>merge</em>, at least one of the WHEN MATCHED and WHEN NOT MATCHED clauses must
be present.</p>
@@ -894,11 +892,10 @@ name will have been converted to upper c
</tbody>
</table>
-<p>Where:
-<code>SQL
-timeUnit:
- YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
-</code></p>
+<p>Where:</p>
+
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">timeUnit</span><span class="p">:</span>
+ <span class="k">YEAR</span> <span class="o">|</span> <span class="k">MONTH</span> <span class="o">|</span> <span class="k">DAY</span> <span class="o">|</span> <span class="n">HOUR</span> <span class="o">|</span> <span class="k">MINUTE</span> <span class="o">|</span> <span class="k">SECOND</span></code></pre></div>
<p>Note:
* DATE, TIME and TIMESTAMP have no time zone. There is not even an implicit
@@ -1589,11 +1586,9 @@ timeUnit:
<p>Syntax:</p>
-<p><code>SQL
-aggregateCall:
- agg( [ DISTINCT ] value [, value]* ) [ FILTER ( WHERE condition ) ]
- | agg(*) [ FILTER ( WHERE condition ) ]
-</code></p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="n">aggregateCall</span><span class="p">:</span>
+ <span class="n">agg</span><span class="p">(</span> <span class="p">[</span> <span class="k">DISTINCT</span> <span class="p">]</span> <span class="n">value</span> <span class="p">[,</span> <span class="n">value</span><span class="p">]</span><span class="o">*</span> <span class="p">)</span> <span class="p">[</span> <span class="n">FILTER</span> <span class="p">(</span> <span class="k">WHERE</span> <span class="n">condition</span> <span class="p">)</span> <span class="p">]</span>
+ <span class="o">|</span> <span class="n">agg</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="p">[</span> <span class="n">FILTER</span> <span class="p">(</span> <span class="k">WHERE</span> <span class="n">condition</span> <span class="p">)</span> <span class="p">]</span></code></pre></div>
<p>If <code>FILTER</code> is present, the aggregate function only considers rows for which
<em>condition</em> evaluates to TRUE.</p>
Modified: incubator/calcite/site/docs/stream.html
URL: http://svn.apache.org/viewvc/incubator/calcite/site/docs/stream.html?rev=1682538&r1=1682537&r2=1682538&view=diff
==============================================================================
--- incubator/calcite/site/docs/stream.html (original)
+++ incubator/calcite/site/docs/stream.html Fri May 29 19:47:55 2015
@@ -649,21 +649,19 @@ standard SQL.</p>
<p>Letâs start with the simplest streaming query:</p>
-<p>```sql
-SELECT STREAM *
-FROM Orders;</p>
-
-<p>rowtime | productId | orderId | units
-âââ-+ââââ+âââ+ââ-
- 10:17:00 | 30 | 5 | 4
- 10:17:05 | 10 | 6 | 1
- 10:18:05 | 20 | 7 | 2
- 10:18:07 | 30 | 8 | 20
- 11:02:00 | 10 | 9 | 6
- 11:04:00 | 10 | 10 | 1
- 11:09:30 | 40 | 11 | 12
- 11:24:11 | 10 | 12 | 4
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span>
+<span class="k">FROM</span> <span class="n">Orders</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span> <span class="o">|</span> <span class="n">orderId</span> <span class="o">|</span> <span class="n">units</span>
+<span class="c1">----------+-----------+---------+-------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">5</span> <span class="o">|</span> <span class="mi">4</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">05</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">6</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">18</span><span class="p">:</span><span class="mi">05</span> <span class="o">|</span> <span class="mi">20</span> <span class="o">|</span> <span class="mi">7</span> <span class="o">|</span> <span class="mi">2</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">18</span><span class="p">:</span><span class="mi">07</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">8</span> <span class="o">|</span> <span class="mi">20</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">02</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">9</span> <span class="o">|</span> <span class="mi">6</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">04</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">09</span><span class="p">:</span><span class="mi">30</span> <span class="o">|</span> <span class="mi">40</span> <span class="o">|</span> <span class="mi">11</span> <span class="o">|</span> <span class="mi">12</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">24</span><span class="p">:</span><span class="mi">11</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="mi">4</span></code></pre></div>
<p>This query reads all columns and rows from the <code>Orders</code> stream.
Like any streaming query, it never terminates. It outputs a record whenever
@@ -674,19 +672,17 @@ a record arrives in <code>Orders</code>.
<p>The <code>STREAM</code> keyword is the main extension in streaming SQL. It tells the
system that you are interested in incoming orders, not existing ones. The query</p>
-<p>```sql
-SELECT *
-FROM Orders;</p>
-
-<p>rowtime | productId | orderId | units
-âââ-+ââââ+âââ+ââ-
- 08:30:00 | 10 | 1 | 3
- 08:45:10 | 20 | 2 | 1
- 09:12:21 | 10 | 3 | 10
- 09:27:44 | 30 | 4 | 2</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="o">*</span>
+<span class="k">FROM</span> <span class="n">Orders</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span> <span class="o">|</span> <span class="n">orderId</span> <span class="o">|</span> <span class="n">units</span>
+<span class="c1">----------+-----------+---------+-------</span>
+ <span class="mi">08</span><span class="p">:</span><span class="mi">30</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">1</span> <span class="o">|</span> <span class="mi">3</span>
+ <span class="mi">08</span><span class="p">:</span><span class="mi">45</span><span class="p">:</span><span class="mi">10</span> <span class="o">|</span> <span class="mi">20</span> <span class="o">|</span> <span class="mi">2</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">09</span><span class="p">:</span><span class="mi">12</span><span class="p">:</span><span class="mi">21</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">3</span> <span class="o">|</span> <span class="mi">10</span>
+ <span class="mi">09</span><span class="p">:</span><span class="mi">27</span><span class="p">:</span><span class="mi">44</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">4</span> <span class="o">|</span> <span class="mi">2</span>
-<p>4 records returned.
-```</p>
+<span class="mi">4</span> <span class="n">records</span> <span class="n">returned</span><span class="p">.</span></code></pre></div>
<p>is also valid, but will print out all existing orders and then terminate. We
call it a <em>relational</em> query, as opposed to <em>streaming</em>. It has traditional
@@ -696,57 +692,49 @@ SQL semantics.</p>
a streaming query on a table, or a relational query on a stream, Calcite gives
an error:</p>
-<p>```sql
-> SELECT * FROM Shipments;
-ERROR: Cannot convert stream âSHIPMENTSâ to a table</p>
-
-<blockquote>
- <p>SELECT STREAM * FROM Products;
-ERROR: Cannot convert table âPRODUCTSâ to a stream
-```</p>
-</blockquote>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="o">></span> <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">Shipments</span><span class="p">;</span>
+<span class="n">ERROR</span><span class="p">:</span> <span class="n">Cannot</span> <span class="k">convert</span> <span class="n">stream</span> <span class="s1">'SHIPMENTS'</span> <span class="k">to</span> <span class="n">a</span> <span class="k">table</span>
+
+<span class="o">></span> <span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">Products</span><span class="p">;</span>
+<span class="n">ERROR</span><span class="p">:</span> <span class="n">Cannot</span> <span class="k">convert</span> <span class="k">table</span> <span class="s1">'PRODUCTS'</span> <span class="k">to</span> <span class="n">a</span> <span class="n">stream</span></code></pre></div>
<h1 id="filtering-rows">Filtering rows</h1>
<p>Just as in regular SQL, you use a <code>WHERE</code> clause to filter rows:</p>
-<p>```sql
-SELECT STREAM *
-FROM Orders
-WHERE units > 3;</p>
-
-<p>rowtime | productId | orderId | units
-âââ-+ââââ+âââ+ââ-
- 10:17:00 | 30 | 5 | 4
- 10:18:07 | 30 | 8 | 20
- 11:02:00 | 10 | 9 | 6
- 11:09:30 | 40 | 11 | 12
- 11:24:11 | 10 | 12 | 4
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span>
+<span class="k">FROM</span> <span class="n">Orders</span>
+<span class="k">WHERE</span> <span class="n">units</span> <span class="o">></span> <span class="mi">3</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span> <span class="o">|</span> <span class="n">orderId</span> <span class="o">|</span> <span class="n">units</span>
+<span class="c1">----------+-----------+---------+-------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">5</span> <span class="o">|</span> <span class="mi">4</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">18</span><span class="p">:</span><span class="mi">07</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">8</span> <span class="o">|</span> <span class="mi">20</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">02</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">9</span> <span class="o">|</span> <span class="mi">6</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">09</span><span class="p">:</span><span class="mi">30</span> <span class="o">|</span> <span class="mi">40</span> <span class="o">|</span> <span class="mi">11</span> <span class="o">|</span> <span class="mi">12</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">24</span><span class="p">:</span><span class="mi">11</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="mi">4</span></code></pre></div>
<h1 id="projecting-expressions">Projecting expressions</h1>
<p>Use expressions in the <code>SELECT</code> clause to choose which columns to return or
compute expressions:</p>
-<p>```sql
-SELECT STREAM rowtime,
- âAn order for â || units || â â
- || CASE units WHEN 1 THEN âunitâ ELSE âunitsâ END
- || â of product #â || productId AS description
-FROM Orders;</p>
-
-<p>rowtime | description
-âââ-+âââââââââââââ
- 10:17:00 | An order for 4 units of product #30
- 10:17:05 | An order for 1 unit of product #10
- 10:18:05 | An order for 2 units of product #20
- 10:18:07 | An order for 20 units of product #30
- 11:02:00 | An order by 6 units of product #10
- 11:04:00 | An order by 1 unit of product #10
- 11:09:30 | An order for 12 units of product #40
- 11:24:11 | An order by 4 units of product #10
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span><span class="p">,</span>
+ <span class="s1">'An order for '</span> <span class="o">||</span> <span class="n">units</span> <span class="o">||</span> <span class="s1">' '</span>
+ <span class="o">||</span> <span class="k">CASE</span> <span class="n">units</span> <span class="k">WHEN</span> <span class="mi">1</span> <span class="k">THEN</span> <span class="s1">'unit'</span> <span class="k">ELSE</span> <span class="s1">'units'</span> <span class="k">END</span>
+ <span class="o">||</span> <span class="s1">' of product #'</span> <span class="o">||</span> <span class="n">productId</span> <span class="k">AS</span> <span class="n">description</span>
+<span class="k">FROM</span> <span class="n">Orders</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">description</span>
+<span class="c1">----------+---------------------------------------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="n">An</span> <span class="k">order</span> <span class="k">for</span> <span class="mi">4</span> <span class="n">units</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">30</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">05</span> <span class="o">|</span> <span class="n">An</span> <span class="k">order</span> <span class="k">for</span> <span class="mi">1</span> <span class="n">unit</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">10</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">18</span><span class="p">:</span><span class="mi">05</span> <span class="o">|</span> <span class="n">An</span> <span class="k">order</span> <span class="k">for</span> <span class="mi">2</span> <span class="n">units</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">20</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">18</span><span class="p">:</span><span class="mi">07</span> <span class="o">|</span> <span class="n">An</span> <span class="k">order</span> <span class="k">for</span> <span class="mi">20</span> <span class="n">units</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">30</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">02</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="n">An</span> <span class="k">order</span> <span class="k">by</span> <span class="mi">6</span> <span class="n">units</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">10</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">04</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="n">An</span> <span class="k">order</span> <span class="k">by</span> <span class="mi">1</span> <span class="n">unit</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">10</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">09</span><span class="p">:</span><span class="mi">30</span> <span class="o">|</span> <span class="n">An</span> <span class="k">order</span> <span class="k">for</span> <span class="mi">12</span> <span class="n">units</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">40</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">24</span><span class="p">:</span><span class="mi">11</span> <span class="o">|</span> <span class="n">An</span> <span class="k">order</span> <span class="k">by</span> <span class="mi">4</span> <span class="n">units</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">10</span></code></pre></div>
<p>We recommend that you always include the <code>rowtime</code> column in the <code>SELECT</code>
clause. Having a sorted timestamp in each stream and streaming query makes it
@@ -764,22 +752,20 @@ differences are:
<p>First weâll look a <em>tumbling window</em>, which is defined by a streaming
<code>GROUP BY</code>. Here is an example:</p>
-<p>```sql
-SELECT STREAM FLOOR(rowtime TO HOUR) AS rowtime,
- productId,
- COUNT(*) AS c,
- SUM(units) AS units
-FROM Orders
-GROUP BY FLOOR(rowtime TO HOUR), productId;</p>
-
-<p>rowtime | productId | c | units
-âââ-+ââââ+âââ+ââ-
- 10:00:00 | 30 | 2 | 24
- 10:00:00 | 10 | 1 | 1
- 10:00:00 | 20 | 1 | 7
- 11:00:00 | 10 | 3 | 11
- 11:00:00 | 40 | 1 | 12
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rowtime</span><span class="p">,</span>
+ <span class="n">productId</span><span class="p">,</span>
+ <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">c</span><span class="p">,</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="n">units</span><span class="p">)</span> <span class="k">AS</span> <span class="n">units</span>
+<span class="k">FROM</span> <span class="n">Orders</span>
+<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span> <span class="o">|</span> <span class="k">c</span> <span class="o">|</span> <span class="n">units</span>
+<span class="c1">----------+-----------+---------+-------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">2</span> <span class="o">|</span> <span class="mi">24</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">1</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">20</span> <span class="o">|</span> <span class="mi">1</span> <span class="o">|</span> <span class="mi">7</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">3</span> <span class="o">|</span> <span class="mi">11</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">40</span> <span class="o">|</span> <span class="mi">1</span> <span class="o">|</span> <span class="mi">12</span></code></pre></div>
<p>The result is a stream. At 11 oâclock, Calcite emits a sub-total for every
<code>productId</code> that had an order since 10 oâclock. At 12 oâclock, it will emit
@@ -796,14 +782,12 @@ total.</p>
<em>monotonic</em>. Without a monotonic expression in the <code>GROUP BY</code> clause, Calcite is
not able to make progress, and it will not allow the query:</p>
-<p><code>sql
-> SELECT STREAM productId,
-> COUNT(*) AS c,
-> SUM(units) AS units
-> FROM Orders
-> GROUP BY productId;
-ERROR: Streaming aggregation requires at least one monotonic expression in GROUP BY clause
-</code></p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="o">></span> <span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">productId</span><span class="p">,</span>
+<span class="o">></span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">c</span><span class="p">,</span>
+<span class="o">></span> <span class="k">SUM</span><span class="p">(</span><span class="n">units</span><span class="p">)</span> <span class="k">AS</span> <span class="n">units</span>
+<span class="o">></span> <span class="k">FROM</span> <span class="n">Orders</span>
+<span class="o">></span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">productId</span><span class="p">;</span>
+<span class="n">ERROR</span><span class="p">:</span> <span class="n">Streaming</span> <span class="n">aggregation</span> <span class="n">requires</span> <span class="k">at</span> <span class="n">least</span> <span class="n">one</span> <span class="n">monotonic</span> <span class="n">expression</span> <span class="k">in</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">clause</span></code></pre></div>
<p>Monotonic columns need to be declared in the schema. The monotonicity is
enforced when records enter the stream and assumed by queries that read from
@@ -815,42 +799,38 @@ that stream. We recommend that you give
<p>As in standard SQL, you can apply a <code>HAVING</code> clause to filter rows emitted by
a streaming <code>GROUP BY</code>:</p>
-<p>```sql
-SELECT STREAM FLOOR(rowtime TO HOUR) AS rowtime,
- productId
-FROM Orders
-GROUP BY FLOOR(rowtime TO HOUR), productId
-HAVING COUNT(*) > 2 OR SUM(units) > 10;</p>
-
-<p>rowtime | productId
-âââ-+ââââ
- 10:00:00 | 30
- 11:00:00 | 10
- 11:00:00 | 40
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rowtime</span><span class="p">,</span>
+ <span class="n">productId</span>
+<span class="k">FROM</span> <span class="n">Orders</span>
+<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span>
+<span class="k">HAVING</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="o">></span> <span class="mi">2</span> <span class="k">OR</span> <span class="k">SUM</span><span class="p">(</span><span class="n">units</span><span class="p">)</span> <span class="o">></span> <span class="mi">10</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span>
+<span class="c1">----------+-----------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">40</span></code></pre></div>
<h1 id="sub-queries-views-and-sqls-closure-property">Sub-queries, views and SQLâs closure property</h1>
<p>The previous <code>HAVING</code> query can be expressed using a <code>WHERE</code> clause on a
sub-query:</p>
-<p>```sql
-SELECT STREAM rowtime, productId
-FROM (
- SELECT FLOOR(rowtime TO HOUR) AS rowtime,
- productId,
- COUNT(*) AS c,
- SUM(units) AS su
- FROM Orders
- GROUP BY FLOOR(rowtime TO HOUR), productId)
-WHERE c > 2 OR su > 10;</p>
-
-<p>rowtime | productId
-âââ-+ââââ
- 10:00:00 | 30
- 11:00:00 | 10
- 11:00:00 | 40
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span><span class="p">,</span> <span class="n">productId</span>
+<span class="k">FROM</span> <span class="p">(</span>
+ <span class="k">SELECT</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rowtime</span><span class="p">,</span>
+ <span class="n">productId</span><span class="p">,</span>
+ <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">c</span><span class="p">,</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="n">units</span><span class="p">)</span> <span class="k">AS</span> <span class="n">su</span>
+ <span class="k">FROM</span> <span class="n">Orders</span>
+ <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span><span class="p">)</span>
+<span class="k">WHERE</span> <span class="k">c</span> <span class="o">></span> <span class="mi">2</span> <span class="k">OR</span> <span class="n">su</span> <span class="o">></span> <span class="mi">10</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span>
+<span class="c1">----------+-----------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">40</span></code></pre></div>
<p><code>HAVING</code> was introduced in the early days of SQL, when a way was needed to
perform a filter <em>after</em> aggregation. (Recall that <code>WHERE</code> filters rows before
@@ -863,25 +843,23 @@ any operation you can perform on a table
<code>HAVING</code> obsolete (or, at least, reduce it to syntactic sugar), it makes views
possible:</p>
-<p>```sql
-CREATE VIEW HourlyOrderTotals (rowtime, productId, c, su) AS
- SELECT FLOOR(rowtime TO HOUR),
- productId,
- COUNT(*),
- SUM(units)
- FROM Orders
- GROUP BY FLOOR(rowtime TO HOUR), productId;</p>
-
-<p>SELECT STREAM rowtime, productId
-FROM HourlyOrderTotals
-WHERE c > 2 OR su > 10;</p>
-
-<p>rowtime | productId
-âââ-+ââââ
- 10:00:00 | 30
- 11:00:00 | 10
- 11:00:00 | 40
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">CREATE</span> <span class="k">VIEW</span> <span class="n">HourlyOrderTotals</span> <span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">productId</span><span class="p">,</span> <span class="k">c</span><span class="p">,</span> <span class="n">su</span><span class="p">)</span> <span class="k">AS</span>
+ <span class="k">SELECT</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">),</span>
+ <span class="n">productId</span><span class="p">,</span>
+ <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">),</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="n">units</span><span class="p">)</span>
+ <span class="k">FROM</span> <span class="n">Orders</span>
+ <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span><span class="p">;</span>
+
+<span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span><span class="p">,</span> <span class="n">productId</span>
+<span class="k">FROM</span> <span class="n">HourlyOrderTotals</span>
+<span class="k">WHERE</span> <span class="k">c</span> <span class="o">></span> <span class="mi">2</span> <span class="k">OR</span> <span class="n">su</span> <span class="o">></span> <span class="mi">10</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span>
+<span class="c1">----------+-----------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">40</span></code></pre></div>
<p>Sub-queries in the <code>FROM</code> clause are sometimes referred to as âinline viewsâ,
but really, nested queries are more fundamental. Views are just a convenient
@@ -895,24 +873,22 @@ Nested queries and views help to express
<p>And, by the way, a <code>WITH</code> clause can accomplish the same as a sub-query or
a view:</p>
-<p>```sql
-WITH HourlyOrderTotals (rowtime, productId, c, su) AS (
- SELECT FLOOR(rowtime TO HOUR),
- productId,
- COUNT(*),
- SUM(units)
- FROM Orders
- GROUP BY FLOOR(rowtime TO HOUR), productId)
-SELECT STREAM rowtime, productId
-FROM HourlyOrderTotals
-WHERE c > 2 OR su > 10;</p>
-
-<p>rowtime | productId
-âââ-+ââââ
- 10:00:00 | 30
- 11:00:00 | 10
- 11:00:00 | 40
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">WITH</span> <span class="n">HourlyOrderTotals</span> <span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">productId</span><span class="p">,</span> <span class="k">c</span><span class="p">,</span> <span class="n">su</span><span class="p">)</span> <span class="k">AS</span> <span class="p">(</span>
+ <span class="k">SELECT</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">),</span>
+ <span class="n">productId</span><span class="p">,</span>
+ <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">),</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="n">units</span><span class="p">)</span>
+ <span class="k">FROM</span> <span class="n">Orders</span>
+ <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span><span class="p">)</span>
+<span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span><span class="p">,</span> <span class="n">productId</span>
+<span class="k">FROM</span> <span class="n">HourlyOrderTotals</span>
+<span class="k">WHERE</span> <span class="k">c</span> <span class="o">></span> <span class="mi">2</span> <span class="k">OR</span> <span class="n">su</span> <span class="o">></span> <span class="mi">10</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span>
+<span class="c1">----------+-----------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">40</span></code></pre></div>
<h2 id="converting-between-streams-and-relations">Converting between streams and relations</h2>
@@ -924,19 +900,17 @@ However, it is a relation that can be co
<p>You can use it in both relational and streaming queries:</p>
-<p>```sql
-# A relation; will query the historic Orders table.
-# Returns the largest number of product #10 ever sold in one hour.
-SELECT max(su)
-FROM HourlyOrderTotals
-WHERE productId = 10;</p>
-
-<h1 id="a-stream-will-query-the-orders-stream">A stream; will query the Orders stream.</h1>
-<p># Returns every hour in which at least one product #10 was sold.
-SELECT STREAM rowtime
-FROM HourlyOrderTotals
-WHERE productId = 10;
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="o">#</span> <span class="n">A</span> <span class="n">relation</span><span class="p">;</span> <span class="n">will</span> <span class="n">query</span> <span class="n">the</span> <span class="n">historic</span> <span class="n">Orders</span> <span class="k">table</span><span class="p">.</span>
+<span class="o">#</span> <span class="k">Returns</span> <span class="n">the</span> <span class="n">largest</span> <span class="nb">number</span> <span class="k">of</span> <span class="n">product</span> <span class="o">#</span><span class="mi">10</span> <span class="n">ever</span> <span class="n">sold</span> <span class="k">in</span> <span class="n">one</span> <span class="n">hour</span><span class="p">.</span>
+<span class="k">SELECT</span> <span class="k">max</span><span class="p">(</span><span class="n">su</span><span class="p">)</span>
+<span class="k">FROM</span> <span class="n">HourlyOrderTotals</span>
+<span class="k">WHERE</span> <span class="n">productId</span> <span class="o">=</span> <span class="mi">10</span><span class="p">;</span>
+
+<span class="o">#</span> <span class="n">A</span> <span class="n">stream</span><span class="p">;</span> <span class="n">will</span> <span class="n">query</span> <span class="n">the</span> <span class="n">Orders</span> <span class="n">stream</span><span class="p">.</span>
+<span class="o">#</span> <span class="k">Returns</span> <span class="k">every</span> <span class="n">hour</span> <span class="k">in</span> <span class="n">which</span> <span class="k">at</span> <span class="n">least</span> <span class="n">one</span> <span class="n">product</span> <span class="o">#</span><span class="mi">10</span> <span class="n">was</span> <span class="n">sold</span><span class="p">.</span>
+<span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span>
+<span class="k">FROM</span> <span class="n">HourlyOrderTotals</span>
+<span class="k">WHERE</span> <span class="n">productId</span> <span class="o">=</span> <span class="mi">10</span><span class="p">;</span></code></pre></div>
<p>This approach is not limited to views and sub-queries.
Following the approach set out in CQL [<a href="#ref1">1</a>], every query
@@ -964,17 +938,15 @@ hour and product id.</p>
the past three hours. To do this, we use <code>SELECT ... OVER</code> and a sliding window
to combine multiple tumbling windows.</p>
-<p><code>sql
-SELECT STREAM rowtime,
- productId,
- SUM(su) OVER w AS su,
- SUM(c) OVER w AS c
-FROM HourlyTotals
-WINDOW w AS (
- ORDER BY rowtime
- PARTITION BY productId
- RANGE INTERVAL '2' HOUR PRECEDING)
-</code></p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span><span class="p">,</span>
+ <span class="n">productId</span><span class="p">,</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="n">su</span><span class="p">)</span> <span class="n">OVER</span> <span class="n">w</span> <span class="k">AS</span> <span class="n">su</span><span class="p">,</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="k">c</span><span class="p">)</span> <span class="n">OVER</span> <span class="n">w</span> <span class="k">AS</span> <span class="k">c</span>
+<span class="k">FROM</span> <span class="n">HourlyTotals</span>
+<span class="n">WINDOW</span> <span class="n">w</span> <span class="k">AS</span> <span class="p">(</span>
+ <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">rowtime</span>
+ <span class="n">PARTITION</span> <span class="k">BY</span> <span class="n">productId</span>
+ <span class="n">RANGE</span> <span class="nb">INTERVAL</span> <span class="s1">'2'</span> <span class="n">HOUR</span> <span class="n">PRECEDING</span><span class="p">)</span></code></pre></div>
<p>This query uses the <code>HourlyOrderTotals</code> view defined previously.
The 2 hour interval combines the totals timestamped 09:00:00, 10:00:00 and
@@ -1017,45 +989,41 @@ The syntax looks like regular SQL, but C
timely results. It therefore requires a monotonic expression on the leading edge
of your <code>ORDER BY</code> key.</p>
-<p>```sql
-SELECT STREAM FLOOR(rowtime TO hour) AS rowtime, productId, orderId, units
-FROM Orders
-ORDER BY FLOOR(rowtime TO hour) ASC, units DESC;</p>
-
-<p>rowtime | productId | orderId | units
-âââ-+ââââ+âââ+ââ-
- 10:00:00 | 30 | 8 | 20
- 10:00:00 | 30 | 5 | 4
- 10:00:00 | 20 | 7 | 2
- 10:00:00 | 10 | 6 | 1
- 11:00:00 | 40 | 11 | 12
- 11:00:00 | 10 | 9 | 6
- 11:00:00 | 10 | 12 | 4
- 11:00:00 | 10 | 10 | 1
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">hour</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rowtime</span><span class="p">,</span> <span class="n">productId</span><span class="p">,</span> <span class="n">orderId</span><span class="p">,</span> <span class="n">units</span>
+<span class="k">FROM</span> <span class="n">Orders</span>
+<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">hour</span><span class="p">)</span> <span class="k">ASC</span><span class="p">,</span> <span class="n">units</span> <span class="k">DESC</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span> <span class="o">|</span> <span class="n">orderId</span> <span class="o">|</span> <span class="n">units</span>
+<span class="c1">----------+-----------+---------+-------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">8</span> <span class="o">|</span> <span class="mi">20</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">5</span> <span class="o">|</span> <span class="mi">4</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">20</span> <span class="o">|</span> <span class="mi">7</span> <span class="o">|</span> <span class="mi">2</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">6</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">40</span> <span class="o">|</span> <span class="mi">11</span> <span class="o">|</span> <span class="mi">12</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">9</span> <span class="o">|</span> <span class="mi">6</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="mi">4</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">1</span></code></pre></div>
<p>Most queries will return results in the order that they were inserted,
because the engine is using streaming algorithms, but you should not rely on it.
For example, consider this:</p>
-<p>```sql
-SELECT STREAM *
-FROM Orders
-WHERE productId = 10
-UNION ALL
-SELECT STREAM *
-FROM Orders
-WHERE productId = 30;</p>
-
-<p>rowtime | productId | orderId | units
-âââ-+ââââ+âââ+ââ-
- 10:17:05 | 10 | 6 | 1
- 10:17:00 | 30 | 5 | 4
- 10:18:07 | 30 | 8 | 20
- 11:02:00 | 10 | 9 | 6
- 11:04:00 | 10 | 10 | 1
- 11:24:11 | 10 | 12 | 4
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span>
+<span class="k">FROM</span> <span class="n">Orders</span>
+<span class="k">WHERE</span> <span class="n">productId</span> <span class="o">=</span> <span class="mi">10</span>
+<span class="k">UNION</span> <span class="k">ALL</span>
+<span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span>
+<span class="k">FROM</span> <span class="n">Orders</span>
+<span class="k">WHERE</span> <span class="n">productId</span> <span class="o">=</span> <span class="mi">30</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span> <span class="o">|</span> <span class="n">orderId</span> <span class="o">|</span> <span class="n">units</span>
+<span class="c1">----------+-----------+---------+-------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">05</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">6</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">5</span> <span class="o">|</span> <span class="mi">4</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">18</span><span class="p">:</span><span class="mi">07</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">8</span> <span class="o">|</span> <span class="mi">20</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">02</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">9</span> <span class="o">|</span> <span class="mi">6</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">04</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">24</span><span class="p">:</span><span class="mi">11</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="mi">4</span></code></pre></div>
<p>The rows with <code>productId</code> = 30 are apparently out of order, probably because
the <code>Orders</code> stream was partitioned on <code>productId</code> and the partitioned streams
@@ -1063,25 +1031,23 @@ sent their data at different times.</p>
<p>If you require a particular ordering, add an explicit <code>ORDER BY</code>:</p>
-<p>```sql
-SELECT STREAM *
-FROM Orders
-WHERE productId = 10
-UNION ALL
-SELECT STREAM *
-FROM Orders
-WHERE productId = 30
-ORDER BY rowtime;</p>
-
-<p>rowtime | productId | orderId | units
-âââ-+ââââ+âââ+ââ-
- 10:17:00 | 30 | 5 | 4
- 10:17:05 | 10 | 6 | 1
- 10:18:07 | 30 | 8 | 20
- 11:02:00 | 10 | 9 | 6
- 11:04:00 | 10 | 10 | 1
- 11:24:11 | 10 | 12 | 4
-```</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span>
+<span class="k">FROM</span> <span class="n">Orders</span>
+<span class="k">WHERE</span> <span class="n">productId</span> <span class="o">=</span> <span class="mi">10</span>
+<span class="k">UNION</span> <span class="k">ALL</span>
+<span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span>
+<span class="k">FROM</span> <span class="n">Orders</span>
+<span class="k">WHERE</span> <span class="n">productId</span> <span class="o">=</span> <span class="mi">30</span>
+<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">rowtime</span><span class="p">;</span>
+
+ <span class="n">rowtime</span> <span class="o">|</span> <span class="n">productId</span> <span class="o">|</span> <span class="n">orderId</span> <span class="o">|</span> <span class="n">units</span>
+<span class="c1">----------+-----------+---------+-------</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">5</span> <span class="o">|</span> <span class="mi">4</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">17</span><span class="p">:</span><span class="mi">05</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">6</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">10</span><span class="p">:</span><span class="mi">18</span><span class="p">:</span><span class="mi">07</span> <span class="o">|</span> <span class="mi">30</span> <span class="o">|</span> <span class="mi">8</span> <span class="o">|</span> <span class="mi">20</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">02</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">9</span> <span class="o">|</span> <span class="mi">6</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">04</span><span class="p">:</span><span class="mi">00</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">1</span>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">24</span><span class="p">:</span><span class="mi">11</span> <span class="o">|</span> <span class="mi">10</span> <span class="o">|</span> <span class="mi">12</span> <span class="o">|</span> <span class="mi">4</span></code></pre></div>
<p>Calcite will probably implement the <code>UNION ALL</code> by merging using <code>rowtime</code>,
which is only slightly less efficient.</p>
@@ -1097,11 +1063,9 @@ implicitly, in order to make the GROUP B
<p>Streaming is disallowed. The set of rows never changes, and therefore a stream
would never return any rows.</p>
-<p>```sql
-> SELECT STREAM * FROM (VALUES (1, âabcâ));</p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="o">></span> <span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">VALUES</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'abc'</span><span class="p">));</span>
-<p>ERROR: Cannot stream VALUES
-```</p>
+<span class="n">ERROR</span><span class="p">:</span> <span class="n">Cannot</span> <span class="n">stream</span> <span class="k">VALUES</span></code></pre></div>
<h2 id="sliding-windows">Sliding windows</h2>
@@ -1112,13 +1076,11 @@ on a window of many rows.</p>
<p>Letâs look at an example.</p>
-<p><code>sql
-SELECT STREAM rowtime,
- productId,
- units,
- SUM(units) OVER (ORDER BY rowtime RANGE INTERVAL '1' HOUR PRECEDING) unitsLastHour
-FROM Orders;
-</code></p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span><span class="p">,</span>
+ <span class="n">productId</span><span class="p">,</span>
+ <span class="n">units</span><span class="p">,</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="n">units</span><span class="p">)</span> <span class="n">OVER</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">rowtime</span> <span class="n">RANGE</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span> <span class="n">PRECEDING</span><span class="p">)</span> <span class="n">unitsLastHour</span>
+<span class="k">FROM</span> <span class="n">Orders</span><span class="p">;</span></code></pre></div>
<p>The feature packs a lot of power with little effort. You can have multiple
functions in the <code>SELECT</code> clause, based on multiple window specifications.</p>
@@ -1126,20 +1088,18 @@ functions in the <code>SELECT</code> cla
<p>The following example returns orders whose average order size over the last
10 minutes is greater than the average order size for the last week.</p>
-<p><code>sql
-SELECT STREAM *
-FROM (
- SELECT STREAM rowtime,
- productId,
- units,
- AVG(units) OVER product (RANGE INTERVAL '10' MINUTE PRECEDING) AS m10,
- AVG(units) OVER product (RANGE INTERVAL '7' DAY PRECEDING) AS d7
- FROM Orders
- WINDOW product AS (
- ORDER BY rowtime
- PARTITION BY productId))
-WHERE m10 > d7;
-</code></p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="o">*</span>
+<span class="k">FROM</span> <span class="p">(</span>
+ <span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span><span class="p">,</span>
+ <span class="n">productId</span><span class="p">,</span>
+ <span class="n">units</span><span class="p">,</span>
+ <span class="k">AVG</span><span class="p">(</span><span class="n">units</span><span class="p">)</span> <span class="n">OVER</span> <span class="n">product</span> <span class="p">(</span><span class="n">RANGE</span> <span class="nb">INTERVAL</span> <span class="s1">'10'</span> <span class="k">MINUTE</span> <span class="n">PRECEDING</span><span class="p">)</span> <span class="k">AS</span> <span class="n">m10</span><span class="p">,</span>
+ <span class="k">AVG</span><span class="p">(</span><span class="n">units</span><span class="p">)</span> <span class="n">OVER</span> <span class="n">product</span> <span class="p">(</span><span class="n">RANGE</span> <span class="nb">INTERVAL</span> <span class="s1">'7'</span> <span class="k">DAY</span> <span class="n">PRECEDING</span><span class="p">)</span> <span class="k">AS</span> <span class="n">d7</span>
+ <span class="k">FROM</span> <span class="n">Orders</span>
+ <span class="n">WINDOW</span> <span class="n">product</span> <span class="k">AS</span> <span class="p">(</span>
+ <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">rowtime</span>
+ <span class="n">PARTITION</span> <span class="k">BY</span> <span class="n">productId</span><span class="p">))</span>
+<span class="k">WHERE</span> <span class="n">m10</span> <span class="o">></span> <span class="n">d7</span><span class="p">;</span></code></pre></div>
<p>For conciseness, here we use a syntax where you partially define a window
using a <code>WINDOW</code> clause and then refine the window in each <code>OVER</code> clause.
@@ -1164,13 +1124,11 @@ sliding window, but resets totals on a f
tumbling window? Such a pattern is called a <em>cascading window</em>. Here
is an example:</p>
-<p><code>sql
-SELECT STREAM rowtime,
- productId,
- units,
- SUM(units) OVER (PARTITION BY FLOOR(rowtime TO HOUR)) AS unitsSinceTopOfHour
-FROM Orders;
-</code></p>
+<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">rowtime</span><span class="p">,</span>
+ <span class="n">productId</span><span class="p">,</span>
+ <span class="n">units</span><span class="p">,</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="n">units</span><span class="p">)</span> <span class="n">OVER</span> <span class="p">(</span><span class="n">PARTITION</span> <span class="k">BY</span> <span class="n">FLOOR</span><span class="p">(</span><span class="n">rowtime</span> <span class="k">TO</span> <span class="n">HOUR</span><span class="p">))</span> <span class="k">AS</span> <span class="n">unitsSinceTopOfHour</span>
+<span class="k">FROM</span> <span class="n">Orders</span><span class="p">;</span></code></pre></div>
<p>It looks similar to a sliding window query, but the monotonic
expression occurs within the <code>PARTITION BY</code> clause of the window. As