STEP

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks > Functions > Built-in Functions >

STEP

Previous pageReturn to chapter overviewNext page

The STEP function works on datetime data types or integer types. The STEP function performs arithmetic similar to the FLOOR() function, but lets you work through repeated "floors" as data flows--in "steps." You use the STEP function in a tumbling window with a GROUP BY clause. The second parameter acts as a "flooring unit". If this parameter is a 5 minute interval literal, for example, data will be emitted every five minutes.

Syntax

STEP(<datetimeExpression> BY <intervalLiteral>)

 

OR

STEP(<IntegerExpression> BY <integerLiteral>)

 

See the topic Expressions and Literals for more information on datetimeExpressions, intervalLiterals, and IntegerExpressions.

Notes

The monotonicity of the STEP function result is same as that of the first parameter.

The result of the STEP function has the same data type as that of the first parameter (either a datetime expression or an integer expression).

STEP function for Datetime Data Types

STEP(<datetimeExpression> BY <intervalLiteral>)

 

is equivalent to

(datetimeExpression - timestamp '1970-01-01 00:00:00')  /  <intervalLiteral> )  * <intervalLiteral>

+ timestamp '1970-01-01 00:00:00'

 

STEP function for Integer Data Types

STEP(<IntegerExpression> BY <integerLiteral>)

 

is equivalent to

(<IntegerExpression> / <IntegerLiteral>) * <IntegerLiteral>

 

Using the STEP Function in a Tumbling Window

You use the STEP function in a tumbling window with a GROUP BY clause, such as the following. For more information on GROUP BY, see the topic GROUP BY clause.

SELECT STREAM partition_id, SUM(measure)

FROM s

GROUP BY STEP(s.ROWTIME BY INTERVAL '5' MINUTE), partition_id;

 

In the example above, all streaming data for stream s is grouped by partition_id every 5 minutes. In the example above, STEP function is monotonically increasing since its first parameter, s.ROWTIME, is monotonic. The aggregator correctly generates punctuations when STEP function is used in a GROUP BY clause.

For all rows with ROWTIME between '2016-01-01 07:30:00' and '2016-01-01 07:34.59.999', the aggregated result has a ROWTIME of

STEP(s.ROWTIME BY INTERVAL '5' MINUTE) + INTERVAL '5' MINUTE

=  '2016-01-01 07:35:00'

 

In this case, when aggregated results are emitted, the aggregator also emits a punctuation of 5 minutes later since the next aggregation result will have a ROWTIME of at least '2016-01-01 07:40:00'