[
https://issues.apache.org/jira/browse/CALCITE-968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15007422#comment-15007422
]
Milinda Lakmal Pathirage commented on CALCITE-968:
--------------------------------------------------
Below are some sample queries I came up with. But I am not sure what exactly
[~julianhyde] mentioned by
{quote}
the join condition should involve a monotonic expression
{quote}
above.
In below queries I assumed that we are not going to extend FROM and JOIN
clauses with OVER to express the window specification and put <monotonic
expression> to represent a monotonic expression which can express the window
spec. All the windows expressed below are sliding windows.
h2. Sample Schemas
h3. Order Processing
* {{Orders (rowtime, productId, orderId, units)}} - a stream
* {{Products (productId, name, supplierId)}} - a table
* {{Shipments (rowtime, shipmentId, orderId)}} - a stream
* {{Suppliers (supplierId, name, loction)}} - a table
h3. Packet Monitoring
* {{PacketsR1 (rowtime, sourcetime, packetId)}} - a stream
* {{PacketsR2 (rowtime, sourcetime, packetId)}} - a stream
h2. Stream-to-Stream Joins
h3. Network Packet Monitoring
{code:sql|borderStyle=solid}
SELECT STREAM
PacketsR1.sourcetime, PacketsR1.packetId, PacketsR2.rowtime -
PacketsR1.rowtime AS timeToTravel
FROM PacketsR1
JOIN PacketsR2 ON <monotonic expression> AND PacketsR1.packetId =
PacketsR2.packetId
{code}
With {{OVER}} clause:
{code:sql|borderStyle=solid}
SELECT STREAM
PacketsR1.sourcetime, PacketsR1.packetId, PacketsR2.rowtime -
PacketsR1.rowtime AS timeToTravel
FROM PacketsR1 (ORDER BY rowtime RANGE INTERVAL '2' SECOND PRECEDING)
JOIN PacketsR2 (ORDER BY rowtime RANGE INTERVAL '2' SECOND PRECEDING) ON
PacketsR1.packetId = PacketsR2.packetId
{code}
h3. Online Auctions
>From http://www.sqlstream.com/docs/index.html?qs_stream_window_joins.html
But joining the bids from last minute with Asks stream may be a valid query.
But we don't have support for {{OVER}} in {{FROM}} and {{JOIN}} clauses. As
Julian mentioned in the mail, it may be possible to express *window spec* as a
monotonic expression in the {{ON}} clause.
{code:sql|borderStyle=solid}
SELECT STREAM
Asks.askId as askId, Bids.bidId as bidId, Asks.rowtime as askRowtime,
Bids.rowtime as bidRowtime, Asks.ticker, Asks.shares as askShares, Asks.prices
as askPrice, Bids.shares as bidShares, Bids.price as bidPrice
FROM Bids
JOIN Asks ON <monotonic expression> AND Asks.ticker = Bids.ticker
{code}
Above query with {{OVER}} clause.
{code:sql|borderStyle=solid}
SELECT STREAM
Asks.askId as askId, Bids.bidId as bidId, Asks.rowtime as askRowtime,
Bids.rowtime as bidRowtime, Asks.ticker, Asks.shares as askShares, Asks.prices
as askPrice, Bids.shares as bidShares, Bids.price as bidPrice
FROM Bids OVER (ORDER BY rowtime RANGE INTERVAL '1' MINUTE PRECEDING)
JOIN Asks OVER (ROWS CURRENT ROW) ON Asks.ticker = Bids.ticker
{code}
h2. Stream-to-Relaiton Joins
h3. Add Supplier Information to Orders Stream
{code:sql|borderStyle=solid}
SELECT STREAM
Orders.rowtime, Orders.orderId, Orders.productId, Orders.units,
Products.supplierId
FROM Orders
JOIN Products ON Orders.productId = Products.productId
{code}
{code:sql|borderStyle=solid}
CREATE VIEW OrdersWithSupplierId (rowtime, orderId, productId, units,
supplierId) AS
SELECT STREAM
Orders.rowtime, Orders.orderId, Orders.productId, Orders.units,
Products.supplierId
FROM Orders
JOIN Products ON Orders.productId = Products.productId
SELECT STREAM
OrdersWithSupplierId.rowtime, OrdersWithSupplierId.orderId,
OrdersWithSupplierId.supplierId, Suppliers.locaiton
FROM OrdersWithSupplierId
JOIN Suppliers ON OrdersWithSupplierId.supplierId = Suppliers.supplierId
{code}
> Support stream joins
> --------------------
>
> Key: CALCITE-968
> URL: https://issues.apache.org/jira/browse/CALCITE-968
> Project: Calcite
> Issue Type: New Feature
> Components: core, stream
> Reporter: Milinda Lakmal Pathirage
> Assignee: Julian Hyde
>
> Stream joins are used to relate information from different streams or stream
> and relation combinations. Calcite lacks (proper) support for
> stream-to-relation joins and stream-to-stream joins.
> stream-to-relation join like below fails at the SQL validation stage.
> select stream orders.orderId, orders.productId, products.name from orders
> join products on orders.productId = products.id
> But if 'products' is a stream, the query is valid according to Calcite, even
> though the stream-to-stream join in above query is not valid due to unbounded
> nature of streams.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
