You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by el...@apache.org on 2016/04/17 23:26:05 UTC
svn commit: r1739659 [16/18] - in /calcite/site: ./ avatica/
avatica/community/ avatica/develop/ avatica/docs/ avatica/downloads/
avatica/news/ avatica/news/2016/03/03/separate-project/
avatica/news/2016/03/18/release-1.7.1/ avatica/news/releases/ comm...
Modified: calcite/site/docs/stream.html
URL: http://svn.apache.org/viewvc/calcite/site/docs/stream.html?rev=1739659&r1=1739658&r2=1739659&view=diff
==============================================================================
--- calcite/site/docs/stream.html (original)
+++ calcite/site/docs/stream.html Sun Apr 17 21:26:04 2016
@@ -4,7 +4,7 @@
<meta charset="UTF-8">
<title>Streaming</title>
<meta name="viewport" content="width=device-width,initial-scale=1">
- <meta name="generator" content="Jekyll v2.4.0">
+ <meta name="generator" content="Jekyll v3.0.3">
<link rel="stylesheet" href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic,900">
<link rel="stylesheet" href="/css/screen.css">
<link rel="icon" type="image/x-icon" href="/favicon.ico">
@@ -813,35 +813,35 @@
streaming queries.</p>
<ul id="markdown-toc">
- <li><a href="#introduction">Introduction</a></li>
- <li><a href="#an-example-schema">An example schema</a></li>
- <li><a href="#a-simple-query">A simple query</a></li>
- <li><a href="#filtering-rows">Filtering rows</a></li>
- <li><a href="#projecting-expressions">Projecting expressions</a></li>
- <li><a href="#tumbling-windows">Tumbling windows</a></li>
- <li><a href="#tumbling-windows-improved">Tumbling windows, improved</a></li>
- <li><a href="#hopping-windows">Hopping windows</a></li>
- <li><a href="#grouping-sets">GROUPING SETS</a></li>
- <li><a href="#filtering-after-aggregation">Filtering after aggregation</a></li>
- <li><a href="#sub-queries-views-and-sqls-closure-property">Sub-queries, views and SQLâs closure property</a></li>
- <li><a href="#converting-between-streams-and-relations">Converting between streams and relations</a></li>
- <li><a href="#the-pie-chart-problem-relational-queries-on-streams">The âpie chartâ problem: Relational queries on streams</a></li>
- <li><a href="#sorting">Sorting</a></li>
- <li><a href="#table-constructor">Table constructor</a></li>
- <li><a href="#sliding-windows">Sliding windows</a></li>
- <li><a href="#cascading-windows">Cascading windows</a></li>
- <li><a href="#joining-streams-to-tables">Joining streams to tables</a></li>
- <li><a href="#joining-streams-to-streams">Joining streams to streams</a></li>
- <li><a href="#dml">DML</a></li>
- <li><a href="#punctuation">Punctuation</a></li>
- <li><a href="#state-of-the-stream">State of the stream</a> <ul>
- <li><a href="#implemented">Implemented</a></li>
- <li><a href="#not-implemented">Not implemented</a></li>
- <li><a href="#to-do-in-this-document">To do in this document</a></li>
+ <li><a href="#introduction" id="markdown-toc-introduction">Introduction</a></li>
+ <li><a href="#an-example-schema" id="markdown-toc-an-example-schema">An example schema</a></li>
+ <li><a href="#a-simple-query" id="markdown-toc-a-simple-query">A simple query</a></li>
+ <li><a href="#filtering-rows" id="markdown-toc-filtering-rows">Filtering rows</a></li>
+ <li><a href="#projecting-expressions" id="markdown-toc-projecting-expressions">Projecting expressions</a></li>
+ <li><a href="#tumbling-windows" id="markdown-toc-tumbling-windows">Tumbling windows</a></li>
+ <li><a href="#tumbling-windows-improved" id="markdown-toc-tumbling-windows-improved">Tumbling windows, improved</a></li>
+ <li><a href="#hopping-windows" id="markdown-toc-hopping-windows">Hopping windows</a></li>
+ <li><a href="#grouping-sets" id="markdown-toc-grouping-sets">GROUPING SETS</a></li>
+ <li><a href="#filtering-after-aggregation" id="markdown-toc-filtering-after-aggregation">Filtering after aggregation</a></li>
+ <li><a href="#sub-queries-views-and-sqls-closure-property" id="markdown-toc-sub-queries-views-and-sqls-closure-property">Sub-queries, views and SQLâs closure property</a></li>
+ <li><a href="#converting-between-streams-and-relations" id="markdown-toc-converting-between-streams-and-relations">Converting between streams and relations</a></li>
+ <li><a href="#the-pie-chart-problem-relational-queries-on-streams" id="markdown-toc-the-pie-chart-problem-relational-queries-on-streams">The âpie chartâ problem: Relational queries on streams</a></li>
+ <li><a href="#sorting" id="markdown-toc-sorting">Sorting</a></li>
+ <li><a href="#table-constructor" id="markdown-toc-table-constructor">Table constructor</a></li>
+ <li><a href="#sliding-windows" id="markdown-toc-sliding-windows">Sliding windows</a></li>
+ <li><a href="#cascading-windows" id="markdown-toc-cascading-windows">Cascading windows</a></li>
+ <li><a href="#joining-streams-to-tables" id="markdown-toc-joining-streams-to-tables">Joining streams to tables</a></li>
+ <li><a href="#joining-streams-to-streams" id="markdown-toc-joining-streams-to-streams">Joining streams to streams</a></li>
+ <li><a href="#dml" id="markdown-toc-dml">DML</a></li>
+ <li><a href="#punctuation" id="markdown-toc-punctuation">Punctuation</a></li>
+ <li><a href="#state-of-the-stream" id="markdown-toc-state-of-the-stream">State of the stream</a> <ul>
+ <li><a href="#implemented" id="markdown-toc-implemented">Implemented</a></li>
+ <li><a href="#not-implemented" id="markdown-toc-not-implemented">Not implemented</a></li>
+ <li><a href="#to-do-in-this-document" id="markdown-toc-to-do-in-this-document">To do in this document</a></li>
</ul>
</li>
- <li><a href="#functions">Functions</a></li>
- <li><a href="#references">References</a></li>
+ <li><a href="#functions" id="markdown-toc-functions">Functions</a></li>
+ <li><a href="#references" id="markdown-toc-references">References</a></li>
</ul>
<h2 id="introduction">Introduction</h2>
@@ -870,7 +870,7 @@ a stream, which is basically an in-memor
<li>Lots of existing tools can generate standard SQL.</li>
</ul>
-<p>If you donât use the <code>STREAM</code> keyword, you are back in regular
+<p>If you donât use the <code class="highlighter-rouge">STREAM</code> keyword, you are back in regular
standard SQL.</p>
<h2 id="an-example-schema">An example schema</h2>
@@ -878,107 +878,107 @@ standard SQL.</p>
<p>Our streaming SQL examples use the following schema:</p>
<ul>
- <li><code>Orders (rowtime, productId, orderId, units)</code> - a stream and a table</li>
- <li><code>Products (rowtime, productId, name)</code> - a table</li>
- <li><code>Shipments (rowtime, orderId)</code> - a stream</li>
+ <li><code class="highlighter-rouge">Orders (rowtime, productId, orderId, units)</code> - a stream and a table</li>
+ <li><code class="highlighter-rouge">Products (rowtime, productId, name)</code> - a table</li>
+ <li><code class="highlighter-rouge">Shipments (rowtime, orderId)</code> - a stream</li>
</ul>
<h2 id="a-simple-query">A simple query</h2>
<p>Letâs start with the simplest streaming query:</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>
+<figure 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="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>
+ <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></figure>
-<p>This query reads all columns and rows from the <code>Orders</code> stream.
+<p>This query reads all columns and rows from the <code class="highlighter-rouge">Orders</code> stream.
Like any streaming query, it never terminates. It outputs a record whenever
-a record arrives in <code>Orders</code>.</p>
+a record arrives in <code class="highlighter-rouge">Orders</code>.</p>
-<p>Type <code>Control-C</code> to terminate the query.</p>
+<p>Type <code class="highlighter-rouge">Control-C</code> to terminate the query.</p>
-<p>The <code>STREAM</code> keyword is the main extension in streaming SQL. It tells the
+<p>The <code class="highlighter-rouge">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>
-<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="o">*</span>
+<figure 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="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>
-<span class="mi">4</span> <span class="n">records</span> <span class="n">returned</span><span class="p">.</span></code></pre></div>
+<span class="mi">4</span> <span class="n">records</span> <span class="n">returned</span><span class="p">.</span></code></pre></figure>
<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
SQL semantics.</p>
-<p><code>Orders</code> is special, in that it has both a stream and a table. If you try to run
+<p><code class="highlighter-rouge">Orders</code> is special, in that it has both a stream and a table. If you try to run
a streaming query on a table, or a relational query on a stream, Calcite gives
an error:</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">Shipments</span><span class="p">;</span>
+<figure 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">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="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="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>
+<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></figure>
<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>Just as in regular SQL, you use a <code class="highlighter-rouge">WHERE</code> clause to filter rows:</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>
+<figure 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="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>
+ <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></figure>
<h1 id="projecting-expressions">Projecting expressions</h1>
-<p>Use expressions in the <code>SELECT</code> clause to choose which columns to return or
+<p>Use expressions in the <code class="highlighter-rouge">SELECT</code> clause to choose which columns to return or
compute expressions:</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>
+<figure 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="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>
+ <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></figure>
-<p>We recommend that you always include the <code>rowtime</code> column in the <code>SELECT</code>
+<p>We recommend that you always include the <code class="highlighter-rouge">rowtime</code> column in the <code class="highlighter-rouge">SELECT</code>
clause. Having a sorted timestamp in each stream and streaming query makes it
-possible to do advanced calculations later, such as <code>GROUP BY</code> and <code>JOIN</code>.</p>
+possible to do advanced calculations later, such as <code class="highlighter-rouge">GROUP BY</code> and <code class="highlighter-rouge">JOIN</code>.</p>
<h1 id="tumbling-windows">Tumbling windows</h1>
@@ -1006,9 +1006,9 @@ differences are:</p>
<p><img src="/img/window-types.png" alt="Window types" /></p>
<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>
+<code class="highlighter-rouge">GROUP BY</code>. Here is an example:</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">CEIL</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>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">CEIL</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>
@@ -1016,22 +1016,22 @@ differences are:</p>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">CEIL</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">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">30</span> <span class="o">|</span> <span class="mi">2</span> <span class="o">|</span> <span class="mi">24</span>
+<span class="c1">----------+-----------+---------+-------
+</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">30</span> <span class="o">|</span> <span class="mi">2</span> <span class="o">|</span> <span class="mi">24</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">1</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">20</span> <span class="o">|</span> <span class="mi">1</span> <span class="o">|</span> <span class="mi">7</span>
<span class="mi">12</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">12</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>
+ <span class="mi">12</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></figure>
<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, timestamped 11 oâclock.
+<code class="highlighter-rouge">productId</code> that had an order since 10 oâclock, timestamped 11 oâclock.
At 12 oâclock, it will emit
the orders that occurred between 11:00 and 12:00. Each input row contributes to
only one output row.</p>
<p>How did Calcite know that the 10:00:00 sub-totals were complete at 11:00:00,
-so that it could emit them? It knows that <code>rowtime</code> is increasing, and it knows
-that <code>CEIL(rowtime TO HOUR)</code> is also increasing. So, once it has seen a row
+so that it could emit them? It knows that <code class="highlighter-rouge">rowtime</code> is increasing, and it knows
+that <code class="highlighter-rouge">CEIL(rowtime TO HOUR)</code> is also increasing. So, once it has seen a row
at or after 11:00:00, it will never see a row that will contribute to a 10:00:00
total.</p>
@@ -1043,23 +1043,23 @@ and the stream has a mechanism (such as
to declare that a particular value will never be seen again, then
the column or expression is said to be <em>quasi-monotonic</em>.</p>
-<p>Without a monotonic or quasi-monotonic expression in the <code>GROUP BY</code> clause,
+<p>Without a monotonic or quasi-monotonic expression in the <code class="highlighter-rouge">GROUP BY</code> clause,
Calcite is
not able to make progress, and it will not allow the query:</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">productId</span><span class="p">,</span>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</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">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>
+<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></figure>
<p>Monotonic and quasi-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
that stream. We recommend that you give each stream a timestamp column called
-<code>rowtime</code>, but you can declare others to be monotonic, <code>orderId</code>, for example.</p>
+<code class="highlighter-rouge">rowtime</code>, but you can declare others to be monotonic, <code class="highlighter-rouge">orderId</code>, for example.</p>
<p>We discuss punctuation, watermarks, and other ways of making progress
<a href="#punctuation">below</a>.</p>
@@ -1068,54 +1068,54 @@ that stream. We recommend that you give
<p>The previous example of tumbling windows was easy to write because the window
was one hour. For intervals that are not a whole time unit, say 2 hours or
-2 hours and 17 minutes, you cannot use <code>CEIL</code>, and the expression gets more
+2 hours and 17 minutes, you cannot use <code class="highlighter-rouge">CEIL</code>, and the expression gets more
complicated.</p>
<p>Calcite supports an alternative syntax for tumbling windows:</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">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rowtime</span><span class="p">,</span>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span><span class="p">;</span>
+<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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">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">30</span> <span class="o">|</span> <span class="mi">2</span> <span class="o">|</span> <span class="mi">24</span>
+<span class="c1">----------+-----------+---------+-------
+</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">30</span> <span class="o">|</span> <span class="mi">2</span> <span class="o">|</span> <span class="mi">24</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">1</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">20</span> <span class="o">|</span> <span class="mi">1</span> <span class="o">|</span> <span class="mi">7</span>
<span class="mi">12</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">12</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>
+ <span class="mi">12</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></figure>
-<p>As you can see, it returns the same results as the previous query. The <code>TUMBLE</code>
+<p>As you can see, it returns the same results as the previous query. The <code class="highlighter-rouge">TUMBLE</code>
function returns a grouping key that is the same for all the rows that will end
-up in a given summary row; the <code>TUMBLE_END</code> function takes the same arguments
+up in a given summary row; the <code class="highlighter-rouge">TUMBLE_END</code> function takes the same arguments
and returns the time at which that window ends;
-there is also a <code>TUMBLE_START</code> function.</p>
+there is also a <code class="highlighter-rouge">TUMBLE_START</code> function.</p>
-<p><code>TUMBLE</code> has an optional parameter to align the window.
+<p><code class="highlighter-rouge">TUMBLE</code> has an optional parameter to align the window.
In the following example,
we use a 30 minute interval and 0:12 as the alignment time,
so the query emits summaries at 12 and 42 minutes past each hour:</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">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'30'</span> <span class="k">MINUTE</span><span class="p">,</span> <span class="n">TIME</span> <span class="s1">'0:12'</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rowtime</span><span class="p">,</span>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span>
+ <span class="n">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'30'</span> <span class="k">MINUTE</span><span class="p">,</span> <span class="n">TIME</span> <span class="s1">'0:12'</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">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'30'</span> <span class="k">MINUTE</span><span class="p">,</span> <span class="n">TIME</span> <span class="s1">'0:12'</span><span class="p">),</span>
+<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'30'</span> <span class="k">MINUTE</span><span class="p">,</span> <span class="n">TIME</span> <span class="s1">'0:12'</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">42</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="c1">----------+-----------+---------+-------
+</span> <span class="mi">10</span><span class="p">:</span><span class="mi">42</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">42</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">42</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">12</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">2</span> <span class="o">|</span> <span class="mi">7</span>
<span class="mi">11</span><span class="p">:</span><span class="mi">12</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>
- <span class="mi">11</span><span class="p">:</span><span class="mi">42</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">4</span></code></pre></div>
+ <span class="mi">11</span><span class="p">:</span><span class="mi">42</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">4</span></code></pre></figure>
<h1 id="hopping-windows">Hopping windows</h1>
@@ -1127,48 +1127,48 @@ from 08:00 to 11:00 (or 10:59.9 if weâ�
and a row timestamped 12:00 containing data from 09:00
to 12:00.</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">HOP_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'3'</span> <span class="n">HOUR</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rowtime</span><span class="p">,</span>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span>
+ <span class="n">HOP_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'3'</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="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">HOP</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'3'</span> <span class="n">HOUR</span><span class="p">);</span>
+<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">HOP</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'3'</span> <span class="n">HOUR</span><span class="p">);</span>
<span class="n">rowtime</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">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">4</span> <span class="o">|</span> <span class="mi">27</span>
- <span class="mi">12</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">8</span> <span class="o">|</span> <span class="mi">50</span></code></pre></div>
+<span class="c1">----------+----------+-------
+</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">4</span> <span class="o">|</span> <span class="mi">27</span>
+ <span class="mi">12</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">8</span> <span class="o">|</span> <span class="mi">50</span></code></pre></figure>
<p>In this query, because the retain period is 3 times the emit period, every input
-row contributes to exactly 3 output rows. Imagine that the <code>HOP</code> function
+row contributes to exactly 3 output rows. Imagine that the <code class="highlighter-rouge">HOP</code> function
generates a collection of group keys for incoming row, and places its values
in the accumulators of each of those group keys. For example,
-<code>HOP(10:18:00, INTERVAL '1' HOUR, INTERVAL '3')</code> generates 3 periods</p>
+<code class="highlighter-rouge">HOP(10:18:00, INTERVAL '1' HOUR, INTERVAL '3')</code> generates 3 periods</p>
-<p><code>[08:00, 09:00)
+<p><code class="highlighter-rouge">[08:00, 09:00)
[09:00, 10:00)
[10:00, 11:00)
</code></p>
<p>This raises the possibility of allowing user-defined partitioning functions
-for users who are not happy with the built-in functions <code>HOP</code> and <code>TUMBLE</code>.</p>
+for users who are not happy with the built-in functions <code class="highlighter-rouge">HOP</code> and <code class="highlighter-rouge">TUMBLE</code>.</p>
<p>We can build complex complex expressions such as an exponentially decaying
moving average:</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">HOP_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">),</span>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">HOP_END</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">SUM</span><span class="p">(</span><span class="n">unitPrice</span> <span class="o">*</span> <span class="n">EXP</span><span class="p">((</span><span class="n">rowtime</span> <span class="o">-</span> <span class="n">HOP_START</span><span class="p">(</span><span class="n">rowtime</span><span class="p">))</span> <span class="k">SECOND</span> <span class="o">/</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">))</span>
- <span class="o">/</span> <span class="k">SUM</span><span class="p">(</span><span class="n">EXP</span><span class="p">((</span><span class="n">rowtime</span> <span class="o">-</span> <span class="n">HOP_START</span><span class="p">(</span><span class="n">rowtime</span><span class="p">))</span> <span class="k">SECOND</span> <span class="o">/</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">))</span>
+ <span class="k">SUM</span><span class="p">(</span><span class="n">unitPrice</span> <span class="o">*</span> <span class="n">EXP</span><span class="p">((</span><span class="n">rowtime</span> <span class="o">-</span> <span class="n">HOP_START</span><span class="p">(</span><span class="n">rowtime</span><span class="p">))</span> <span class="k">SECOND</span> <span class="o">/</span> <span class="n">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">))</span>
+ <span class="o">/</span> <span class="k">SUM</span><span class="p">(</span><span class="n">EXP</span><span class="p">((</span><span class="n">rowtime</span> <span class="o">-</span> <span class="n">HOP_START</span><span class="p">(</span><span class="n">rowtime</span><span class="p">))</span> <span class="k">SECOND</span> <span class="o">/</span> <span class="n">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</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">HOP</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="k">SECOND</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span>
- <span class="n">productId</span></code></pre></div>
+<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">HOP</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</span> <span class="k">SECOND</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span>
+ <span class="n">productId</span></code></pre></figure>
<p>Emits:</p>
<ul>
- <li>a row at <code>11:00:00</code> containing rows in <code>[10:00:00, 11:00:00)</code>;</li>
- <li>a row at <code>11:00:01</code> containing rows in <code>[10:00:01, 11:00:01)</code>.</li>
+ <li>a row at <code class="highlighter-rouge">11:00:00</code> containing rows in <code class="highlighter-rouge">[10:00:00, 11:00:00)</code>;</li>
+ <li>a row at <code class="highlighter-rouge">11:00:01</code> containing rows in <code class="highlighter-rouge">[10:00:01, 11:00:01)</code>.</li>
</ul>
<p>The expression weighs recent orders more heavily than older orders.
@@ -1176,96 +1176,96 @@ Extending the window from 1 hour to 2 ho
virtually no effect on the accuracy of the result (but use more memory
and compute).</p>
-<p>Note that we use <code>HOP_START</code> inside an aggregate function (<code>SUM</code>) because it
+<p>Note that we use <code class="highlighter-rouge">HOP_START</code> inside an aggregate function (<code class="highlighter-rouge">SUM</code>) because it
is a value that is constant for all rows within a sub-total. This
-would not be allowed for typical aggregate functions (<code>SUM</code>, <code>COUNT</code>
+would not be allowed for typical aggregate functions (<code class="highlighter-rouge">SUM</code>, <code class="highlighter-rouge">COUNT</code>
etc.).</p>
-<p>If you are familiar with <code>GROUPING SETS</code>, you may notice that partitioning
-functions can be seen as a generalization of <code>GROUPING SETS</code>, in that they
+<p>If you are familiar with <code class="highlighter-rouge">GROUPING SETS</code>, you may notice that partitioning
+functions can be seen as a generalization of <code class="highlighter-rouge">GROUPING SETS</code>, in that they
allow an input row to contribute to multiple sub-totals.
-The auxiliary functions for <code>GROUPING SETS</code>,
-such as <code>GROUPING()</code> and <code>GROUP_ID</code>,
+The auxiliary functions for <code class="highlighter-rouge">GROUPING SETS</code>,
+such as <code class="highlighter-rouge">GROUPING()</code> and <code class="highlighter-rouge">GROUP_ID</code>,
can be used inside aggregate functions, so it is not surprising that
-<code>HOP_START</code> and <code>HOP_END</code> can be used in the same way.</p>
+<code class="highlighter-rouge">HOP_START</code> and <code class="highlighter-rouge">HOP_END</code> can be used in the same way.</p>
<h1 id="grouping-sets">GROUPING SETS</h1>
-<p><code>GROUPING SETS</code> is valid for a streaming query provided that every
+<p><code class="highlighter-rouge">GROUPING SETS</code> is valid for a streaming query provided that every
grouping set contains a monotonic or quasi-monotonic expression.</p>
-<p><code>CUBE</code> and <code>ROLLUP</code> are not valid for streaming query, because they will
+<p><code class="highlighter-rouge">CUBE</code> and <code class="highlighter-rouge">ROLLUP</code> are not valid for streaming query, because they will
produce at least one grouping set that aggregates everything (like
-<code>GROUP BY ()</code>).</p>
+<code class="highlighter-rouge">GROUP BY ()</code>).</p>
<h1 id="filtering-after-aggregation">Filtering after aggregation</h1>
-<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>As in standard SQL, you can apply a <code class="highlighter-rouge">HAVING</code> clause to filter rows emitted by
+a streaming <code class="highlighter-rouge">GROUP BY</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">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rowtime</span><span class="p">,</span>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span>
+<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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="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>
+ <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></figure>
<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
+<p>The previous <code class="highlighter-rouge">HAVING</code> query can be expressed using a <code class="highlighter-rouge">WHERE</code> clause on a
sub-query:</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>
+<figure 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">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</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="k">SELECT</span> <span class="n">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span><span class="p">)</span>
+ <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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="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>
+ <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></figure>
-<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
-they enter the <code>GROUP BY</code> clause.)</p>
+<p><code class="highlighter-rouge">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 class="highlighter-rouge">WHERE</code> filters rows before
+they enter the <code class="highlighter-rouge">GROUP BY</code> clause.)</p>
<p>Since then, SQL has become a mathematically closed language, which means that
any operation you can perform on a table can also perform on a query.</p>
<p>The <em>closure property</em> of SQL is extremely powerful. Not only does it render
-<code>HAVING</code> obsolete (or, at least, reduce it to syntactic sugar), it makes views
+<code class="highlighter-rouge">HAVING</code> obsolete (or, at least, reduce it to syntactic sugar), it makes views
possible:</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">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span>
+<figure 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">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span><span class="p">;</span>
+ <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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="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>
+ <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></figure>
-<p>Sub-queries in the <code>FROM</code> clause are sometimes referred to as âinline viewsâ,
+<p>Sub-queries in the <code class="highlighter-rouge">FROM</code> clause are sometimes referred to as âinline viewsâ,
but really, they are more fundamental than views. Views are just a convenient
way to carve your SQL into manageable chunks by giving the pieces names and
storing them in the metadata repository.</p>
@@ -1275,38 +1275,38 @@ than they are on relations. Streaming qu
operators all running continuously, and often those pipelines get quite long.
Nested queries and views help to express and manage those pipelines.</p>
-<p>And, by the way, a <code>WITH</code> clause can accomplish the same as a sub-query or
+<p>And, by the way, a <code class="highlighter-rouge">WITH</code> clause can accomplish the same as a sub-query or
a view:</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">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span>
+<figure 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">TUMBLE_END</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span><span class="p">),</span> <span class="n">productId</span><span class="p">)</span>
+ <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">TUMBLE</span><span class="p">(</span><span class="n">rowtime</span><span class="p">,</span> <span class="n">INTERVAL</span> <span class="s1">'1'</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="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>
+ <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></figure>
<h1 id="converting-between-streams-and-relations">Converting between streams and relations</h1>
-<p>Look back at the definition of the <code>HourlyOrderTotals</code> view.
+<p>Look back at the definition of the <code class="highlighter-rouge">HourlyOrderTotals</code> view.
Is the view a stream or a relation?</p>
-<p>It does not contain the <code>STREAM</code> keyword, so it is a relation.
+<p>It does not contain the <code class="highlighter-rouge">STREAM</code> keyword, so it is a relation.
However, it is a relation that can be converted into a stream.</p>
<p>You can use it in both relational and streaming queries:</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>
+<figure 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="n">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>
@@ -1315,14 +1315,14 @@ However, it is a relation that can be co
<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>
+<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></figure>
<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
in streaming SQL is defined as a relational query and converted to a stream
-using the <code>STREAM</code> keyword in the top-most <code>SELECT</code>.</p>
+using the <code class="highlighter-rouge">STREAM</code> keyword in the top-most <code class="highlighter-rouge">SELECT</code>.</p>
-<p>If the <code>STREAM</code> keyword is present in sub-queries or view definitions, it has no
+<p>If the <code class="highlighter-rouge">STREAM</code> keyword is present in sub-queries or view definitions, it has no
effect.</p>
<p>At query preparation time, Calcite figures out whether the relations referenced
@@ -1342,15 +1342,15 @@ number of orders for each product over t
<p><img src="/img/pie-chart.png" alt="Pie chart" /></p>
-<p>But the <code>Orders</code> stream only contains a few records, not an hourâs summary.
+<p>But the <code class="highlighter-rouge">Orders</code> stream only contains a few records, not an hourâs summary.
We need to run a relational query on the history of the stream:</p>
-<div class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</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>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</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">FROM</span> <span class="n">Orders</span>
-<span class="k">WHERE</span> <span class="n">rowtime</span> <span class="k">BETWEEN</span> <span class="k">current_timestamp</span> <span class="o">-</span> <span class="nb">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span>
- <span class="k">AND</span> <span class="k">current_timestamp</span><span class="p">;</span></code></pre></div>
+<span class="k">WHERE</span> <span class="n">rowtime</span> <span class="k">BETWEEN</span> <span class="k">current_timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1'</span> <span class="n">HOUR</span>
+ <span class="k">AND</span> <span class="k">current_timestamp</span><span class="p">;</span></code></pre></figure>
-<p>If the history of the <code>Orders</code> stream is being spooled to the <code>Orders</code> table,
+<p>If the history of the <code class="highlighter-rouge">Orders</code> stream is being spooled to the <code class="highlighter-rouge">Orders</code> table,
we can answer the query, albeit at a high cost. Better, if we can tell the
system to materialize one hour summary into a table,
maintain it continuously as the stream flows,
@@ -1358,31 +1358,31 @@ and automatically rewrite queries to use
<h1 id="sorting">Sorting</h1>
-<p>The story for <code>ORDER BY</code> is similar to <code>GROUP BY</code>.
+<p>The story for <code class="highlighter-rouge">ORDER BY</code> is similar to <code class="highlighter-rouge">GROUP BY</code>.
The syntax looks like regular SQL, but Calcite must be sure that it can deliver
timely results. It therefore requires a monotonic expression on the leading edge
-of your <code>ORDER BY</code> key.</p>
+of your <code class="highlighter-rouge">ORDER BY</code> key.</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">CEIL</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>
+<figure class="highlight"><pre><code class="language-sql" data-lang="sql"><span class="k">SELECT</span> <span class="n">STREAM</span> <span class="n">CEIL</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">CEIL</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="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>
+ <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></figure>
<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>
-<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>
+<figure 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>
@@ -1391,21 +1391,21 @@ For example, consider this:</p>
<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="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>
[... 1578 lines stripped ...]