需求

求某个人当日是否在30分钟内交易了超过5次？

数据给定是用户的交易记录，每条交易记录了用户的交易时间，精确到秒

问题抽象

求用户在滑动窗口大小30分钟内，交易次数是否超过5次？

先验知识

滚动窗口

滚动窗口有固定的大小，是一种对数据进行均匀切片的划分方式。窗口之间没有重叠，也不会有间隔，是“首尾相接”的状态。滚动窗口可以基于时间定义，也可以基于数据个数定义；需要的参数只有一个，就是窗口的大小（window size）。

滑动窗口

与滚动窗口类似，滑动窗口的大小也是固定的。区别在于，窗口之间并不是首尾相接的，而是可以“错开”一定的位置。如果看作一个窗口的运动，那么就像是向前小步“滑动”一样。定义滑动窗口的参数有两个：除去窗口大小（window size）之外，还有一个滑动步长（window slide），代表窗口计算的频率

方法1-自连接

优势

实现逻辑简单，只需要自join,join条件就是两条交易记录间隔小于30分钟，并且交易人相同（根据需求添加连接条件）

缺点

原始表n条记录，自连接后，会产生大概n*n量级的中间表，如果数据量过大，由于节点数据库临时文件大小限制，会导致sql任务调度失败；

加入n是10万，那么会有百亿级别的中间数据，那么数据库处理的时候，需要记录对应的中间数据，需要过大的中间表

sql就不写出了，很简单

方法2-循环的滚动窗口

通过时间换空间，for循环遍历滚动窗口，解决产生过大中间表问题

步骤如下：

获取交易记录最多用户的交易次数，获得循环次数
for循环，每个用户以自己的第i个交易记录开始滚动
- 通过窗口函数 partition by,以每个用户第i条交易记录作为滚动的开始时间，窗口大小为30分钟，计算每个用户的每条记录的序列数
  - 核心就是用户交易记录时间减去其第i调交易记录时间，然后按照30分钟进行均匀分片
  - 分片后，在partition by中以用户id和分片id进行分组，统计数量
- 去掉每个用户小于第一条记录记录时间的交易记录数据
按照用户分组，计算出该用户在每次的滚动窗口中，单个30分钟数据切片的最大交易数量cnt
如果用户存在cnt大于5，就记录该用户信息

declare maxTranCnt int4 :=0;
	total int4:=0;
begin 
	raise notice '开始 %', now();
	-- 获取请求记录中用户最多的请求次数
	select max(index) from (select user_uuid, uri, row_number() over (partition by user_uuid order by last_modified_time) as index from tbl_tran_info) temp into maxTranCnt;
	for i in 1..maxTranCnt LOOP
            -- 遍历计算
            select TEMP.user_uuid, count(1) over(partition by user_uuid, index) as cnt
            -- 获取到每轮去掉第一个后的滚动窗口
            from (select traninfo.user_uuid, (date_pate('epoch', traninfo.last_modified_time::timestamp - beginTime::timestamp) / 18000)::int4 as index
					from tbl_tran_info traninfo
					join (select user_uuid, last_modified_time as beginTime
								from (select *, row_number() over (partition by user_uuid order by last_modified_time) as idx
											from tbl_tran_info)temp2 
											where idx = i) temp2 on temp2.user_uuid = traninfo.user_uuid
					join ((select user_uuid, last_modified_time as beginTime
								from (select *, row_number() over (partition by user_uuid order by last_modified_time) as idx
											from tbl_tran_info)temp2 
											where idx = i) temp on temp.user_uuid = reqinfo.user_uuid and traninfo.last_modified_time > temp.last_modified_time) temp
            )temp;
	end loop;
	return total;
end$body$

参考

flink 滚动窗口、滑动窗口、会话窗口、全局窗口_滑动窗口和滚动窗口的区别-CSDN博客

PostgreSQL多种方式实现滑动窗口计算

需求