More Complex SQL Examples

More Complex Streaming SQL Examples

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

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
SHIPMENT 1001
SHIPMENT 1000

Query that Aggregates a Stream

Report each hour the order count for each state.

SELECT state, count(*)
FROMOrders
GROUP BY FLOOR(Orders.ROWTIME to hour), state;
state count
CA 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:05:52 CA 2 3