Flink Sql With 1.14 query - window functions (TVFs)

Streaming media

Windows is the core of handling infinite streams. Windows divides streams into "buckets" of limited size, on which we can apply calculations. This document focuses on how to perform windowing in Flink SQL and how programmers can get the most from the functions they provide.

Apache Flink provides several window table valued functions (TVF) to divide the elements of the table into windows, including:

Tumble the window
Jump window
Cumulative window
Session window (to be supported)

Note that each element can logically belong to multiple windows, depending on the window table valued function you use. For example, HOP windows create overlapping windows where a single element can be assigned to multiple windows.

Windowing TVF is a polymorphic table function (PTF) defined by Fl ink. PTF is a part of SQL 2016 standard. It is a special table function, but the table can be used as a parameter. PTF is a powerful function to change the shape of tables. Because PTF is semantically similar to a table, their calls occur in the SELECT clause of the statement.

Windowing TVF is a traditional Grouped Window Functions Alternative. Windowed TVF is more in line with SQL standards and more powerful, and can support complex window based computing, such as Window TopN and Window Join. But, Grouping window function Only window aggregation is supported.

Learn more about how to apply further calculations based on window TVF:

Window function #

Apache Flink provides three built-in windows TVF # tunnel: HOP and CUMULATE The return value of windowed TVF is a new relationship, including all columns of the original relationship and three additional columns named "window_start", "window_end" and "window_time" to indicate the allocated window. The "window_time" field is the time of the window after opening TVF Time attribute , can be used for subsequent time-based operations, such as another windowed TVF, or interval joins，over aggregations . Value window_time is always equal to window_end - 1ms.

Tumbling #

The toggle function assigns each element to a window of a specified window size. TUMBLE windows have a fixed size and do not overlap. For example, suppose you specify a scrolling window with a size of 5 minutes. In this case, Flink will evaluate the current window and start a new window every five minutes, as shown in the figure below.

This function is based on Time attribute Each row of the mbturn window is assigned a column. The return value of is a new relationship, including all columns of the original relationship and three additional columns named "window_start", "window_end" and "window_time" to indicate the allocated window. The original time attribute "timecol" will be the regular timestamp column after the window TVF. TUMBLE

The toggle function accepts three required parameters and one optional parameter:

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-sql">TUMBLE(<span style="color:#000000"><strong>TABLE</strong></span> <span style="color:#000000"><strong>data</strong></span>, <span style="color:#000000"><strong>DESCRIPTOR</strong></span>(timecol), <span style="color:#000000"><strong>size</strong></span> [, <span style="color:#000000"><strong>offset</strong></span> ])
</code></span></span>

data: is a table parameter, which can be any relationship with the time attribute column.
timecol: is a column descriptor that indicates which of the data Time attribute Columns should be mapped to flipped windows.
size: Specifies the duration of scrolling window width.
Offset: is an optional parameter used to specify the offset of the starting position of the window.

This is an example of a call to a table Bid:

