PostgreSQL中如何按相近扫描时间对Product_ID进行聚类分组？

问题背景 #

现有记录RFID产品扫描信息的Scans表，结构及数据如下：

需求是按指定时间容差（比如10秒）把扫描时间相近的Product_ID聚类分组，以10秒容差为例，期望得到包含{1,2,3}和{4}的两个分组。

最初尝试的SQL语句是：

SELECT ARRAY_AGG(DISTINCT Product_ID) FROM scans GROUP BY ROUND(EXTRACT(EPOCH FROM created_at) / 10);

但这个方法有边缘场景问题：比如两个产品分别在第19秒和第21秒扫描时，会被分到不同分组，达不到需求。

可靠解决方案 #

可以通过窗口函数计算时间间隔+累计分组ID的方式实现连续时间聚类，核心逻辑如下：

• 先按扫描时间排序，计算每条记录与上一条记录的时间差
• 当时间差超过设定的容差（或当前是第一条记录），标记为新分组的起点
• 累加这些起点标记，生成唯一的分组ID
• 最后按分组ID聚合Product_ID

以10秒容差为例，SQL语句如下：

WITH ranked_scans AS (
    SELECT
        Product_ID,
        Created_At,
        -- 计算当前记录与前一条的时间差（单位：秒）
        EXTRACT(EPOCH FROM Created_At - LAG(Created_At) OVER (ORDER BY Created_At)) AS time_diff
    FROM scans
),
grouped_scans AS (
    SELECT
        Product_ID,
        -- 累计超过容差的次数，生成唯一分组ID
        SUM(CASE WHEN time_diff > 10 OR time_diff IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY Created_At) AS group_id
    FROM ranked_scans
)
SELECT ARRAY_AGG(DISTINCT Product_ID) AS product_group
FROM grouped_scans
GROUP BY group_id
ORDER BY group_id;

方案细节说明 #

• LAG(Created_At)会获取当前记录的上一条扫描时间，通过计算时间差判断是否属于同一组
• 第一条记录没有上一条数据，time_diff为NULL，直接归为第一个分组
• 只要当前记录和上一条的时间差超过10秒，就触发新分组，分组ID加1
• 最后按group_id聚合，就能得到符合要求的产品分组

这个方法能完美处理边缘场景：比如19秒和21秒的两条记录，时间差仅2秒（小于10秒），会被分到同一组，解决了原方法的缺陷。

内容的提问来源于stack exchange，提问作者Tobi696