T-sorting Stream Input

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > SELECT statement  > ORDER BY clause >

T-sorting Stream Input

Previous pageReturn to chapter overviewNext page

SQLstream s-Server's real-time analytics use the fact that arriving data is ordered by ROWTIME. However, sometimes data arriving from multiple sources may not be time-synchronized.

While SQLstream s-Server can sort data from individual data sources that have been independently inserted into s-Server's native stream, in some cases such data may have already combined from multiple sources (such as for efficient consumption at an earlier stage in processing). At other times, high volume data sources could make direct insertion impossible.

In addition, an unreliable data source could block progress by forcing s-Server to wait indefinitely, unable to proceed until all connected data sources deliver. In this case, data from this source could be unsynchronized.

SQLstream s-Server resolves these issues using a new execution object (XO), the t-Sort XO. It uses a sliding time-based window of incoming rows to reorder those rows by ROWTIME.

Syntax

You specify the time-based parameter for sorting and the time-based window in which the streaming rows are to be time-sorted, using the following syntax:

 ORDER BY <timestamp_expr> WITHIN <interval_literal>

Examples

The SQL statements below pre-sort rows from different streams using windows of two minutes and two seconds.

CREATE OR REPLACE STREAM "vehicle_positions" (

"PositionTime" TIMESTAMP,

"VID" INTEGER,

"vSpeed" SMALLINT,

"vBearing" REAL)

DESCRIPTION 'TSort Example';

 

SELECT STREAM "PositionTime" AS ROWTIME, VID, "vSpeed", "vBearing"

FROM "vehicle_positions"

ORDER BY "PositionTime" WITHIN INTERVAL '2' MINUTE, VID;

SET SCHEMA '"SALES"';

 

CREATE OR REPLACE STREAM "ASKS" (

"TransactionTime" TIMESTAMP,

"time" BIGINT,

"ticker" VARCHAR(5),

"shares" INTEGER,

"price" REAL,

"expiryMs" BIGINT,

"comment" VARCHAR(1024))

DESCRIPTION 'updating ASKS for this example';

 

SELECT STREAM "TransactionTime" AS ROWTIME, "ticker", "price"

FROM ASKS

ORDER BY "TransactionTime" WITHIN INTERVAL '2' SECOND;

 

Restrictions

The T-sort XO has the following restrictions:

The datatype of the ORDER BY expression must be timestamp.
The partially-ordered expression <timestamp_expr> must be present in the select list of the query with the alias ROWTIME.
The leading expression of the ORDER BY clause must not contain the ROWTIME function and must not use the DESC keyword.
The ROWTIME column needs to be fully qualified. For example:
oORDER BY FLOOR(ROWTIME TO MINUTE), ... fails.
oORDER BY FLOOR(s.ROWTIME TO MINUTE), ... works.

If any of these requirements are not met, the statement will fail with errors.

Additional notes:

You cannot use incoming rowtimebounds. These are ignored by the system.
If <timestamp_expr> evaluates to NULL, the corresponding row is discarded.