<span style="color:#000000"><span style="background-color:#ffffff"><code class="language-sql"><span style="color:#999988"><em>-- tables must have time attribute, e.g. `bidtime` in this table
</em></span>Flink <span style="color:#000000"><strong>SQL</strong></span><span style="color:#000000"><strong>></strong></span> <span style="color:#000000"><strong>desc</strong></span> Bid;
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>-------------+------------------------+------+-----+--------+---------------------------------+
</em></span><span style="color:#000000"><strong>|</strong></span>        name <span style="color:#000000"><strong>|</strong></span>                   <span style="color:#000000"><strong>type</strong></span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>null</strong></span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>key</strong></span> <span style="color:#000000"><strong>|</strong></span> extras <span style="color:#000000"><strong>|</strong></span>                       watermark <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>-------------+------------------------+------+-----+--------+---------------------------------+
</em></span><span style="color:#000000"><strong>|</strong></span>     bidtime <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>TIMESTAMP</strong></span>(<span style="color:#009999">3</span>) <span style="color:#000000"><strong>*</strong></span>ROWTIME<span style="color:#000000"><strong>*</strong></span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>true</strong></span> <span style="color:#000000"><strong>|</strong></span>     <span style="color:#000000"><strong>|</strong></span>        <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>`</strong></span>bidtime<span style="color:#000000"><strong>`</strong></span> <span style="color:#000000"><strong>-</strong></span> <span style="color:#0086b3">INTERVAL</span> <span style="color:#dd1144">'1'</span> <span style="color:#000000"><strong>SECOND</strong></span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span>       price <span style="color:#000000"><strong>|</strong></span>         <span style="color:#0086b3">DECIMAL</span>(<span style="color:#009999">10</span>, <span style="color:#009999">2</span>) <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>true</strong></span> <span style="color:#000000"><strong>|</strong></span>     <span style="color:#000000"><strong>|</strong></span>        <span style="color:#000000"><strong>|</strong></span>                                 <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span>        item <span style="color:#000000"><strong>|</strong></span>                 STRING <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>true</strong></span> <span style="color:#000000"><strong>|</strong></span>     <span style="color:#000000"><strong>|</strong></span>        <span style="color:#000000"><strong>|</strong></span>                                 <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>-------------+------------------------+------+-----+--------+---------------------------------+
</em></span>
Flink <span style="color:#000000"><strong>SQL</strong></span><span style="color:#000000"><strong>></strong></span> <span style="color:#000000"><strong>SELECT</strong></span> <span style="color:#000000"><strong>*</strong></span> <span style="color:#000000"><strong>FROM</strong></span> Bid;
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+-------+------+
</em></span><span style="color:#000000"><strong>|</strong></span>          bidtime <span style="color:#000000"><strong>|</strong></span> price <span style="color:#000000"><strong>|</strong></span> item <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+-------+------+
</em></span><span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">05</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">4</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>C</strong></span>    <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">07</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">2</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> A    <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">09</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">5</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> D    <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">11</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">3</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> B    <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">13</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">1</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> E    <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">17</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">6</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> F    <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+-------+------+
</em></span>
<span style="color:#999988"><em>-- NOTE: Currently Flink doesn't support evaluating individual window table-valued function,
</em></span><span style="color:#999988"><em>--  window table-valued function should be used with aggregate operation,
</em></span><span style="color:#999988"><em>--  this example is just used for explaining the syntax and the data produced by table-valued function.
</em></span>Flink <span style="color:#000000"><strong>SQL</strong></span><span style="color:#000000"><strong>></strong></span> <span style="color:#000000"><strong>SELECT</strong></span> <span style="color:#000000"><strong>*</strong></span> <span style="color:#000000"><strong>FROM</strong></span> <span style="color:#000000"><strong>TABLE</strong></span>(
   TUMBLE(<span style="color:#000000"><strong>TABLE</strong></span> Bid, <span style="color:#000000"><strong>DESCRIPTOR</strong></span>(bidtime), <span style="color:#0086b3">INTERVAL</span> <span style="color:#dd1144">'10'</span> MINUTES));
