Simple SQL Examples

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Simple SQL Examples

Return to chapter overview

This example filters a stream by applying predicates to the rows as they flow by:

SELECT STREAM *

 FROM SALES.BIDS

 WHERE "ticker" = 'SQLS';

SELECT STREAM *

 FROM SALES.BIDS

 WHERE "shares" > 500 AND "price" > 150.00;

Unions

This example forms the union of two streams to create a new composite stream:

CREATE OR REPLACE VIEW "BidsAndAsks"

DESCRIPTION 'Unioned view of BIDS and ASKS streams' AS

SELECT STREAM

 'BID' AS "type", "time", "ticker", "shares", "price"

FROM SALES.BIDS

UNION ALL

SELECT STREAM

 'ASK' AS "type", "time", "ticker", "shares", "price"

FROM SALES.ASKS;

 

This new stream can now be filtered for the high-value bids and asks:

CREATE OR REPLACE VIEW "HighBidsAndAsks"

DESCRIPTION 'BIDS and ASKS filtered for high price and share count' AS

SELECT STREAM *

FROM "BidsAndAsks"

WHERE "price" > 150.00 AND "shares" > 500;

 

In $SQLSTREAM_HOME/s-Server/examples/querypatterns/union you can find a catalog.sql script with the code for the BidsAndAsks and HighBidsAndAsks views, plus DataGen configuration files to generate streams of SALES.BIDS and SALES.ASKS sample data. (For more information on DataGen, see the Appendix - DataGen: a tool for generating test data in the SQLstream Developer Guide.)

Note: $SQLSTREAM_HOME refers to the installation directory, such as /opt/sqlstream/4.0.XXX.

Windows

WINDOW is a SQL:2008 standard for identifying a subset of rows in a table. SQLstream extends the use of WINDOW to identify a finite set of rows (fixed or varying in number) from an otherwise infinite stream. The use of windows allows the application to perform aggregations and joins over these finite subsets of rows.

Time-based Windows

Time-based windows include a varying number of rows. For example, RANGE INTERVAL '1' HOUR PRECEDING specifies all rows with ROWTIME values within the last hour. The actual rows included by this window definition change from moment to moment. The number of rows within the window can also vary from moment to moment.

The following statements are examples of time-based window specifications:

RANGE INTERVAL '1' HOUR PRECEDING

RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND INTERVAL '14' DAY PRECEDING

RANGE BETWEEN INTERVAL '2' MINUTE PRECEDING AND UNBOUNDED FOLLOWING

RANGE BETWEEN INTERVAL '90' SECOND PRECEDING AND CURRENT ROW

Row-based Windows

Row-based windows include a fixed number of rows. For example, ROWS 10 PRECEDING specifies the last 10 rows. The actual rows included by this window definition changes each time a new row arrives, but there will never be more than 10. When the window is first being populated, each arriving row is added to the window. Once the number of rows in the window reaches 10, each arriving row will also cause the window's oldest (the 10th) row to be removed from the window.

Some examples of row-based window specifications:

ROWS 10 PRECEDING

RANGE BETWEEN 10 PRECEDING AND CURRENT ROW

RANGE CURRENT ROW

Aggregations

In SQLstream, windowed aggregation is an aggregation function applied to all the rows currently within a window. The window may be time-based or row-based (set by a fixed number of rows).

For example, a moving average takes all event values within a certain time period (or number of rows) and calculates the result. If the period is "the last hour," then each time the average is calculated, the set of values in "the last hour" has changed. Older events will have dropped out of the time period, and any qualifying events that occurred in the last hour will be included. If the moving average is specified to be calculated on a fixed or calculated number of rows, then only that number of rows is used. Including new qualifying rows beyond that number causes older rows to be discarded.

The following illustrates several windowed aggregations over a 1-minute rolling window:

CREATE OR REPLACE VIEW "Trend"

DESCRIPTION 'rolling-period price trend for SALES.BIDS' AS

SELECT STREAM

  B.ROWTIME AS "bidTime",

  "ticker", "shares", "price",

  SUM("shares") OVER "lastMinute" AS "sharesPerPeriod",

  SUM("price" * "shares") OVER "lastMinute" AS "amtPerPeriod",

  MIN("price") OVER "lastMinute" AS "minPrice",

  AVG("price") OVER "lastMinute" AS "avgPrice",

  MAX("price") OVER "lastMinute" AS "maxPrice"

FROM SALES.BIDS AS B

WINDOW "lastMinute" AS (RANGE INTERVAL '1' MINUTE PRECEDING);

 

Each row arriving on SALES.BIDS changes the set of rows defined within the window and produces an output row with the new aggregate values. Windowed aggregation has "one-row-in, one-row-out" behavior within the defined window.

