Null treatment option Effect
FIRST_VALUE(x) IGNORE NULLS OVER
<window-specification> Returns first non null value of x in <window-specification>
FIRST_VALUE(x) RESPECT NULLS OVER
<window-specification> Returns first value, including null of x in <window-specification>
FIRST_VALUE(x) OVER <window-specification> Returns first value, including null of x in <window-specification>
LAST_VALUE
LAST_VALUE ( <value-expression> ) OVER <window-specification>
LAST_VALUE returns the evaluation of the <value expression> from the last row that qualifies for the aggregate.
Null Treatment Option Effect
LAST_VALUE(x) IGNORE NULLS OVER
<window-specification> Returns last non null value of x in <window-specification>
LAST_VALUE(x) RESPECT NULLS OVER
<window-specification> Returns last value, including null of x in <window-specification>
LAST_VALUE(x) OVER <window-specification> Returns last value, including null of x in <window-specification>
MAX
Returns the maximum value of a group of values from a windowed query. A windowed query is defined in terms of time or rows. For information about window queries, see Windowed Queries.
When you use MAX, be aware of the following:
• If you don't use the OVER clause, MAX is calculated as an aggregate function. In this case, the aggregate query must contain a GROUP BY clause (p. 62) on a monotonic expression based on ROWTIME that groups the stream into finite rows. Otherwise, the group is the infinite stream, and the query will never complete and no rows will be emitted. For more information, see Aggregate Functions (p. 78).
• A windowed query that uses a GROUP BY clause processes rows in a tumbling window. For more information, see Tumbling Windows (Aggregations Using GROUP BY).
• If you use the OVER clause, MAX is calculated as an analytic function. For more information, see Analytic Functions (p. 110).
• A windowed query that uses an OVER clause processes rows in a sliding window. For more information, see Sliding Windows
Syntax
Tumbling Windowed Query
MAX
MAX(number-expression) ... GROUP BY monotonic-expression | time-based-expression
Sliding Windowed Query
MAX(number-expression) OVER window-specification
Parameters
number-expression
Specifies the value expressions evaluated for each row in the aggregation.
OVER window-specification
Divides records in a stream partitioned by the time range interval or the number of rows. A window specification defines how records in the stream are partitioned by the time range interval or the number of rows.
GROUP BY monotonic-expression | time-based-expression
Groups records based on the value of the grouping expression returning a single summary row for each group of rows that has identical values in all columns.
Examples
Example Dataset
The examples following are based on the sample stock dataset that is part of Getting Started 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 in the Amazon Kinesis Analytics Developer Guide.
The sample stock dataset has the schema following.
(ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL)
Example 1: Return the Maximum Value Using the GROUP BY Clause
In this example, the aggregate query has a GROUP BY clause on ROWTIME that groups the stream into finite rows. The MAX function is then calculated from the rows returned by the GROUP BY clause.
Using STEP (recommended)
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4),
max_price DOUBLE);
MAX
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol,
MAX(Price) AS max_price FROM "SOURCE_SQL_STREAM_001"
GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND);
Using FLOOR
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4),
max_price DOUBLE);
-- CREATE OR REPLACE PUMP to insert into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol,
MAX(Price) AS max_price FROM "SOURCE_SQL_STREAM_001"
GROUP BY ticker_symbol, FLOOR("SOURCE_SQL_STREAM_001".ROWTIME TO MINUTE);
Results
The preceding examples output a stream similar to the following.
Example 2: Return the Maximum Value Using the OVER Clause
In this example, the OVER clause divides records in a stream partitioned by the time range interval of '1' hour preceding. The MAX function is then calculated from the rows returned by the OVER clause.
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4),
max_price DOUBLE);
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol, MAX(price) OVER (
PARTITION BY ticker_symbol
RANGE INTERVAL '1' HOUR PRECEDING) AS max_price FROM "SOURCE_SQL_STREAM_001"
The preceding example outputs a stream similar to the following.
MIN
Usage Notes
For string values, MAX is determined by which string is last in the collating sequence.
If MAX is used as an analytic function and the window being evaluated contains no rows, MAX returns null. For more information, see Analytic Functions (p. 110).
Related Topics
• Windowed Queries
• Aggregate Functions (p. 78)
• GROUP BY clause (p. 62)
• Analytic Functions (p. 110)
• Getting Started Exercise
• WINDOW Clause (Sliding Windows) (p. 66)
MIN
Returns the minimum value of a group of values from a windowed query. A windowed query is defined in terms of time or rows. For information about windowed queries, see Windowed Queries.
When you use MIN, be aware of the following:
• If you don't use the OVER clause, MIN is calculated as an aggregate function. In this case, the aggregate query must contain a GROUP BY clause (p. 62) on a monotonic expression based on ROWTIME that groups the stream into finite rows. Otherwise, the group is the infinite stream, and the query will never complete and no rows will be emitted. For more information, see Aggregate Functions (p. 78).
• A windowed query that uses a GROUP BY clause processes rows in a tumbling window. For more information, see Tumbling Windows (Aggregations Using GROUP BY).
• If you use the OVER clause, MIN is calculated as an analytic function. For more information, see Analytic Functions (p. 110).
• A windowed query that uses an OVER clause processes rows in a sliding window. For more information, see Sliding Windows
Syntax
Tumbling Windowed Query
MIN(number-expression) ... GROUP BY monotonic-expression | time-based-expression
MIN
Sliding Windowed Query
MIN(number-expression) OVER window-specification
Parameters
number-expression
Specifies the value expressions evaluated for each row in the aggregation.
OVER window-specification
Divides records in a stream partitioned by the time range interval or the number of rows. A window specification defines how records in the stream are partitioned by the time range interval or the number of rows.
GROUP BY monotonic-expression | time-based-expression
Groups records based on the value of the grouping expression returning a single summary row for each group of rows that has identical values in all columns.
Examples
Example Dataset
The examples following are based on the sample stock dataset that is part of the Getting Started Exercise 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 in the Amazon Kinesis Analytics Developer Guide.
The sample stock dataset has the schema following.
(ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL)
Example 1: Return the Minimum Value Using the GROUP BY Clause
In this example, the aggregate query has a GROUP BY clause on ROWTIME that groups the stream into finite rows. The MIN function is then calculated from the rows returned by the GROUP BY clause.
Using STEP (Recommended)
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4),
min_price DOUBLE);
MIN
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol,
MIN(Price) AS min_price FROM "SOURCE_SQL_STREAM_001"
GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND);
Using FLOOR
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4),
min_price DOUBLE);
-- CREATE OR REPLACE PUMP to insert into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol,
MIN(Price) AS min_price FROM "SOURCE_SQL_STREAM_001"
GROUP BY ticker_symbol, FLOOR("SOURCE_SQL_STREAM_001".ROWTIME TO MINUTE);
Results
The preceding examples output a stream similar to the following.
Example 2: Return the Minimum Value Using the OVER Clause
In this example, the OVER clause divides records in a stream partitioned by the time range interval of '1' hour preceding. The MIN function is then calculated from the rows returned by the OVER clause.
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4),
min_price DOUBLE);
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol, MIN(price) OVER (
PARTITION BY ticker_symbol
RANGE INTERVAL '1' HOUR PRECEDING) AS min_price FROM "SOURCE_SQL_STREAM_001"
The preceding example outputs a stream similar to the following.
SUM
Usage Notes
For string values, MIN is determined by which string is last in the collating sequence.
If MIN is used as an analytic function and the window being evaluated contains no rows, MIN returns null.
For more information, see Analytic Functions (p. 110).
Related Topics
• Windowed Queries
• Aggregate Functions (p. 78)
• GROUP BY clause (p. 62)
• Analytic Functions (p. 110)
• Getting Started Exercise
• WINDOW Clause (Sliding Windows) (p. 66)
SUM
Returns the sum of a group of values from a windowed query. A windowed query is defined in terms of time or rows. For information about windowed queries, see Windowed Queries.
When you use SUM, be aware of the following:
• If you don't use the OVER clause, SUM is calculated as an aggregate function. In this case, the aggregate query must contain a GROUP BY clause (p. 62) on a monotonic expression based on ROWTIME that groups the stream into finite rows. Otherwise, the group is the infinite stream, and the query will never complete and no rows will be emitted. For more information, see Aggregate Functions (p. 78).
• A windowed query that uses a GROUP BY clause processes rows in a tumbling window. For more information, see Tumbling Windows (Aggregations Using GROUP BY).
• If you use the OVER clause, SUM is calculated as an analytic function. For more information, see Analytic Functions (p. 110).
• A windowed query that uses an OVER clause processes rows in a sliding window. For more information, see Sliding Windows
Syntax
Tumbling Windowed Query
SUM(number-expression) ... GROUP BY monotonic-expression | time-based-expression
SUM
Sliding Windowed Query
SUM([DISTINCT | ALL] number-expression) OVER window-specification
Parameters
DISTINCT
Counts only distinct values.
ALL
Counts all rows. ALL is the default.
number-expression
Specifies the value expressions evaluated for each row in the aggregation.
OVER window-specification
Divides records in a stream partitioned by the time range interval or the number of rows. A window specification defines how records in the stream are partitioned by the time range interval or the number of rows.
GROUP BY monotonic-expression | time-based-expression
Groups records based on the value of the grouping expression returning a single summary row for each group of rows that has identical values in all columns.
Examples
Example Dataset
The examples following are based on the sample stock dataset that is part of the Getting Started Exercise 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 in the Amazon Kinesis Analytics Developer Guide.
The sample stock dataset has the schema following.
(ticker_symbol VARCHAR(4), sector VARCHAR(16), change REAL, price REAL)
Example 1: Return the Sum of Values Using the GROUP BY Clause
In this example, the aggregate query has a GROUP BY clause on ROWTIME that groups the stream into finite rows. The SUM function is then calculated from the rows returned by the GROUP BY clause.
Using STEP (Recommended)
SUM
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4),
sum_price DOUBLE);
CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol,
SUM(price) AS sum_price FROM "SOURCE_SQL_STREAM_001"
GROUP BY ticker_symbol, STEP("SOURCE_SQL_STREAM_001".ROWTIME BY INTERVAL '60' SECOND);
Using FLOOR
CREATE OR REPLACE STREAM "DESTINATION_SQL_STREAM" ( ticker_symbol VARCHAR(4),
sum_price DOUBLE);
-- CREATE OR REPLACE PUMP to insert into output CREATE OR REPLACE PUMP "STREAM_PUMP" AS INSERT INTO "DESTINATION_SQL_STREAM"
SELECT STREAM ticker_symbol,
SUM(price) AS sum_price FROM "SOURCE_SQL_STREAM_001"
GROUP BY ticker_symbol, FLOOR("SOURCE_SQL_STREAM_001".ROWTIME TO MINUTE);
Results
The preceding examples output a stream similar to the following.
Usage Notes
Amazon Kinesis Analytics doesn't support SUM applied to interval types. This functionality is a departure from the SQL:2008 standard.
SUM ignores null values from the set of values or a numeric expression. For example, each of the following return the value of 6:
• SUM(1, 2, 3) = 6
• SUM(1,null, 2, null, 3, null) = 6
Related Topics
• Windowed Queries