<span style="color:#999988"><em>-- or with the named params
</em></span><span style="color:#999988"><em>-- note: the DATA param must be the first
</em></span>Flink <span style="color:#000000"><strong>SQL</strong></span><span style="color:#000000"><strong>></strong></span> <span style="color:#000000"><strong>SELECT</strong></span> <span style="color:#000000"><strong>*</strong></span> <span style="color:#000000"><strong>FROM</strong></span> <span style="color:#000000"><strong>TABLE</strong></span>(
   TUMBLE(
     <span style="color:#000000"><strong>DATA</strong></span> <span style="color:#000000"><strong>=></strong></span> <span style="color:#000000"><strong>TABLE</strong></span> Bid,
     TIMECOL <span style="color:#000000"><strong>=></strong></span> <span style="color:#000000"><strong>DESCRIPTOR</strong></span>(bidtime),
     <span style="color:#000000"><strong>SIZE</strong></span> <span style="color:#000000"><strong>=></strong></span> <span style="color:#0086b3">INTERVAL</span> <span style="color:#dd1144">'10'</span> MINUTES));
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+-------+------+------------------+------------------+-------------------------+
</em></span><span style="color:#000000"><strong>|</strong></span>          bidtime <span style="color:#000000"><strong>|</strong></span> price <span style="color:#000000"><strong>|</strong></span> item <span style="color:#000000"><strong>|</strong></span>     window_start <span style="color:#000000"><strong>|</strong></span>       window_end <span style="color:#000000"><strong>|</strong></span>            window_time  <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+-------+------+------------------+------------------+-------------------------+
</em></span><span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">05</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">4</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#000000"><strong>C</strong></span>    <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">10</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">09</span>:<span style="color:#009999">59</span>.<span style="color:#009999">999</span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">07</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">2</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> A    <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">10</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">09</span>:<span style="color:#009999">59</span>.<span style="color:#009999">999</span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">09</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">5</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> D    <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">10</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">09</span>:<span style="color:#009999">59</span>.<span style="color:#009999">999</span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">11</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">3</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> B    <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">10</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">20</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">19</span>:<span style="color:#009999">59</span>.<span style="color:#009999">999</span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">13</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">1</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> E    <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">10</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">20</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">19</span>:<span style="color:#009999">59</span>.<span style="color:#009999">999</span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">17</span> <span style="color:#000000"><strong>|</strong></span>  <span style="color:#009999">6</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> F    <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">10</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">20</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">19</span>:<span style="color:#009999">59</span>.<span style="color:#009999">999</span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+-------+------+------------------+------------------+-------------------------+
</em></span>
<span style="color:#999988"><em>-- apply aggregation on the tumbling windowed table
</em></span>Flink <span style="color:#000000"><strong>SQL</strong></span><span style="color:#000000"><strong>></strong></span> <span style="color:#000000"><strong>SELECT</strong></span> window_start, window_end, <span style="color:#000000"><strong>SUM</strong></span>(price)
  <span style="color:#000000"><strong>FROM</strong></span> <span style="color:#000000"><strong>TABLE</strong></span>(
    TUMBLE(<span style="color:#000000"><strong>TABLE</strong></span> Bid, <span style="color:#000000"><strong>DESCRIPTOR</strong></span>(bidtime), <span style="color:#0086b3">INTERVAL</span> <span style="color:#dd1144">'10'</span> MINUTES))
  <span style="color:#000000"><strong>GROUP</strong></span> <span style="color:#000000"><strong>BY</strong></span> window_start, window_end;
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+------------------+-------+
</em></span><span style="color:#000000"><strong>|</strong></span>     window_start <span style="color:#000000"><strong>|</strong></span>       window_end <span style="color:#000000"><strong>|</strong></span> price <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+------------------+-------+
</em></span><span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">10</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">11</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">10</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">2020</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">04</span><span style="color:#000000"><strong>-</strong></span><span style="color:#009999">15</span> <span style="color:#009999">08</span>:<span style="color:#009999">20</span> <span style="color:#000000"><strong>|</strong></span> <span style="color:#009999">10</span>.<span style="color:#009999">00</span> <span style="color:#000000"><strong>|</strong></span>
<span style="color:#000000"><strong>+</strong></span><span style="color:#999988"><em>------------------+------------------+-------+
</em></span></code></span></span>

Note: in order to better understand the windowing behavior, we have simplified the display of timestamp value to not display trailing zeros. For example, if the type is 2020-04-15 08:05, it should be 2020-04-15 08:05:00.000. In Flink SQL client, TIMESTAMP(3).

Jump #

This HOP function assigns elements to fixed length windows. Like the toggle window function, the size of the window is configured by the window size parameter. An additional window sliding parameter controls the starting frequency of a jump window. Therefore, if the slide is smaller than the window size, the jump window may overlap. In this case, the element is assigned to multiple windows. Jumping windows are also called "sliding windows".

