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

["casel.chen" <ca...@126.com>]

业务需求是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
;

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

[Leonard Xu <xb...@gmail.com>]

> On Nov 29, 2022, at 8:32 AM, casel.chen <ca...@126.com> wrote:
> 
> 业务需求是mysql订单表按天按供应商实时统计交易金额，订单表会发生修改和删除，用flink sql要如何实现呢？开窗取最新一条记录再聚合吗？如果遇到delete记录会不会减去相应的price呢？试着写了如下flink sql不知道对不对


会的，可以看下flink sql相关的原理文章，百度/谷歌一搜一大把。

祝好
Leonard


> 
> 
> 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
> ;
>