展开查看详情
1. SELECT * FROM RatesHistory; ᳵ ᨵ૰ ሲ time currency rate 09:00 USD 102 09:00 Euro 114 09:00 Yen 1 10:45 Euro 116 11:15 Euro 119 11:49 USD 99 ... ... ...
2. RatesHistory ᳵ ᨵ૰ ሲ TemporalTableFunction rates = time currency rate ratesHistory 09:00 USD 102 .createTemporalTableFunction( 09:00 Euro 114 "time", "currency"); 09:00 Yen 1 10:45 Euro 116 11:15 Euro 119 tableEnv.registerFunction("Rates", rates); 11:49 USD 99 ... ... ...
3. RatesHistory ᳵ ᨵ૰ ሲ time currency rate SELECT * FROM Rates('10:15'); 09:00 USD 102 09:00 Euro 114 time currency rate 09:00 Yen 1 09:00 USD 102 10:45 Euro 116 09:00 Euro 114 11:15 Euro 119 09:00 Yen 1 11:49 USD 99 ... ... ...
4. RatesHistory ᳵ ᨵ૰ time currency rate SELECT * FROM Rates('11:50’); 09:00 USD 102 09:00 Euro 114 time currency rate 09:00 Yen 1 11:49 USD 99 10:45 Euro 116 11:15 Euro 119 11:15 Euro 119 09:00 Yen 1 11:49 USD 99 ... ... ...
5. RatesHistory ᳵ ᨵ૰ ሲ Orders time currency rate ᳵ ᨵ૰ ᰁ 09:00 USD 102 time currency amount 09:00 Euro 114 10:15 Euro 2 09:00 Yen 1 10:00 Yen 50 10:45 Euro 116 11:35 Euro 2 11:15 Euro 119 ... ... ... 11:49 USD 99 ... ... ...
6. ܲݥᤒᬳള Temporal Table Join SELECT o.amount * r.rate FROM Orders o, LATERAL TABLE (Rates(o.time)) r WHERE o.currency = r.currency
7. RatesHistory Orders Result ᳵ ᨵ૰ ሲ ᳵ ᨵ૰ ᰁ ሲ ᰁ SELECT o.amount * r.rate time currency rate time currency amount rate * amount FROM Orders o, 09:00 USD 102 10:15 Euro 2 228 LATERAL TABLE (Rates(o.time)) r 09:00 Euro 114 ... ... ... ... WHERE 09:00 Yen 1 o.currency = r.currency 10:45 Euro 116 11:15 Euro 119 11:49 USD 99 ... ... ...
8. RatesHistory Orders Result ᳵ ᨵ૰ ሲ ᳵ ᨵ૰ ᰁ ሲ ᰁ SELECT o.amount * r.rate time currency rate time currency amount rate * amount FROM Orders o, 09:00 USD 102 10:15 Euro 2 228 LATERAL TABLE (Rates(o.time)) r 09:00 Euro 114 10:00 Yen 50 50 WHERE 09:00 Yen 1 11:35 Euro 2 238 o.currency = r.currency 10:45 Euro 116 ... ... ... ... 11:15 Euro 119 11:49 USD 99 ... ... ...
9. ଉᥠᬳള Regular joins ဩ SELECT * FROM A a, B b WHERE a.id = b.id Syntax ࢱ ӷӻᤒ໒ஂྌਠݢقᥠ Scope All rows from both tables visible to each other 㲌ਂ ಅํᦕ୯ᶳ෫ᴴ๗ਂכ Memory All records must be persisted indefinitely
10. ᳵᑻݗᬳള Time-windowed Joins ဩ SELECT * FROM A a, B b WHERE a.id = b.id AND Syntax a.time BETWEEN b.time AND b.time + 1 `DAY` ࢱ ಅํᤈࣁᤩਧԎጱᳵᑻݗӾݢᥠ Scope Rows visible within a defined time window 㲌ਂ ಅํᦕ୯ࣁሿࣁᑻݗᳩଶԏᳵҁےӤ֖ᕚ᬴҂ Memory All records between now and window length (plus watermark delay)
11. ܲݥᤒᬳള Temporal Table Joins ဩ SELECT * FROM A a, LATERAL TABLE (B(a.time)) Syntax WHERE a.id = b.id ࢱ ݝᕳਧጱ%ጱ๋ෛᇇݢᥠ a.time Scope Visible is only the latest version of B for given a.time 㲌ਂ ᤒ໒%ғಅํ%ጱᇇࣁሿࣁ֖ᕚ᬴ԏᳵ Memory Table B: all versions of B between now and watermark delay ᤒ໒$ғಅํᦕ୯ࣁሿࣁ֖ᕚ᬴ԏᳵ Table A: all records between now and watermark delay
12. ཛྷୗᦩڦ Pattern recognition
13. ཛྷୗᦩڦ Pattern recognition ᐏֺғ Examples: ! (S M{2,} E) ! (A B+ C* D) ! (START_ROW PRICE_DOWN+ PRICE_UP)
14.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)
15.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)
16.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)
17.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)
18.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)
19.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)
20. ཛྷୗᦩڦ Pattern recognition MATCH_RECOGNIZE ݙӨ GROUP BY ํӞԶፘ֒ԏ॒ MATCH_RECOGNIZE clause has some similarities with GROUP BY ฎ 64/ຽٵጱӞ᮱ړ It is a part of the SQL 2016 standard
21. ٌ՜౮ຎ Other work
22. SQL Client Flink SQL ጱեᤈളݗ Command line interface for Flink SQL ෫ᖫᑕᵱᥝ No programming required
23.ᬰᤈӾጱᶱፓ Ongoing work ᬳള໒ୗ Connectors & formats SQL Client क़᮱ፓ୯ඪ೮ External catalog support
24.