# WINDOW Clause (Sliding Windows)
The `WINDOW` clause for a sliding windowed query specifies the rows over which analytic functions are computed across a group of rows in relation to the current row. These aggregate functions produce an output row aggregated by the keys in one or more columns for each input row. The `WINDOW` clause in a query specifies records in a stream partitioned by the time range interval or the number of rows, and an additional optional set of columns specified by the `PARTITION BY` clause. You can define named or inline window specifications that can be used in analytic functions and streaming `JOIN` clauses. For more information about analytic functions, see [Analytic Functions](sql-reference-analytic-functions.md).
Aggregate functions in a sliding window query are performed over each column specified in the `OVER` clause. The `OVER` clause can reference a named window specification or can be inline as part of the `SELECT` statement for a pump. The following examples show how to use the `OVER` clause to reference a named window specification and inline in the `SELECT` statement.
## Syntax
```
[WINDOW window_name AS
(
{PARTITION BY partition_name
RANGE INTERVAL 'interval' {SECOND | MINUTE | HOUR} PRECEDING |
ROWS number PRECEDING
, …}
)
```
## OVER Clause
The examples following show you how to use the `OVER` clause to reference a named window specification.
**Example 1: OVER Referencing a Named Window Specification**
The following example shows an aggregate function that references the window specification with the name W1. In this example, the average price is calculated over the set of records specified by the `W1` window specification. To learn more about how to use the OVER clause with a window specification, see [Examples](#sql-reference-window-clause-examples), following.
```
AVG(price) OVER W1 AS avg_price
```
**Example 2: OVER Referencing an Inline Window Specification**
The following example shows an aggregate function that references an inline window specification. In this example, the average price is calculated over each input row with an inline window specification. To learn more about how to use the OVER clause with a window specification, see [Examples](#sql-reference-window-clause-examples), following.
```
AVG(price) OVER (
PARTITION BY ticker_symbol
RANGE INTERVAL '1' HOUR PRECEDING) AS avg_price
```
For more information about aggregate functions and the OVER clause, see [Aggregate Functions](sql-reference-aggregate-functions.md).
## Parameters
*window-name*
Specifies a unique name that can be referenced from OVER clauses or subsequent window definitions. The name is used in analytic functions and streaming `JOIN` clauses. For more information about analytic functions, see [Analytic Functions](sql-reference-analytic-functions.md).
AS
Defines the named window specification for the `WINDOW` clause.
PARTITION BY *partition-name*
Divides rows into groups that share the same values. After rows are partitioned, the window function computes all rows that fall into the same partition as the current row.
RANGE INTERVAL *'interval'* \$1SECOND \$1 MINUTE \$1 HOUR\$1 PRECEDING
Specifies the window boundaries from the time range interval. The window function computes all rows that fall into the same time interval as the current row.
ROWS *number* PRECEDING
Specifies the window boundaries from the number of rows. The window function computes all rows that fall into the same number of rows.
## Examples
### Example Dataset
The examples following are based on the sample stock dataset that is part of [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. To run each example, you need an Amazon Kinesis Analytics application that has the sample stock ticker input stream. To learn how to create an Analytics application and configure the sample stock ticker input stream, see [Getting Started](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/get-started-exercise.html) in the *Amazon Kinesis Analytics Developer Guide*. For additional samples, see [Sliding Windows](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/sliding-window-concepts.html).
The sample stock dataset has the schema following.
```
(ticker_symbol VARCHAR(4),
sector VARCHAR(16),
change REAL,
price REAL)
```
### Example 1: Time-Based Sliding Window That References a Named Window Specification
This example defines a named window specification with a partition boundary of one minute preceding the current row. The `OVER` clause of the `SELECT` statement for the pump references the named window specification.
```
WINDOW W1 AS (
PARTITION BY ticker_symbol
RANGE INTERVAL '1' MINUTE PRECEDING);
```
To run this example, create the stock sample application and run and save the SQL code following.
```
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" (
ticker_symbol VARCHAR(4),
min_price DOUBLE,
max_price DOUBLE,
avg_price DOUBLE);
CREATE OR REPLACE PUMP "STREAM_PUMP" AS
INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol,
MIN(price) OVER W1 AS min_price,
MAX(price) OVER W1 AS max_price,
AVG(price) OVER W1 AS avg_price
FROM "SOURCE_SQL_STREAM_001"
WINDOW W1 AS (
PARTITION BY ticker_symbol
RANGE INTERVAL '1' MINUTE PRECEDING);
```
The preceding example outputs a stream similar to the following.
![\[Table showing stock data with columns for rowtime, ticker symbol, and price information.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-analytic-functions-example-1.png)
### Example 2: Row-Based Sliding Window That References a Named Window Specification
This example defines a named window specification with a partition boundary of two rows preceding the current row and ten rows preceding the current row. The `OVER` clause of the `SELECT` statement for the pump references the named window specification.
```
WINDOW
last2rows AS (PARTITION BY ticker_symbol ROWS 2 PRECEDING),
last10rows AS (PARTITION BY ticker_symbol ROWS 10 PRECEDING);
```
To run this example, create the stock sample application and run and save the SQL code following.
```
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" (
ticker_symbol VARCHAR(4),
price DOUBLE,
avg_last2rows DOUBLE,
avg_Last10rows DOUBLE);
CREATE OR REPLACE PUMP "myPump" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol,
price,
AVG(price) OVER last2rows,
AVG(price) OVER last10rows
FROM SOURCE_SQL_STREAM_001
WINDOW
last2rows AS (PARTITION BY ticker_symbol ROWS 2 PRECEDING),
last10rows AS (PARTITION BY ticker_symbol ROWS 10 PRECEDING);
```
The preceding example outputs a stream similar to the following.
![\[Table showing stock ticker symbols, prices, and average values for multiple rows.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-analytic-functions-example-2.png)
### Example 3: Time-Based Sliding Window with Inline Window Specification
This example defines an inline window specification with a partition boundary of one minute preceding the current row. The `OVER` clause of the `SELECT` statement for the pump uses the inline window specification.
To run this example, create the stock sample application and run and save the SQL code following.
```
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" (
ticker_symbol VARCHAR(4),
price DOUBLE,
avg_price DOUBLE);
CREATE OR REPLACE PUMP "STREAM_PUMP" AS
INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol, price,
AVG(Price) OVER (
PARTITION BY ticker_symbol
RANGE INTERVAL '1' HOUR PRECEDING) AS avg_price
FROM "SOURCE_SQL_STREAM_001"
```
The preceding example outputs a stream similar to the following.
![\[Table showing stock data with columns for timestamp, ticker symbol, price, and average price.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/sql-reference-analytic-functions-example-3.png)
## Usage Notes
###
For the WINDOW clause and endpoints, Amazon Kinesis Analytics SQL follows SQL-2008 standards for windows over a range.
To include the endpoints of an hour, you can use the window syntax following.
```
WINDOW HOUR AS (RANGE INTERVAL '1' HOUR PRECEDING)
```
To not include the endpoints of the previous hour, you can use the window syntax following.
```
WINDOW HOUR AS (RANGE INTERVAL '59:59.999' MINUTE TO SECOND(3) PRECEDING);
```
For more information, see [Allowed and Disallowed Window Specifications](sql-reference-allowed-disallowed-window.md).
## Related Topics
+ [Sliding Windows](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/sliding-window-concepts.html) in the Kinesis Developer Guide
+ [Aggregate Functions](sql-reference-aggregate-functions.md)
+ [SELECT statement](sql-reference-select.md)
+ [CREATE STREAM](sql-reference-create-stream.md) statement
+ [CREATE PUMP](sql-reference-create-pump.md) statement
# Allowed and Disallowed Window Specifications
Amazon Kinesis Data Analytics supports nearly all windows that end with the current row.
You cannot define an infinite window, a negative-sized window, or use negative integers in the window specification. Offset windows are currently unsupported.
+ Infinite windows are windows with no bounds. Typically these point into the future, which for streams is infinite. For example "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" is not supported, because in a streaming context such a query would not produce a result, since streams are continually expanding as new data arrives. All uses of UNBOUNDED FOLLOWING are unsupported.
+ Negative windows . For example, "ROWS BETWEEN 0 PRECEDING AND 4 PRECEDING" is a window of negative size and is therefore illegal. Instead, you would use: "ROWS BETWEEN 4 PRECEDING AND 0 PRECEDING" in this case.
+ Offset windows are windows that do not end with CURRENT ROW. These are not supported in the current release. For example, "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING" is not supported. (Window spans CURRENT ROW rather than starting or ending there.)
+ Windows defined with negative integers. For example, "ROWS BETWEEN -4 PRECEDING AND CURRENT ROW" is invalid because negative integers are disallowed.
Also, the special case of ... 0 PRECEDING (and ... 0 FOLLOWING) cannot be used for windowed aggregation; instead, the synonym CURRENT ROW can be used.
For windowed aggregation, partitioned windows are allowed, but ORDER BY must not be present.
For windowed join, partitioned windows are NOT allowed, but ORDER BY can be present if it sorts by the ROWTIME column of one of the inputs.
# Window examples
The following examples show a sample input data set, the definitions for several windows, and the contents of those windows at various times after 10:00, the time data starts to arrive for this example.
The windows are defined as follows:
```
SELECT STREAM
ticker,
sum(amount) OVER lastHour,
count(*) OVER lastHour
sum(amount) OVER lastThree
FROM Trades
WINDOW
lastHour AS (RANGE INTERVAL '1' HOUR PRECEDING),
lastThree AS (ROWS 3 PRECEDING),
lastZeroRows AS (ROWS CURRENT ROW),
lastZeroSeconds AS (RANGE CURRENT ROW),
lastTwoSameTicker AS (PARTITION BY ticker ROWS 2 PRECEDING),
lastHourSameTicker AS (PARTITION BY ticker RANGE INTERVAL '1' HOUR PRECEDING)
```
## First Example: time-based windows versus row-based windows
As shown on the right side of the figure below, the time-based lastHour window contains varying numbers of rows, because window membership is defined by time range.
![\[Time-based window examples showing ROWTIME, ticker, and amount columns with varying row counts.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/window-examples.png)
## Examples of windows containing rows
The row-based lastThree window generally contains four rows: the three preceding and the current row. However for the row 10:10 IBM, it only contains two rows, because there is no data before 10:00.
A row-based window can contain several rows whose ROWTIME value is the same, though they arrive at different times (wall-clock times). The order of such a row in the row-based window depends on its arrival time; indeed, the row's arrival time can determine which window includes it.
For example, the middle lastThree window in Figure 1 shows the arrival of a YHOO trade with ROWTIME 11:15 (and the last three trades before it). However, this window excludes the next trade, for IBM, whose ROWTIME is also 11:15 but which must have arrived later than the YHOO trade. This 11:15 IBM trade is included in the 'next' window, as is the 11:15 YHOO trade, its immediate predecessor.
Second Example: zero width windows, row-based and time-based
Figure 2: Examples of zero-width windows shows row-based and time-based windows of zero width. The row-based window lastZeroRows includes just the current row, and therefore always contains precisely one row. Note that ROWS CURRENT ROW is equivalent to ROWS 0 PRECEDING.
The time-based window lastZeroSeconds contains all rows with the same timestamp, of which there may be several. Note that RANGE CURRENT ROW is equivalent to RANGE INTERVAL '0' SECOND PRECEDING.
![\[Examples of zero-width windows showing ROWTIME, ticker, and amount columns with time-based data.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/window-examples2.png)
## Third Example: Partitioning applied to row-based and time-based windows
Figure 3 shows windows that are similar to those in Figure 1 but with a PARTITION BY clause. For time-based window lastTwoSameTicker and the row-based window lastHourSameTicker, the window contains rows that meet the window criteria and have the same value of the ticker column. Note: Partitions are evaluated before windows.
![\[Table showing partitioned windows with ROWTIME, ticker, and amount columns for stock data.\]](http://docs.aws.amazon.com/kinesisanalytics/latest/sqlref/images/window-examples3.png)