WINDOW clause

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > SELECT statement  >

WINDOW clause

Previous pageReturn to chapter overviewNext page

The WINDOW clause allows you to define named window specifications that can be used in analytic function calls and streaming JOIN clauses elsewhere in the query. The windows so defined are inherited by sub-queries of the current query.

<window-clause> :=

   WINDOW <window-definition> { , <window-definition> }...

<window-definition> :=

   <window-name> AS <window-specification>

<window-specification> :=

   <window-name>

 | <query_partition_clause>

 | ORDER BY <order_by_clause>

 | <windowing_clause>

<query_partition_clause> :=

  PARTITION BY <expression> { , <expression> }...

 |  ( <expression> { , <expression> }... )

 

Window clause and endpoints

Streaming SQL follows the SQL Standards for windows over a range. This means, for example that the syntax

WINDOW HOUR AS (RANGE INTERVAL '1' HOUR PRECEDING)

 

will include the end points of the hour

To ensure that the the endpoint of the previous hour is not included, you need to use the following syntax for the window:

WINDOW HOUR AS (RANGE INTERVAL '59:59.999' MINUTE TO SECOND(3) PRECEDING);

 

See Allowed and Disallowed Window Specifications for more details.

Syntax Charts for Window Statement and Window Specification

The chart for Window Statement includes the window-specification, query-partition, and windowing-clause charts that directly follow below it.

Window Statement

window_stmt

Window Specification

The "window-specification" is also referenced in the stream-or-table-reference chart that appears within the select-clause chart.

windowSpecnReally

The order_by_clause chart appears after the window examples .

Syntax Chart for the query-partition clause

query_partition

Syntax Chart for the windowing-clause

In addition to being part of the window-specification, the windowing-clause appears in the FROM clause and the analytic clause of an analytic function.

windowSpecn

Note: Partitions are evaluated before windows.

<windowing-clause> :=

   { ROWS | RANGE }

   { BETWEEN

     { UNBOUNDED PRECEDING

     | CURRENT ROW

     | <value-expression> { PRECEDING | FOLLOWING }

     }

     AND

     { UNBOUNDED FOLLOWING

     | CURRENT ROW

     | <value-expression> { PRECEDING | FOLLOWING }

     }

   | { UNBOUNDED { PRECEDING | FOLLOWING }

     | CURRENT ROW

     | <value-expression> { PRECEDING | FOLLOWING }

     }

   }