More Complex SQL Examples

More Complex Streaming SQL Examples

Schema for these examples

CREATE OR REPLACE SCHEMA "Trading";
SET SCHEMA '"Trading"';
CREATE OR REPLACE STREAM "Orders"
(
"orderTime" TIMESTAMP,
"orderId" INTEGER,
"productId" INTEGER,
"quantity" INTEGER,
"unitPrice" DECIMAL(11,2),
"shippingState" CHAR(2)
);
CREATE OR REPLACE STREAM "Shipments"
(
"shipTime" TIMESTAMP,
"orderId" INTEGER,
"warehouseState" CHAR(2)
);

Sample Data

The following sample data set will be used in the examples and explanations presented in the following sections. To test any of the queries below open sqlline, run the query, open a separate terminal window, and run the sample data set below.

INSERT INTO "Trading"."Orders" ("orderTime", "orderId", "productId", "quantity", "unitPrice", "shippingState")
VALUES
(CAST('2019-03-30 03:04:00.000' as TIMESTAMP), 1000, 510, 1, 10.50, 'CA'),
(CAST('2019-03-30 03:06:00.000' as TIMESTAMP), 1001, 21, 3, 6.25, 'TX'),
(CAST('2019-03-30 03:30:00.000' as TIMESTAMP), 1002, 601, 5, 1.25, 'CA')
;

INSERT INTO "Trading"."Shipments" ("shipTime", "orderId", "warehouseState")
VALUES
(CAST('2019-03-30 03:04:00.000' as TIMESTAMP), 1001, 'TX'),
(CAST('2019-03-30 03:06:00.000' as TIMESTAMP), 1000, 'TX'),
(CAST('2019-03-30 03:30:00.000' as TIMESTAMP), 1002, 'CA')
;

Query that filters a stream

After you have created the streams above, you can run the following query to filter only those orders with a shipping state of CA. (To test the query, open a separate terminal and run the sample data set above.)

SELECT STREAM "orderTime", "orderId", "shippingState"
FROM "Trading"."Orders"
WHERE "shippingState" = 'CA';
orderTime orderId shippingState
2019-03-30 03:04:00.0 1000 CA
2019-03-30 03:30:00.0 1002 CA

Query That Merges Two Streams

SELECT STREAM *
FROM (
SELECT STREAM 'ORDERS' as type, "orderId" FROM "Trading"."Orders"
UNION ALL
SELECT STREAM 'SHIPMENT' as type, "orderId" FROM "Trading"."Shipments"
);

TYPE orderId
ORDERS 1000
ORDERS 1001
ORDERS 1002
SHIPMENT 1001
SHIPMENT 1000
SHIPMENT 1002

Query that Aggregates a Stream

Report each hour the order count for each state.

SELECT STREAM "shippingState", count(*) as "count"
FROM "Trading"."Orders" AS s
GROUP BY FLOOR(s.ROWTIME to minute), "shippingState";

Result:

shippingState count
CA 2
TX 1

Note: The code above will only work if you allow a minute to elapse between inserting the rows above.

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 "Trading"."Shipments"
  WINDOW anHour AS (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",
"shippingState",
count(*)
OVER (PARTITION BY
"shippingState" RANGE INTERVAL '1' HOUR PRECEDING) AS "stateLastHour",
count(*) OVER (RANGE INTERVAL '1' HOUR PRECEDING) AS "totalLastHour"
FROM "Trading"."Orders";
orderTime shippingState stateLastHour totalLastHour
2019-03-30 03:04:00.0 CA 1 1
2019-03-30 03:06:00.0 TX 1 2
2019-03-30 03:30:00.0 CA 2 3