|
1 ）一个kafka的topic order
{“ord_id”:“1”,“city_id”:“300100”,“submit_time”:“2021-11-25 11:32:24”,“total_fee”:25}
{“ord_id”:“1”,“city_id”:“300100”,“submit_time”:“2021-11-25 11:32:55”,“total_fee”:25}
{“ord_id”:“1”,“city_id”:“300100”,“submit_time”:“2021-11-25 11:35:14”,“total_fee”:25}
{“ord_id”:“2”,“city_id”:“300100”,“submit_time”:“2021-11-25 11:36:51”,“total_fee”:48}
{“ord_id”:“2”,“city_id”:“300100”,“submit_time”:“2021-11-25 11:37:55”,“total_fee”:48}
{“ord_id”:“3”,“city_id”:“300200”,“submit_time”:“2021-11-25 11:38:57”,“total_fee”:35}
2）按key取最新1条后的结果集 order_unique_view
select * from order_unique_view
;
ord_id city_id submit_time total_fee
1 300100 2021-11-25 11:35:14 25
2 300100 2021-11-25 11:37:55 48
3 300200 2021-11-25 11:38:57 35
3）对order_unique_view进行窗口聚合
insert into dwm_hy_trd_ord_rt_ck
select city_id
,sum(total_fee) total_fee
from order_unique_view
group by city_id,TUMBLE(time_pt, interval ‘1’ day, interval ‘30’ MINUTE)
;
结果：
city_id ,total_fee
300100 ,73
300200 ,35
|