Re: 关于如何根据参数条件动态生成join的关联的sql

Mon, 21 Oct 2024 07:05:05 -0700 

hi,


先计算所有的 join 结果，结果中把相关的 join 字段带上， 再去基于 join 的结果去选择需要的结果是否可以？



Best,

Feng



On Wed, Oct 16, 2024 at 10:42 AM 斗鱼 <1227581...@qq.com.invalid> wrote:

> 大佬们，目前我遇到一个业务场景的问题，
> 有如下四张表，其中一张事实表tableFact和三张维度表tableDimA、tableDimDetailB、tableDimDetailC进行left
> join关联，这四张表根据userId、actPkId、bId、cType、create_time这几个参数进行关联，三张维度表通过actPkId进行关联
>
> *目前需求是想实现根据维度表bId、cType、create_time这三个字段的数据的情况，动态转成对应join的SQL*，具体相关信息如下：
>
> *表结构大致信息：*
>
>  tableFact(
>  userId String primary key,
>  create_time
>  )
>
>  tableDimA
>  (
>  actPkId String primary key,
>  targetUserId String,
>  start_time datetime,
>  end_time datetime
>
>  )
>
>  tableDimDetailB
>  (
>  actPkId String primary key,
>  bId String
>  )
>
>   tableDimDetailC
>  (
>  actPkId String primary key,
>  cType String
>  )
>
>  *假定：*
> A: fact.userlddimA.targetUserld
> B: fact.bId == dimB.bId
> C: fact.cType == dimA.cType
> D: fact.create time >= dimA.start time  and fact.create time <= dimA.end
> time
>
> *前置条件：*A必须存在，B、C、D可以组合存在，即B、C、D的所有可能组合条件存在，
>
> *举例如下参数情况：*
> 如 (A B C)表示A、B、C的条件都存在的情况，组合出
> SELECT *
> FROM tableFact P
> LEFT JOIN tableDimA FOR SYSTEM_TIME AS OF P.proctime  AS A ON P.userId =
> A.targetUserId
> LEFT JOIN tableDimDetailB FOR SYSTEM_TIME AS OF P.proctime  AS B ON
> A.actPkId = B.actPkId AND P.bId=B.bId
> LEFT JOIN tableDimDetailC FOR SYSTEM_TIME AS OF P.proctime  AS C ON
> A.actPkId = C.actPkId AND P.cType=B.cType;
>
> 如 (A B C D)表示A、B、C D的条件都存在的情况，组合出如下查询语句
> SELECT *
> FROM tableFact P
> LEFT JOIN tableDimA FOR SYSTEM_TIME AS OF P.proctime  AS A ON P.userId =
> A.targetUserId
> LEFT JOIN tableDimDetailB FOR SYSTEM_TIME AS OF P.proctime  AS B ON
> A.actPkId = B.actPkId AND P.bId=B.bId
> LEFT JOIN tableDimDetailC FOR SYSTEM_TIME AS OF P.proctime  AS C ON
> A.actPkId = C.actPkId AND P.cType=B.cType
>  WHERE P.create_time BETWEEN A.start_time AND A.end_time;
>
> *表结构图信息*
>
>
>
>
> 想请教下各位大佬，Flink或者有什么技术能实现这种动态多条件参数组合转成sql的实现方案吗？期待能收到各位大佬帮忙的指导回复，感谢
>
> ------------------------------
> 斗鱼
> 1227581...@qq.com
>
> <https://wx.mail.qq.com/home/index?t=readmail_businesscard_midpage&nocheck=true&name=%E6%96%97%E9%B1%BC&icon=http%3A%2F%2Fthirdqq.qlogo.cn%2Fg%3Fb%3Dsdk%26k%3DYiaI7kvDORpPP1623Fzbtyg%26s%3D100%26t%3D525%3Frand%3D1641396237&mail=1227581167%40qq.com&code=>
>
>

回复