?????????????????????
????????????????????????????????????????????????????y??????????????á?
????SELECT STREAM rowtime??
????productId??
????units??
????SUM(units) OVER (PARTITION BY FLOOR(rowtime TO HOUR)) AS unitsSinceTopOfHour
????FROM Orders;
????????????????????????????????????????Partition by????С?????????10:59:59??11:00:00??Floor??10:00:00???11:00:00??????????μ????????????sum?????????????á?
????Calcite???????????????????????????????洢??????÷???????????????
???????鴰
???????Window????Over?????????????
???????????????
??????????????Calcite??StreamSQL?????????
???????????л??????????????????????????????????
????????л????????????????????????????????????????????????????????????????????????л????????????????
?????????????????????????????????????????????????????????????????id????????????￡????????????????????
???????????????????????????????????????????
?????????????
????CREATE VIEW HourlyOrderTotals (rowtime?? productId?? c?? su) AS
????SELECT TUMBLE_END(rowtime?? INTERVAL '1' HOUR)??
????productId??
????COUNT(*)??
????SUM(units)
????FROM Orders
????GROUP BY TUMBLE(rowtime?? INTERVAL '1' HOUR)?? productId;
????SELECT STREAM rowtime?? productId
????FROM HourlyOrderTotals
????WHERE c > 2 OR su > 10;
????rowtime | productId
????----------+-----------
????10:00:00 |        30
????11:00:00 |        10
????11:00:00 |        40
?????????????????????????????????????
???????????????Stream????????????????????????????? ????????????????????? ????????????????????????????
?????????????????У?
????WITH HourlyOrderTotals (rowtime?? productId?? c?? su) AS (
????SELECT TUMBLE_END(rowtime?? INTERVAL '1' HOUR)??
????productId??
????COUNT(*)??
????SUM(units)
????FROM Orders
????GROUP BY TUMBLE(rowtime?? INTERVAL '1' HOUR)?? productId)
????SELECT STREAM rowtime?? productId
????FROM HourlyOrderTotals
????WHERE c > 2 OR su > 10;
????rowtime | productId
????----------+-----------
????10:00:00 |        30
????11:00:00 |        10
????11:00:00 |        40
???????????????????????????? ???SQL?е??????????????????????????????????Select????е?Stream???????????? ??
?????????Join???????? ??????Join???????????Join
?????????Join??????????????JOin????????????Join????????????????Join????????????????
????????????????????????Join
????“`sql
????SELECT STREAM o.rowtime?? o.productId?? o.orderId?? o.units??
????p.name?? p.unitPrice
????FROM Orders AS o
????JOIN Products AS p
????ON o.productId = p.productId;
????rowtime | productId | orderId | units | name | unitPrice
????———-+———–+———+——-+ ——-+———–
????10:17:00 | 30 | 5 | 4 | Cheese | 17
????10:17:05 | 10 | 6 | 1 | Beer | 0.25
????10:18:05 | 20 | 7 | 2 | Wine | 6
????10:18:07 | 30 | 8 | 20 | Cheese | 17
????11:02:00 | 10 | 9 | 6 | Beer | 0.25
????11:04:00 | 10 | 10 | 1 | Beer | 0.25
????11:09:30 | 40 | 11 | 12 | Bread | 100
????11:24:11 | 10 | 12 | 4 | Beer | 0.25
????“`
????Order??????Products????????Join??????????????????????д???????????????????????????????浱??????????1?????????Order?????Products??Join??
????????????Join???￡?
????SELECT STREAM o.rowtime?? o.productId?? o.orderId?? s.rowtime AS shipTime
????FROM Orders AS o
????JOIN Shipments AS s
????ON o.orderId = s.orderId
????AND s.rowtime BETWEEN o.rowtime AND o.rowtime + INTERVAL '1' HOUR;
????rowtime | productId | orderId | shipTime
????----------+-----------+---------+----------
????10:17:00 |        30 |       5 | 10:55:00
????10:17:05 |        10 |       6 | 10:20:00
????11:02:00 |        10 |       9 | 11:58:00
????11:24:11 |        10 |      12 | 11:44:00
??????????????????????崰??????????????????where?????????????????Χ????????????????????????????????С?
????DML???
???????????Create View??????????????????????????????????????????Insert AS select??????????????????????????
???????磺
????CREATE VIEW LargeOrders AS
????SELECT STREAM * FROM Orders WHERE units > 1000;
????INSERT INTO LargeOrders
????SELECT STREAM * FROM Orders WHERE units > 1000;
?????????????Upsert????????????????
????UPSERT INTO OrdersSummary
????SELECT STREAM productId??
????COUNT(*) OVER lastHour AS c
????FROM Orders
????WINDOW lastHour AS (
????PARTITION BY productId
????ORDER BY rowtime
????RANGE INTERVAL '1' HOUR PRECEDING)
???????????
??????????
????· ????? SELECT?? WHERE?? GROUP BY?? HAVING?? UNION ALL?? ORDER BY
????· FLOOR??CEIL????
????· ???????Monotonicity??
????· ????????????
???????磺
????sql
????> SELECT STREAM * FROM (VALUES (1?? 'abc'));
????ERROR: Cannot stream VALUES
????δ???
????· ????????Join
????· ??????Join
????· ???????????
????· ????????Order by??Union ALL?????????
????· ???????????
????· ?????????????????????????
????· ??????????????е?Stream?????
????· ?????Order by???????Offset??Limit
????· ?????????????????????????????????в??
????· ???????–????????????????????????????????????????￡???????????????????????????￡?????????????????????
????· HOP??TUMBLE????????????????HOP_START?? HOP_END?? TUMBLE_START?? TUMBLE_END????