flink sql接cdc数据源按最新数据统计问题

Mon, 28 Nov 2022 16:32:17 -0800 

业务需求是mysql订单表按天按供应商实时统计交易金额，订单表会发生修改和删除，用flink 
sql要如何实现呢？开窗取最新一条记录再聚合吗？如果遇到delete记录会不会减去相应的price呢？试着写了如下flink sql不知道对不对


select 
  s.biddate, 
  s.supplier, 
  sum(s.price) 
from 
  (
    select 
      * 
    from 
      (
        select 
          biddate, 
          supplier, 
          price, 
          ROW_NUMBER() OVER (
            PARTITION BY biddate, 
            supplier 
            ORDER BY 
              bidtime DESC
          ) as rownum 
        from 
          (
            select 
              bidtime, 
              date_format(bidtime, 'yyyy-MM-dd-HH') as biddate, 
              supplier, 
              price 
            from 
              orders
          )
      ) as t 
    where 
      t.rownum = 1
  ) as s 
group by 
  s.biddate, 
  s.supplier
;

回复