展开查看详情
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
22 . SQL Client
Flink SQL ጱեᤈളݗ
Command line interface for Flink SQL
෫ᖫᑕᵱᥝ
No programming required
23 .ᬰᤈӾጱᶱፓ
Ongoing work
ᬳള໒ୗ
Connectors & formats
SQL Client
क़᮱ፓ୯ඪ೮
External catalog support
