单流与多维表Join

场景设定

某电商平台进行销售，想要实时分析不同地区进行购买操作的用户总数，用户信息、商品信息为独立维表，购买操作为流信息，实时计算可实现单流与双维表JOIN操作，计算输出各个地区的消费者人数和消费总金额；

解决方案

单双-1.png

操作步骤

源表-字段填写

在流计算源表中进行字段映射填写，从kafka中将数据流映射为具体字段。

after_ordercodeofsys varchar as ordercodeofsys
after_paytime timestamp as paytime
after_goodscode varchar as goodscode
after_buyerscode varchar as buyerscode

创建结果表 dts_ds_ssddt_orders_target

create table dts_ds_ssdt_orders_target(
   buyerplace   varchar(255),
   all_count    bigint,
   people_count bigint
)

FlinkSQL 代码

insert into dts_ds_ssddt_orders_target
select
   b.buyerplace as buyerplace,
   sum(CAST(g.goodsamount as bigint)) as all_count,
   COUNT(b.buyername) as people_count
from
   dts_ds_ssddt_orders_source s
       join  dts_ds_ssddt_buyer_info b on s.buyerscode = b.buyerscode
       join  dts_ds_ssddt_goods_info g on s.goodscode = g.goodscode
group by
   b.buyerplace

运行结果

实时采集、FlinkSQL任务有正常的数据吞吐

单双-2.png

单双-3.png

结果表内数据根据消费者地区划分并自动更新

单单-4.png