PostgreSQL计算行之间30秒间隔 筛选X国短时间内多次制作咖啡的设备

原有查询问题说明 #

• 自连接未限制同机器下的时间关联范围，会产生大量无效笛卡尔积，计算效率低且结果易出错
• 时间差计算逻辑依赖浮点数乘法，PostgreSQL中timestamp类型直接相减返回interval类型，用EXTRACT(EPOCH FROM interval)转秒数更可靠
• 未按时间排序做范围/相邻匹配，导致时间差匹配逻辑混乱

通用实现方案（匹配任意30秒窗口≥2杯需求） #

采用滑动窗口统计逻辑，可覆盖补充示例中任意30秒区间内制作量的统计需求：

WITH qualified_records AS (
    -- 先过滤X国近2天的基础数据，关联配方表
    SELECT 
        c.index,
        c.machineid,
        c.utc_timestamp,
        c.capsule_productid AS "Capsule ID",
        -- 按机器分组，按时间排序，统计当前行往前30秒内的总制作次数
        COUNT(*) OVER (
            PARTITION BY c.machineid 
            ORDER BY EXTRACT(EPOCH FROM c.utc_timestamp)
            RANGE BETWEEN 30 PRECEDING AND CURRENT ROW
        ) AS cnt_30s
    FROM brand.consumption c
    INNER JOIN brand.recipecode_list r
        ON c.recipeid = r.recipeid
        AND c.capsule_productid = r.productid
        AND c.capsule_recipetypeid = r.recipetype
    WHERE c.countryid = 'X'
        AND c.rcv_timestamp >= CURRENT_DATE - INTERVAL '2 days' -- 保留原有近2天过滤规则
)
-- 筛选出30秒内制作≥2杯的记录
SELECT index, machineid, utc_timestamp AS "Timestamp", "Capsule ID"
FROM qualified_records
WHERE cnt_30s >= 2
ORDER BY machineid, utc_timestamp;

逻辑说明 #

1. CTE层先完成数据过滤和表关联，避免多层子查询嵌套
2. 窗口函数PARTITION BY c.machineid保证统计范围仅限同一台机器
3. 按时间戳的epoch值排序，范围窗口取当前行往前30秒的所有记录，计数结果就是该时间点往前30秒内的总制作量
4. 最后过滤计数≥2的记录，即可得到所有满足「30秒内至少制作2杯」的请求记录
   如果仅需要输出满足条件的机器列表，不需要明细，将最后一步查询修改为：

SELECT DISTINCT machineid
FROM qualified_records
WHERE cnt_30s >= 2;

相邻行间隔匹配方案（匹配首个示例输出需求） #

如果仅需要筛选相邻两条制作记录间隔≤30秒的记录，可采用相邻行偏移函数实现：

WITH qualified_records AS (
    SELECT 
        c.index,
        c.machineid,
        c.utc_timestamp,
        c.capsule_productid AS "Capsule ID",
        -- 取同机器下上一条制作记录的时间
        LAG(c.utc_timestamp) OVER (PARTITION BY c.machineid ORDER BY c.utc_timestamp) AS prev_time
    FROM brand.consumption c
    INNER JOIN brand.recipecode_list r
        ON c.recipeid = r.recipeid
        AND c.capsule_productid = r.productid
        AND c.capsule_recipetypeid = r.recipetype
    WHERE c.countryid = 'X'
        AND c.rcv_timestamp >= CURRENT_DATE - INTERVAL '2 days'
)
SELECT index, machineid, utc_timestamp AS "Timestamp", "Capsule ID"
FROM qualified_records
WHERE EXTRACT(EPOCH FROM (utc_timestamp - prev_time)) <= 30
ORDER BY machineid, utc_timestamp;

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