ETL中如何实现按5分钟步长统计15分钟滑动窗口去重用户数及最大值 ...

在 ETL 中实现的具体步骤

整体目标：

• 以 5 分钟为步长，生成连续的时间窗口
• 统计每个 15 分钟窗口内的去重登录用户数
• 最后得到各窗口的去重人数，以及整体最大值

例如需要统计这些窗口：

• 08:00 - 08:15
• 08:05 - 08:20
• 08:10 - 08:25

一、准备原始数据
原始数据至少需要以下两个字段：

• 登录时间
• 用户名

例如：

说明：

• 登录时间最好是时间类型
• 如果当前是字符串，也可以在 ETL 中先转成时间类型

二、第一步：新增一个“5分钟对齐时间”字段
在 ETL 中先新增一个计算字段，例如命名为：

• bucket_5

这个字段的含义是：
把登录时间对齐到前一个 5 分钟整点。

例如：

• 08:12:34 -> 08:10:00
• 08:07:10 -> 08:05:00
• 08:18:22 -> 08:15:00

函数参考：

from_unixtime(floor(unix_timestamp(cast(login_time as timestamp)) / 300) * 300)

这一步相当于先把零散的登录时间统一整理到“每 5 分钟一个刻度”的时间轴上。

三、第二步：生成 3 路窗口开始时间
因为窗口长度是 15 分钟，步长是 5 分钟，所以每条登录记录最多会落入 3 个窗口。

例如一条登录记录时间是 08:12:34，它会被计入：

• 08:00 - 08:15
• 08:05 - 08:20
• 08:10 - 08:25

因此，在 ETL 中需要生成 3 路结果，每一路都保留相同字段结构，但 win_start 的值不同。

建议统一新增一个字段名：

• win_start

分别生成如下 3 路：

第 1 路
表示当前 5 分钟桶本身：

bucket_5

第 2 路
表示向前 5 分钟的窗口起点：

from_unixtime(unix_timestamp(bucket_5) - 5 * 60)

第 3 路
表示向前 10 分钟的窗口起点：

from_unixtime(unix_timestamp(bucket_5) - 10 * 60)

四、第三步：将 3 路结果做纵向合并
这里的合并方式，可以使用 SQL 中的：

UNION ALL

也就是把多路结果“按行追加”，不是横向拼列。

参考写法：

SELECT 

    a.`字段1`,

    a.`字段2`,

    a.`字段3`

FROM 

    `input1` a

UNION ALL

SELECT 

    b.`字段1`,

    b.`字段2`,

    b.`字段3`

FROM 

    `input2` b

在当前场景中，含义是：

• 第 1 路算出一个 win_start
• 第 2 路算出一个 win_start
• 第 3 路再算出一个 win_start
• 最后把这 3 路结果按行追加到一起

这样原来 1 条登录记录，就会变成 3 条窗口归属记录。

例如原始 1 条记录：

合并后会变成：

业务理解：
不是在一行上增加 3 个窗口字段，而是把 1 条登录记录拆成 3 条不同窗口起点的记录，便于后续按窗口统计人数。

五、第四步：新增窗口结束时间
在合并后的结果上，再新增一个计算字段：

• win_end

公式为：

from_unixtime(unix_timestamp(win_start) + 15 * 60)

这样就可以得到完整窗口：

• win_start
• win_end

例如：

• 08:00 -> 08:15
• 08:05 -> 08:20

六、第五步：按窗口统计去重用户数
接下来新增一个聚合节点，按窗口统计人数。

分组字段：

• win_start
• win_end

聚合指标：

• 用户名 做“去重计数”

输出字段建议命名为：

• uv_15m

这样就能得到每个 15 分钟窗口的去重登录用户数。

例如结果会类似：

七、第六步：取所有窗口中的最大值
如果最终还需要一个“最高峰值”，可以在上一步结果上再做一次聚合。

聚合方式：

• 对 uv_15m 取最大值

输出字段建议命名为：

• max_uv_15m

例如：