Flink sql窗口函数使用案例

qq_34125992

已于 2024-06-24 15:18:13 修改

阅读量75

点赞数 3

分类专栏： # Flink 文章标签： flink

于 2024-05-29 10:23:43 首次发布

本文链接：https://blog.csdn.net/qq_34125992/article/details/139288231

版权

Flink 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

滚动窗口统计每1分钟每个人群的命中次数

create table crowd_hit_detail (
  ts as cast(ctime as TIMESTAMP),
  WATERMARK FOR ts AS ts - INTERVAL '5' SECOND
) WITH (
'offsetReset' = 'latest'
) like `Kafka_1`.`r_ods`.`ods_s_ogv_dmp_job_rt`
;



CREATE TABLE MyUserTable (
  window_start TIMESTAMP,
  window_end TIMESTAMP,
  crowd_id   bigint,
  cnt bigint
) WITH(
  'connector' = 'bsql-log',
  'infinite' = 'true'
);
 
 



--使用传统的分组聚合函数
INSERT into
  MyUserTable
select
  TUMBLE_START(ts, INTERVAL '1' MINUTE),
  cast(crowd_id as bigint) as crowd_id,
  count(1) as cnt
from crowd_hit_detail
where env='prod' and is_hit=1
group by TUMBLE(ts, INTERVAL '1' MINUTE), crowd_id



--使用表值函数 性能更优
INSERT into
  MyUserTable
select
  window_start,
  window_end,
  cast(crowd_id as bigint) as crowd_id,
  count(1) as cnt
from TABLE(TUMBLE(TABLE crowd_hit_detail, DESCRIPTOR(ts), INTERVAL '1' MINUTE))
where env='prod' and is_hit=1
group by crowd_id, window_start, window_end




+I(2024-05-21T11:30,2024-05-21T11:35,21147,18563)
+I(2024-05-21T11:30,2024-05-21T11:35,22546,7)
+I(2024-05-21T11:30,2024-05-21T11:35,22280,40)
+I(2024-05-21T11:30,2024-05-21T11:35,22186,351170)


+I(2024-05-21T11:45,2024-05-21T11:50,22490,2)
+I(2024-05-21T11:50,2024-05-21T11:55,22490,5)
+I(2024-05-21T11:55,2024-05-21T12:00,22490,1)