Postgres select if another event exists before and after a time range

问题: I have a table like this: I need a select the following records: All category A Category B only if before and after 20 seconds a category A exists for the sam...

问题:

I have a table like this:

enter image description here

I need a select the following records:

  • All category A
  • Category B only if before and after 20 seconds a category A exists for the same name

To create a test table:

     CREATE TABLE test(
       time TIMESTAMP,
       name CHAR(10),
       category CHAR(50)
    );

INSERT INTO test (time, name, category)
    VALUES ('2019-02-25 18:30:10', 'john', 'A'),
           ('2019-02-25 18:30:15', 'john', 'B'),
           ('2019-02-25 19:00:00', 'phil', 'A'),
           ('2019-02-25 20:00:00', 'tim', 'A'),
           ('2019-02-25 21:00:00', 'tim', 'B'),
           ('2019-02-25 21:00:00', 'frank', 'B');

So from the above, this is the desired output:

enter image description here


回答1:

You can use an exists subquery to determine if there is an A row within 20 seconds:

select  *
from    test t1
where   category = 'A'
        or exists
        (
        select  *
        from    test t2
        where   t2.category = 'A'
                and t2.name = t1.name
                and abs(extract(epoch from t2.time - t1.time)) < 20
        )

回答2:

You can use exists. But you can also use window functions:

select t.*
from (select t.*,
             max(t.time) filter (t.category = 'A') over (partition by name order by time) as prev_a,
             min(t.time) filter (t.category = 'A') over (partition by name order by time desc) as next_a
      from test t
     ) t
where category = 'A' or
      (category = 'B' and
       (prev_a > time - interval '20 second' or
        next_a < time + interval '20 second'
       )
      );
  • 发表于 2019-03-02 05:25
  • 阅读 ( 168 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除