????Calcite?е????SQL????????·
????????????ü???SQL????????????????SQL????????????μ??
????????????????SQL????а????????????? ??????Oracle?? ?????????????????????????л???????????????????????????????Oracle data warehouse??Match Recognize?????????л????????
??????????й?????????????У????????????????????????????????????????????????????SQL?ж????????????????????Ceil??Tumble??Hop??????????????????
?????????·?? ???????SQL?????????????????????????????????SQL??￡?????????????л????? ??
????Calcite StreamSQL???
????1????DDL?????????schema????Or???TODO????????????????????
??????????????schema??
????Orders (rowtime?? productId?? orderId?? units) - ????????????
????Products (rowtime?? productId?? name) - ??
????Shipments (rowtime?? orderId) - ??
????2??????????????Stream??????????????????????????????????????????????????????????????????????????????????????
?????????????????????????
????SELECT STREAM *
????FROM Orders;
????rowtime | productId | orderId | units
????----------+-----------+---------+-------
????10:17:00 |        30 |       5 |     4
????10:17:05 |        10 |       6 |     1
????10:18:05 |        20 |       7 |     2
????10:18:07 |        30 |       8 |    20
????11:02:00 |        10 |       9 |     6
????11:04:00 |        10 |      10 |     1
????11:09:30 |        40 |      11 |    12
????11:24:11 |        10 |      12 |     4
?????????????
????SELECT *
????FROM Orders;
????rowtime | productId | orderId | units
????----------+-----------+---------+-------
????08:30:00 |        10 |       1 |     3
????08:45:10 |        20 |       2 |     1
????09:12:21 |        10 |       3 |    10
????09:27:44 |        30 |       4 |     2
????4 records returned.
???????????????????????????
????SELECT * FROM Shipments;
????ERROR: Cannot convert stream 'SHIPMENTS' to a table
????SELECT STREAM * FROM Products;
????ERROR: Cannot convert table 'PRODUCTS' to a stream
????1???????????????having?????????sql??￡??????????
????2??????????????????дStream????????????д????Ч??
??????:
????SELECT STREAM rowtime?? productId
????FROM (
????SELECT TUMBLE_END(rowtime?? INTERVAL '1' HOUR) AS rowtime??
????productId??
????COUNT(*) AS c??
????SUM(units) AS su
????FROM Orders
????GROUP BY TUMBLE(rowtime?? INTERVAL '1' HOUR)?? productId)
????WHERE c > 2 OR su > 10;
????rowtime | productId
????----------+-----------
????10:00:00 |        30
????11:00:00 |        10
????11:00:00 |        40
??????????????
????????????Tumbling window??
???????????????????????????
????SELECT STREAM CEIL(rowtime TO HOUR) AS rowtime??
????productId??
????COUNT(*) AS c??
????SUM(units) AS units
????FROM Orders
????GROUP BY CEIL(rowtime TO HOUR)?? productId;
????rowtime | productId |       c | units
????----------+-----------+---------+-------
????11:00:00 |        30 |       2 |    24
????11:00:00 |        10 |       1 |     1
????11:00:00 |        20 |       1 |     7
????12:00:00 |        10 |       3 |    11
????12:00:00 |        40 |       1 |    12
????????????С????????????????С??????????11?????????1??????????????????????????????????????
?????????????????????????
????SELECT STREAM TUMBLE_END(rowtime?? INTERVAL '1' HOUR) AS rowtime??
????productId??
????COUNT(*) AS c??
????SUM(units) AS units
????FROM Orders
????GROUP BY TUMBLE(rowtime?? INTERVAL '1' HOUR)?? productId;
????rowtime | productId |       c | units
????----------+-----------+---------+-------
????11:00:00 |        30 |       2 |    24
????11:00:00 |        10 |       1 |     1
????11:00:00 |        20 |       1 |     7
????12:00:00 |        10 |       3 |    11
????12:00:00 |        40 |       1 |    12
????????磬???????С??????ν??????12????????????
????SELECT STREAM
????TUMBLE_END(rowtime?? INTERVAL '30' MINUTE?? TIME '0:12') AS rowtime??
????productId??
????COUNT(*) AS c??
????SUM(units) AS units
????FROM Orders
????GROUP BY TUMBLE(rowtime?? INTERVAL '30' MINUTE?? TIME '0:12')??
????productId;
????rowtime | productId |       c | units
????----------+-----------+---------+-------
????10:42:00 |        30 |       2 |    24
????10:42:00 |        10 |       1 |     1
????10:42:00 |        20 |       1 |     7
????11:12:00 |        10 |       2 |     7
????11:12:00 |        40 |       1 |    12
????11:42:00 |        10 |       1 |     4
??????????(HOP window)
??????????????????????????????
????????????????廯???????????????????????б?????????
?????????????????????????????11:00?????????л?????08:00??11:00??????????09:00??12:00???????????????ж??????????С?
????SELECT STREAM
????HOP_END(rowtime?? INTERVAL '1' HOUR?? INTERVAL '3' HOUR) AS rowtime??
????COUNT(*) AS c??
????SUM(units) AS units
????FROM Orders
????GROUP BY HOP(rowtime?? INTERVAL '1' HOUR?? INTERVAL '3' HOUR);
????rowtime |        c | units
????----------+----------+-------
????11:00:00 |        4 |    27
????12:00:00 |        8 |    50
??????????(sliding window)
????Calcite?е??????????????Over????????????????SQL?е??????????
????SELECT STREAM rowtime??
????productId??
????units??
????SUM(units) OVER (ORDER BY rowtime RANGE INTERVAL '1' HOUR PRECEDING) unitsLastHour
????FROM Orders;
??????????????????????10??????????????С?????????????????????
????SELECT STREAM *
????FROM (
????SELECT STREAM rowtime??
????productId??
????units??
????AVG(units) OVER product (RANGE INTERVAL '10' MINUTE PRECEDING) AS m10??
????AVG(units) OVER product (RANGE INTERVAL '7' DAY PRECEDING) AS d7
????FROM Orders
????WINDOW product AS (
????ORDER BY rowtime
????PARTITION BY productId))
????WHERE m10 > d7;
??????????????У????Window????????巋??????????????OVER??????????????????????????????window????ж??????е?????
??????????????У??????????????????10?????7?????????????????????????Щ???????????????????
????????????????????????Щ?????
????* ???鴰??
????* ??????δ??????У????????????????????
????* ???????????RANK????????????