sql - 如何根据最大时间间隔进行选择

Question

我在 Postgres SQL 8.4 中有如下表:

 1   | John Smith | 2011-08-12 12:44:13.125+08
 2   | John Smith | 2011-08-16 08:38:57.968+08
 3   | John Smith | 2011-08-16 08:38:58.062+08
 4   | Kenny Long | 2011-08-16 17:06:35.843+08
 5   | Kenny Long | 2011-08-16 17:06:35.906+08
 6   | Kenny Long | 2011-08-16 17:06:59.281+08
 7   | Kenny Long | 2011-08-16 17:07:00.234+08
 8   | Kenny Long | 2011-08-16 17:07:32.859+08
 9   | Kenny Long | 2011-08-16 17:08:00.437+08
 10  | Kenny Long | 2011-08-16 17:08:22.718+08
 11  | Kenny Long | 2011-08-16 17:08:22.781+08

我想选择基于时间戳的列。对于彼此相距 2 分钟以内的那些记录，只需要一行。例如记录号 4 到 9 应该只返回行号 4 并忽略其余行。

我怎样才能做到这一点？非常感谢您的帮助。

提前谢谢你。

乔刘

Answer 1

我用 recursive 试过了方式。我不确定这是更好的方法，而且我很确定我应该研究一些 Window 操作来减少它。

但它适用于我的测试用例。目标是从每个人的最小时间戳开始，然后跟踪要删除的行(在 2 分钟范围内)，以及下一个有效行。然后在每次迭代中，我们从这个有效行开始(每个人一个)。

所以这是对表 myschema.mytable 的查询，其中列有 id、name、tm。请注意，级别列仅用于跟踪递归和调试，不是必需的:

WITH RECURSIVE mytmp(id,name,thetime,thelevel) AS (
   -- recursive query: 1st row
   -- starting point, one row of the table for each people
   -- with a subquery to get the min time with id, maybe a better way to do it
   (
    select myschema.mytable.id,myschema.mytable.name,myschema.mytable.tm as thetime,1 as thelevel
    from (
      select name,min(tm) as mintm
      from myschema.mytable
      group by name
     ) q,myschema.mytable
    WHERE myschema.mytable.name=q.name
    AND myschema.mytable.tm=q.mintm
   ORDER BY name ASC) -- end of starting point of recursive query
  UNION ALL 
    -- now the recursive part, starting from the 1st row and then again and again (loop)
    -- get descendants in the 2 minutes interval for every computed row already in mytmp
    --
    -- get from previous iterations targets, one per guy
    -- and track the 1st new valid row (>2min) for that guy
    -- removing bad rows (<2min) is easy, several way to do it
    -- keeping only one valid row (and not all the others is harder, limit and aggregates functions are restricted in recursive terms
    -- we must keep only one, as the future valid rows will depend on the 2 minutes range from this one
    -- maybe some window function could help me, but at least I've a working solution
    select myschema.mytable.id,myschema.mytable.name,myschema.mytable.tm as thetime,q2.thelevel
    FROM myschema.mytable,(
    -- here need to keep 1st true one
    select myschema.mytable.name,MIN(myschema.mytable.tm) as tm,mytmp2.thelevel +1 as thelevel
    FROM myschema.mytable,(
        select id,name,thetime,thelevel
        from mytmp 
    ) mytmp2
    -- hack: mytmp2 is useless, mytmp should have been used
    -- we create this indirection to avoid this message:
    -- "ERROR:  aggregate functions not allowed in a recursive query's recursive term"
    -- on the MIN functions
    -- I do not know why it worked :-)
    WHERE myschema.mytable.name=mytmp2.name
    -- future
    AND myschema.mytable.tm - mytmp2.thetime > INTERVAL '0'
    GROUP BY
        -- hack the group by, to make 2 groups
        -- the first one for rows in the 2 min range and the second one for others
        CASE WHEN ((myschema.mytable.tm - mytmp2.thetime) > INTERVAL '2 minutes') THEN 1 ELSE 2 END,
        myschema.mytable.name,mytmp2.thelevel,mytmp2.thetime
        -- then with the having we keep only the second group, containing the first valid > 2min row
        HAVING ((MIN(myschema.mytable.tm) - mytmp2.thetime) > INTERVAL '2 minutes')=true
    ) q2 -- q2contains 1st true row and all false rows for each people
    -- q2 is used to get the id, that we cannot have in a group by request
    WHERE q2.tm=myschema.mytable.tm
    AND q2.name=myschema.mytable.name
 ) -- end of recursive query
  SELECT *
  FROM mytmp
  ORDER BY name asc, thelevel asc,thetime asc
  -- LIMIT 100 -- to debug, avoid infinite loops

另一种解决方案可能是使用存储过程，在临时表中执行相同的操作(获取有效行，删除 2 分钟范围内的行，然后获取下一个有效行等)，可能更容易维护。