For example, you can have a window size of 10 minutes sliding for 5 minutes. In this way, you will get a window every 5 minutes containing events that have arrived in the past 10 minutes, as shown in the figure below.

The HOP function allocates a window covering the rows within the size interval, and Time attribute Column moves each slide. The return value HOP of is a new relationship, including all columns of the original relationship and three additional columns named "window_start", "window_end" and "window_time" to indicate the allocated window. The original time attribute "timecol" will be the regular timestamp column after the window TVF.

The HOP accepts four required parameters and one optional parameter:

HOP(TABLE data, DESCRIPTOR(timecol), slide, size [, offset ])

data: is a table parameter, which can be any relationship with the time attribute column.
timecol: is a column descriptor that indicates which of the data Time attribute Columns should be mapped to jump windows.
slide: is a duration that specifies the duration between the start of the sequential jump window
size: is the duration of specifying the width of the jump window.
Offset: is an optional parameter used to specify the offset of the starting position of the window.

This is an example of a call to a table Bid:

-- NOTE: Currently Flink doesn't support evaluating individual window table-valued function,
--  window table-valued function should be used with aggregate operation,
--  this example is just used for explaining the syntax and the data produced by table-valued function.
> SELECT * FROM TABLE(
    HOP(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '5' MINUTES, INTERVAL '10' MINUTES));
-- or with the named params
-- note: the DATA param must be the first
> SELECT * FROM TABLE(
    HOP(
      DATA => TABLE Bid,
      TIMECOL => DESCRIPTOR(bidtime),
      SLIDE => INTERVAL '5' MINUTES,
      SIZE => INTERVAL '10' MINUTES));
+------------------+-------+------+------------------+------------------+-------------------------+
|          bidtime | price | item |     window_start |       window_end |           window_time   |
+------------------+-------+------+------------------+------------------+-------------------------+
| 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
| 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
| 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
| 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
| 2020-04-15 08:09 |  5.00 | D    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
| 2020-04-15 08:09 |  5.00 | D    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
| 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
| 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
| 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:05 | 2020-04-15 08:15 | 2020-04-15 08:14:59.999 |
| 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
| 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
| 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:15 | 2020-04-15 08:25 | 2020-04-15 08:24:59.999 |
+------------------+-------+------+------------------+------------------+-------------------------+

-- apply aggregation on the hopping windowed table
> SELECT window_start, window_end, SUM(price)
  FROM TABLE(
    HOP(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '5' MINUTES, INTERVAL '10' MINUTES))
  GROUP BY window_start, window_end;
+------------------+------------------+-------+
|     window_start |       window_end | price |
+------------------+------------------+-------+
| 2020-04-15 08:00 | 2020-04-15 08:10 | 11.00 |
| 2020-04-15 08:05 | 2020-04-15 08:15 | 15.00 |
| 2020-04-15 08:10 | 2020-04-15 08:20 | 10.00 |
| 2020-04-15 08:15 | 2020-04-15 08:25 |  6.00 |
+------------------+------------------+-------+

Cumulate #

Cumulative windows are useful in some scenarios, such as tumbling windows that are triggered in advance within a fixed window interval. For example, the daily dashboard draws cumulative UVs from 00:00 to every minute, and UVs at 10:00 represent the total number of UVs from 00:00 to 10:00. This can be achieved easily and effectively through the CUMULATE window.

The CUMULATE function assigns elements to windows that cover rows within the initial step interval and expands each step (keeping the window fixed at the beginning) until the maximum window size. You can think of the CUMULATE function as a window in which the maximum window size is applied first, and split each flipped window into several windows with the same difference in window start and window end steps. So the cumulative windows do overlap and have no fixed size.

For example, you can have a cumulative window with 1 hour step and 1 day maximum size, and you will get windows: [00:00, 01:00], [00:00, 02:00], [00:00, 03:00),..., [00:00, 24:00) every day.

These CUMULATE functions are based on Time attribute Column assignment window. The return value CUMULATE of is a new relationship, including all columns of the original relationship and three additional columns named "window_start", "window_end" and "window_time" to indicate the allocated window. The original time attribute "timecol" will be the regular timestamp column after the window TVF.

