滚动窗口统计每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)