sql - PostgreSQL 中单元格之间的 30 秒间隔

Question

我已经尝试了几天在 PostgreeSQL 中运行查询，我需要收集以下数据:“机器 ID、时间戳、胶囊 ID”。
每行都有一个索引(主键)并对应于一个请求，我必须从 X 国家获取在不到半分钟内至少制作 2 杯咖啡的机器列表。
我无法计算一行与另一行之间的时差。例如

 index        machineID           TimeStamp            Capsule ID
    1               A1            2021-08-02 14:18:00         6
    2               A1            2021-08-02 14:18:25         6
    3               A1            2021-08-02 14:18:31         6

我只需要显示索引 1 和 2，因为它们在 30 秒的间隔内，但我不能这样做。
我正在尝试使用此代码

select brand.consumption.index, brand.consumption.recipeid, brand.consumption.machineid,  brand.consumption.rcv_timestamp, brand.consumption.volume_brewed, brand.recipecode_list.cupsizeid, brand.consumption.capsule_productid

from brand.consumption, brand.recipecode_list

where brand.consumption.recipeid = brand.recipecode_list.recipeid
and brand.consumption.capsule_productid = brand.recipecode_list.productid
and brand.consumption.capsule_recipetypeid = brand.recipecode_list.recipetype
and brand.consumption.index 
in 
(SELECT distinct tt1
  
FROM (
SELECT t1.index tt1,
               t2.index tt2,
               t1.machineid,
               (t2.utc_timestamp - t1.utc_timestamp) * (60 * 60 * 24)  result 
          
FROM brand.consumption t1
          
LEFT JOIN brand.consumption t2
           
ON (t1.machineid = t2.machineid)
         
where t2.rcv_timestamp >= sysdate -2) t1

where tt1 != tt2 and result between -30 and  30)

and brand.consumption.countryid = 'X'

order by brand.consumption.machineid, brand.consumption.utc_timestamp

我有一个数据库，其中有几台生产咖啡的机器，我必须过滤 X 国的哪些机器在不到 30 秒的时间内生产了 2 种以上的咖啡。
例如:

Machine A 
ID 1 - coffee at 19:00:00 
ID 2 - coffee at 19:00:05
ID 3 - coffee at 19:00:18 
ID 4 - coffee at 19:00:28 
ID 5 - coffee at 19:00:31 

Machine B 
ID 6 - coffee at 19:00:08 
ID 7 - coffee at 19:00:22 
ID 8 - coffee at 19:00:29 
ID 9 - coffee at 19:00:32 
ID 10 - coffee at 19:00:38 
ID 11 - coffee at 19:00:40 

预期结果: 机器 A:30 秒内 4 杯咖啡 机器 B:30 秒内 6 杯咖啡，因为从第 32 秒到第 40 秒，它在 30 秒内生产了 30 多杯咖啡。

Answer 1

如果您正在寻找一种仅显示与给定 machine_id 的第一条记录相距不到 30 秒的记录的方法。和 capsule_id您可以使用窗口函数FIRST_VALUE .像这样的东西:

WITH j AS (
  SELECT *,utc_timestamp-FIRST_VALUE(utc_timestamp) OVER w AS calctime
  FROM t
  WINDOW w AS (PARTITION BY machine_id,capsule_id ORDER BY utc_timestamp
               RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
)
SELECT * FROM j
WHERE calctime <= interval '30 seconds'

演示: db<>fiddle