CUMULATE accepts four required parameters and one optional parameter:

CUMULATE(TABLE data, DESCRIPTOR(timecol), step, size)

data: is a table parameter, which can be any relationship with the time attribute column.
timecol: is a column descriptor that indicates which of the data Time attribute Columns should be mapped to flipped windows.
step: Specifies the duration of increasing window size between the end of successive cumulative windows.
size: is the duration of specifying the maximum width of the cumulative window. size must be an integer multiple of step.
Offset: is an optional parameter used to specify the offset of the starting position of the window.

The following is an example of a call to the Bid table:

-- NOTE: Currently Flink doesn't support evaluating individual window table-valued function,
--  window table-valued function should be used with aggregate operation,
--  this example is just used for explaining the syntax and the data produced by table-valued function.
> SELECT * FROM TABLE(
    CUMULATE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '2' MINUTES, INTERVAL '10' MINUTES));
-- or with the named params
-- note: the DATA param must be the first
> SELECT * FROM TABLE(
    CUMULATE(
      DATA => TABLE Bid,
      TIMECOL => DESCRIPTOR(bidtime),
      STEP => INTERVAL '2' MINUTES,
      SIZE => INTERVAL '10' MINUTES));
+------------------+-------+------+------------------+------------------+-------------------------+
|          bidtime | price | item |     window_start |       window_end |            window_time  |
+------------------+-------+------+------------------+------------------+-------------------------+
| 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:06 | 2020-04-15 08:05:59.999 |
| 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:08 | 2020-04-15 08:07:59.999 |
| 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
| 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:00 | 2020-04-15 08:08 | 2020-04-15 08:07:59.999 |
| 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
| 2020-04-15 08:09 |  5.00 | D    | 2020-04-15 08:00 | 2020-04-15 08:10 | 2020-04-15 08:09:59.999 |
| 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:12 | 2020-04-15 08:11:59.999 |
| 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:14 | 2020-04-15 08:13:59.999 |
| 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:16 | 2020-04-15 08:15:59.999 |
| 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:18 | 2020-04-15 08:17:59.999 |
| 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
| 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:14 | 2020-04-15 08:13:59.999 |
| 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:16 | 2020-04-15 08:15:59.999 |
| 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:18 | 2020-04-15 08:17:59.999 |
| 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
| 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:10 | 2020-04-15 08:18 | 2020-04-15 08:17:59.999 |
| 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:10 | 2020-04-15 08:20 | 2020-04-15 08:19:59.999 |
+------------------+-------+------+------------------+------------------+-------------------------+

-- apply aggregation on the cumulating windowed table
> SELECT window_start, window_end, SUM(price)
  FROM TABLE(
    CUMULATE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '2' MINUTES, INTERVAL '10' MINUTES))
  GROUP BY window_start, window_end;
+------------------+------------------+-------+
|     window_start |       window_end | price |
+------------------+------------------+-------+
| 2020-04-15 08:00 | 2020-04-15 08:06 |  4.00 |
| 2020-04-15 08:00 | 2020-04-15 08:08 |  6.00 |
| 2020-04-15 08:00 | 2020-04-15 08:10 | 11.00 |
| 2020-04-15 08:10 | 2020-04-15 08:12 |  3.00 |
| 2020-04-15 08:10 | 2020-04-15 08:14 |  4.00 |
| 2020-04-15 08:10 | 2020-04-15 08:16 |  4.00 |
| 2020-04-15 08:10 | 2020-04-15 08:18 | 10.00 |
| 2020-04-15 08:10 | 2020-04-15 08:20 | 10.00 |
+------------------+------------------+-------+

Window offset #

Offset is an optional parameter that can be used to change the window assignment. It can be a positive duration and a negative duration. The default value for window offset is 0. If you set different offset values, the same record may be assigned to different windows.
For example, for the timestamp record of a Tumble window with a size of 10 minutes on 2021-06-30 00:00:04, which window will it be assigned to?

