More Complex Streaming SQL Examples

<< Click to Display Table of Contents >>

Navigation:  Analyzing Data in s-Server >

More Complex Streaming SQL Examples

Previous pageReturn to chapter overviewNext page

Schema for these examples

CREATE SCHEMA "Trading";

SET SCHEMA '"Trading"';

CREATE STREAM "Orders"

(

   orderTime TIMESTAMP,

   orderId INTEGER,

   productId INTEGER,

   quantity INTEGER,

   unitPrice DECIMAL(11,2),

   shippingState CHAR(2)

);

CREATE STREAM Shipments

(

   shipTime TIMESTAMP,

   orderId INTEGER,

   warehouseState CHAR(2)

);

 

The following sample data set will be used in the examples and explanations presented in the following sections.

Order Stream

02/02/2008 10:00:00 1000 510 1 10.50 CA

02/02/2008 10:04:40 1001 21 3 6.25 TX

02/02/2008 10:30:09 1002 601 5 1.25 CA

Shipping Stream

02/02/2008 10:25:00 1001 TX

02/02/2008 10:38:40 1000 TX

02/02/2008 11:46:09 1002 CA

Query that filters a stream

SELECT STREAM orderTime, orderId, shippingState

FROM Orders

WHERE shippingState = 'CA'
 

orderTime

orderId

shippingState

02/02/2008 10:00:00

1000

CA

02/02/2008 10:30:09

1002

CA

Query that merges two streams

SELECT STREAM *

FROM (

   SELECT STREAM '"Orders"' as type, orderId FROM Orders

   UNION ALL

   SELECT STREAM 'SHIPMENT' as type, orderId FROM Shipments

)
 

type

orderId

ORDER

1000

ORDER

1001

SHIPMENT

1001

ORDER

1002

SHIPMENT

1000

SHIPMENT

1002

Query that aggregates a stream

Report each hour the order count for each state.

SELECT state, count(*)

FROM   Orders

GROUP BY FLOOR(Orders.ROWTIME to hour), state
 

state

count

CA

1

TX

1

CA

1

 

Note there is no entry for TX in the second hour since SQL will only report on states that appear in the 1 hour window.

Query: Streaming aggregation

To which states has a shipment just been dispatched but which have not been dispatched to in the previous hour.

SELECT STREAM warehouseState

FROM (SELECT STREAM warehouseState,count(*) over anHour AS shipped

     FROM Shipments

     WINDOW anHour (PARTITION BY warehouseState RANGE INTERVAL '1' HOUR PRECEDING))

WHERE shipped = 1;
 

shipTime

state

02/02/2008 10:30:09

CA

Query: Running sum

For each order, show the number of orders in the previous hour to the same state, and the total number of orders in the previous hour.

SELECT STREAM

  orderTime,

  orderState,

  count(*)

OVER (PARTITION BY

  orderState RANGE INTERVAL '1' HOUR) AS "stateLastHour",

  count(*) OVER (RANGE INTERVAL '1' HOUR) AS "totalLastHour"

FROM Orders;
 

orderTime

orderState

stateLastHour

totalLastHour

10:00:00

CA

1

1

10:03:06

TX

1

2

10:05:52

CA

2

3