Hi, casel. 使用三次lookup join是可以实现的,加上缓存,性能应该不差。
WITH users AS ( SELECT * FROM (VALUES(1, 'zhangsan'), (2, 'lisi'), (3, 'wangwu')) T (id, name) ) SELECT orders.id, u1.name as creator_name, u2.name as approver_name, u3.name as deployer_name FROM ( SELECT * FROM (VALUES(1, 1, 2, 3)) T (id, creator_id, approver_id, deployer_id) ) AS orders LEFT JOIN users AS u1 ON orders.creator_id = u1.id LEFT JOIN users AS u2 ON orders.approver_id = u2.id LEFT JOIN users AS u3 ON orders.deployer_id = u3.id; Best, Jiabao On 2023/11/22 12:44:47 "casel.chen" wrote: > 有一张维表 user,包含id和name字段 > id | name > ----------------- > 1 | zhangsan > 2 | lisi > 3 | wangwu > > > 现在实时来了一条交易数据 > id | creator_id | approver_id | deployer_id > --------------------------------------------------------- > 1 | 1 | 2 | 3 > > > 希望lookup维表user返回各用户名称 > id | creator_name | approver_name | deployer_name > ------------------------------------------------------------------------ > 1 | zhangsan | lisi |. wangwu > > > > 以上场景用flink sql要如何实现? > > > > > > > > > > > > > > > 在 2023-11-22 12:37:10,"Xuyang" <xy...@163.com> 写道: > >Hi, casel. > >可以对“批量lookup join”再描述详细一点么?看上去是符合一个lookup join里直接带上k1=v1 and k2=v2 and > >k3=v3的用法的。 > > > > > > > > > >-- > > > > Best! > > Xuyang > > > > > > > > > >在 2023-11-22 11:55:11,"casel.chen" <ca...@126.com> 写道: > >>一行数据带了三个待lookup查询的key,分别是key1,key2和key3 > >> > >> > >>id key1 key2 key3 > >>想实现批量lookup查询返回一行数据 id value1 value2 value3 > >> > >> > >>查了下目前包括jdbc connector在内的lookup都不支持批量查询,所以只能先将多列转成多行分别lookup再将多行转成多列,如下所示 > >>id key1 key2 key3 > >>先将多列转成多行 > >>id key1 > >>id key2 > >>id key3 > >> > >>分别进行lookup join后得到 > >>id value1 > >>id value2 > >>id value3 > >>最后多行转多列返回一行数据 > >> > >>id value1 value2 value3 > >> > >> > >>上述方案目前我能想到的是通过udtf + udaf来实现,但缺点是不具备通用性。Flink社区打算原生支持么? >