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)
);
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')
;
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 |
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 |
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.
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 |
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 |