If the offset value is - 16 MINUTE, the record is assigned to the window [2021-06-29 23:54:00, 2021-06-30 00:04:00).
If the offset value is - 6 MINUTE, the record is assigned to the window [2021-06-29 23:54:00, 2021-06-30 00:04:00).
If offset is - 4 MINUTE, the record is assigned to the window [2021-06-29 23:56:00, 2021-06-30 00:06:00).
If offset is 0, the record is assigned to the window [2021-06-30 00:00:00, 2021-06-30 00:10:00).
If offset is 4 MINUTE, the record is assigned to the window [2021-06-29 23:54:00, 2021-06-30 00:04:00).
If offset is 6 MINUTE, the record is assigned to the window [2021-06-29 23:56:00, 2021-06-30 00:06:00).
If the offset is 16 minutes, the record is assigned to the window [2021-06-29 23:56:00, 2021-06-30 00:06:00). We can find that some window offset parameters may have the same effect on the window allocation. In the above case, - 16 minutes, - 6 minutes and 4 minutes have the same effect on the Tumble window with a size of 10 minutes.

Note: the effect of window offset is only to update the window allocation, and it has no effect on the watermark.

We use an example to describe how to use the offset in the Tumble window in the following SQL.

-- NOTE: Currently Flink doesn't support evaluating individual window table-valued function,
--  window table-valued function should be used with aggregate operation,
--  this example is just used for explaining the syntax and the data produced by table-valued function.
Flink SQL> SELECT * FROM TABLE(
   TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES, INTERVAL '1' MINUTES));
-- or with the named params
-- note: the DATA param must be the first
Flink SQL> SELECT * FROM TABLE(
   TUMBLE(
     DATA => TABLE Bid,
     TIMECOL => DESCRIPTOR(bidtime),
     SIZE => INTERVAL '10' MINUTES,
     OFFSET => INTERVAL '1' MINUTES));
+------------------+-------+------+------------------+------------------+-------------------------+
|          bidtime | price | item |     window_start |       window_end |            window_time  |
+------------------+-------+------+------------------+------------------+-------------------------+
| 2020-04-15 08:05 |  4.00 | C    | 2020-04-15 08:01 | 2020-04-15 08:11 | 2020-04-15 08:10:59.999 |
| 2020-04-15 08:07 |  2.00 | A    | 2020-04-15 08:01 | 2020-04-15 08:11 | 2020-04-15 08:10:59.999 |
| 2020-04-15 08:09 |  5.00 | D    | 2020-04-15 08:01 | 2020-04-15 08:11 | 2020-04-15 08:10:59.999 |
| 2020-04-15 08:11 |  3.00 | B    | 2020-04-15 08:11 | 2020-04-15 08:21 | 2020-04-15 08:20:59.999 |
| 2020-04-15 08:13 |  1.00 | E    | 2020-04-15 08:11 | 2020-04-15 08:21 | 2020-04-15 08:20:59.999 |
| 2020-04-15 08:17 |  6.00 | F    | 2020-04-15 08:11 | 2020-04-15 08:21 | 2020-04-15 08:20:59.999 |
+------------------+-------+------+------------------+------------------+-------------------------+

-- apply aggregation on the tumbling windowed table
Flink SQL> SELECT window_start, window_end, SUM(price)
  FROM TABLE(
    TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES, INTERVAL '1' MINUTES))
  GROUP BY window_start, window_end;
+------------------+------------------+-------+
|     window_start |       window_end | price |
+------------------+------------------+-------+
| 2020-04-15 08:01 | 2020-04-15 08:11 | 11.00 |
| 2020-04-15 08:11 | 2020-04-15 08:21 | 10.00 |
+------------------+------------------+-------+

From Flink Website Url: Window function | Apache Flink

-------------------------------------------------------------------Reprint prohibited--------------------------------------------------

To be modified....

Keywords: flink debian linq p2p

Added by mattsutton on Wed, 23 Feb 2022 18:08:35 +0200

Programming VIP