In contrast, GROUP BY uses streaming aggregation to perform periodic reporting. In this type of aggregation, only incoming rows that belong to the group are used in the aggregations for periodic reporting. An incoming row that belongs to the next group ends the current group, causes that current group's aggregations to be emitted as a single output row, and begins the next group. Streaming aggregation has "many-rows-in, one-row-out" behavior within the defined window.

The following illustrates streaming aggregations over a 1-minute window:

CREATE OR REPLACE VIEW "Summary"

DESCRIPTION 'periodic price summary for SALES.BIDS' AS

SELECT STREAM

  FLOOR(B.ROWTIME TO MINUTE) AS "minute",

  "ticker",

  SUM("shares") AS "sharesThisPeriod",

  SUM("price" * "shares") AS "amtThisPeriod",

  SUM("price")/COUNT("price") AS "avgPrice"

FROM SALES.BIDS AS B

GROUP BY FLOOR(B.ROWTIME TO MINUTE), "ticker";

 

A periodic reporting interval is more likely to be hourly or daily rather than minute-by-minute. These short intervals were chosen for ease of visualizing actual data flow using sample data. In $SQLSTREAM_HOME/s-Server/examples/querypatterns/agg you can find a catalog.sql script with the code for the Trend and Summary views, plus DataGen configuration files to generate a stream of SALES.BIDS sample data.

Note that the Summary view groups by both minute and ticker symbol. The sales.bids.SQLS.random.set.xml configuration generates data for just the 'SQLS' ticker, so there will be just one Summary row per minute. By comparison, the sales.bids.random.set.xml configuration generates data for four different tickers, so there will be four Summary rows per minute.

Additional examples of aggregation appear in the topic Application Design in this guide.

Joins

Stream-Window Joins

Streaming joins take place over a time-based subset of records. By default, these subsets are ordered by row timestamps: rows from each stream whose ROWTIME values match are joined according to the JOIN condition.

A join with an explicit window is called windowed join. This example matches bids and asks by ticker, shares, and price within a ten-second window. It is written with an inline window specification.

CREATE OR REPLACE VIEW "MatchBidsAndAsks"

DESCRIPTION 'match bids and asks for each ticker over a 10-second time window' AS

SELECT STREAM

 B.ROWTIME AS "bidTime",

 A.ROWTIME AS "askTime",

 B."ticker",

 B."shares" AS "bidShares",

 B."price" AS "bidPrice",

 A."shares" AS "askShares",

 A."price" AS "askPrice"

FROM SALES.BIDS OVER (RANGE INTERVAL '10' SECOND PRECEDING) AS B

JOIN SALES.ASKS AS A ON A."ticker" = B."ticker"

 AND A."shares" = B."shares"

 AND A."price" = B."price";

 

For readability, or for reuse of a window specification within a query, a WINDOW alias can define a named window. Here is the above example rewritten to use a WINDOW alias:

CREATE OR REPLACE VIEW "MatchBidsAndAsks"

DESCRIPTION 'match bids and asks for each ticker over a 10-second time window' AS

SELECT STREAM

 B.ROWTIME AS "bidTime",

 A.ROWTIME AS "askTime",

 B."ticker",

 B."shares" AS "bidShares",

 B."price" AS "bidPrice",

 A."shares" AS "askShares",

 A."price" AS "askPrice"

FROM SALES.BIDS OVER "lastTenSeconds" AS B

JOIN SALES.ASKS AS A ON A."ticker" = B."ticker"

 AND A."shares" = B."shares"

 AND A."price" = B."price"

WINDOW "lastTenSeconds" AS (RANGE INTERVAL '10' SECOND PRECEDING);

 

The following example also matches bids and asks by ticker, shares, and price, but uses both a ten-second window and a five-second window, defined using an inline window specification.

CREATE OR REPLACE VIEW "MatchBidsAndAsksTwoWindows"

DESCRIPTION 'match bids and asks for each ticker over 5- and 10-second time windows' AS

SELECT STREAM

 B.ROWTIME AS "bidTime",

 A.ROWTIME AS "askTime",

 B."ticker",

 B."shares" AS "bidShares",

 B."price" AS "bidPrice",

 A."shares" AS "askShares",

 A."price" AS "askPrice"

FROM SALES.BIDS OVER "lastTenSeconds" AS B

JOIN SALES.ASKS OVER "lastFiveSeconds" AS A

 ON A."ticker" = B."ticker"

 AND A."shares" = B."shares"

 AND A."price" = B."price"

WINDOW "lastTenSeconds" AS (RANGE INTERVAL '10' SECOND PRECEDING),

      "lastFiveSeconds" AS (RANGE INTERVAL '5' SECOND PRECEDING);

 

In $SQLSTREAM_HOME/s-Server/examples/querypatterns/join you can find a catalog.sql script with the code for the MatchBidsAndAsks and MatchBidsAndAsksTwoWindows views, plus DataGen configuration files to generate streams of SALES.BIDS and SALES.ASKS sample data. When you run data through these two views notice that the single-window results differ from the two-window results.