?????? ????????????????????????????join????????sql

[????]

??????????????????????????????????Join????????????Lookup 
Join????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????Lookup
 Join??????????????????????????????


??????????????????????????????union????????????????????????????????????too many 
connection????????


????????????????Lookup 
Join????????????????????????????????????????????????????????????FlinkSQL??????????????????????????????????demo????????????????????????????????????????????????????????????????????????????????????????????



????
1227581...@qq.com



 




------------------ ???????? ------------------
??????:                                                                         
                                               "user-zh"                        
                                                            
<jinfeng1...@gmail.com&gt;;
????????:&nbsp;2024??10??21??(??????) ????10:04
??????:&nbsp;"user-zh"<user-zh@flink.apache.org&gt;;

????:&nbsp;Re: ????????????????????????????join????????sql



hi,


???????????? join ???????????????????? join ?????????? ???????? join 
????????????????????????????????



Best,

Feng



On Wed, Oct 16, 2024 at 10:42?6?2AM ???? <1227581...@qq.com.invalid&gt; wrote:

&gt; ??????????????????????????????????????
&gt; 
????????????????????????????tableFact????????????tableDimA??tableDimDetailB??tableDimDetailC????left
&gt; 
join??????????????????userId??actPkId??bId??cType??create_time??????????????????????????????????actPkId????????
&gt;
&gt; 
*??????????????????????????bId??cType??create_time????????????????????????????????????join??SQL*????????????????????
&gt;
&gt; *????????????????*
&gt;
&gt;&nbsp; tableFact(
&gt;&nbsp; userId String primary key,
&gt;&nbsp; create_time
&gt;&nbsp; )
&gt;
&gt;&nbsp; tableDimA
&gt;&nbsp; (
&gt;&nbsp; actPkId String primary key,
&gt;&nbsp; targetUserId String,
&gt;&nbsp; start_time datetime,
&gt;&nbsp; end_time datetime
&gt;
&gt;&nbsp; )
&gt;
&gt;&nbsp; tableDimDetailB
&gt;&nbsp; (
&gt;&nbsp; actPkId String primary key,
&gt;&nbsp; bId String
&gt;&nbsp; )
&gt;
&gt;&nbsp;&nbsp; tableDimDetailC
&gt;&nbsp; (
&gt;&nbsp; actPkId String primary key,
&gt;&nbsp; cType String
&gt;&nbsp; )
&gt;
&gt;&nbsp; *??????*
&gt; A: fact.userlddimA.targetUserld
&gt; B: fact.bId == dimB.bId
&gt; C: fact.cType == dimA.cType
&gt; D: fact.create time &gt;= dimA.start time&nbsp; and fact.create time <= 
dimA.end
&gt; time
&gt;
&gt; 
*??????????*A??????????B??C??D????????????????B??C??D????????????????????????
&gt;
&gt; *??????????????????*
&gt; ?? (A B C)????A??B??C??????????????????????????
&gt; SELECT *
&gt; FROM tableFact P
&gt; LEFT JOIN tableDimA FOR SYSTEM_TIME AS OF P.proctime&nbsp; AS A ON 
P.userId =
&gt; A.targetUserId
&gt; LEFT JOIN tableDimDetailB FOR SYSTEM_TIME AS OF P.proctime&nbsp; AS B ON
&gt; A.actPkId = B.actPkId AND P.bId=B.bId
&gt; LEFT JOIN tableDimDetailC FOR SYSTEM_TIME AS OF P.proctime&nbsp; AS C ON
&gt; A.actPkId = C.actPkId AND P.cType=B.cType;
&gt;
&gt; ?? (A B C D)????A??B??C D??????????????????????????????????????
&gt; SELECT *
&gt; FROM tableFact P
&gt; LEFT JOIN tableDimA FOR SYSTEM_TIME AS OF P.proctime&nbsp; AS A ON 
P.userId =
&gt; A.targetUserId
&gt; LEFT JOIN tableDimDetailB FOR SYSTEM_TIME AS OF P.proctime&nbsp; AS B ON
&gt; A.actPkId = B.actPkId AND P.bId=B.bId
&gt; LEFT JOIN tableDimDetailC FOR SYSTEM_TIME AS OF P.proctime&nbsp; AS C ON
&gt; A.actPkId = C.actPkId AND P.cType=B.cType
&gt;&nbsp; WHERE P.create_time BETWEEN A.start_time AND A.end_time;
&gt;
&gt; *????????????*
&gt;
&gt;
&gt;
&gt;
&gt; 
??????????????????Flink??????????????????????????????????????????????sql????????????????????????????????????????????????????
&gt;
&gt; ------------------------------
&gt; ????
&gt; 1227581...@qq.com
&gt;
&gt; 
<https://wx.mail.qq.com/home/index?t=readmail_businesscard_midpage&amp;nocheck=true&amp;name=%E6%96%97%E9%B1%BC&amp;icon=http%3A%2F%2Fthirdqq.qlogo.cn%2Fg%3Fb%3Dsdk%26k%3DYiaI7kvDORpPP1623Fzbtyg%26s%3D100%26t%3D525%3Frand%3D1641396237&amp;mail=1227581167%40qq.com&amp;code=&gt;
&gt;
&gt;