WHERE子句用于不选择带时间戳的行50ms的任何一侧？

Question

我有一个表的部分是这样的：

timestamp     | Source 
----------------------------+---------- 
2017-07-28 14:20:28.757464 | Stream 
2017-07-28 14:20:28.775248 | Poll 
2017-07-28 14:20:29.777678 | Poll 
2017-07-28 14:21:28.582532 | Stream 

我要实现这一点：

timestamp     | Source 
----------------------------+---------- 
2017-07-28 14:20:28.757464 | Stream 
2017-07-28 14:20:29.777678 | Poll 
2017-07-28 14:21:28.582532 | Stream 

凡第2行中的原始表已被删除，因为这是内为50ms它之前或之后的时间戳。重要的是，只有当Source ='Poll'时才会删除行。

不知道如何用WHERE子句实现这个可能吗？

在此先感谢您的帮助。

Answer 1

无论我们做什么，我们都可以将其限制为Pools，然后将这些行与Streams结合。

with 
streams as (
select * 
from test 
where Source = 'Stream' 
), 
pools as (
    ... 
) 

(select * from pools) union (select * from streams) order by timestamp 

要获得池，有不同的选择：

相关子查询

对于每一个我们运行额外的查询具有相同源得到上一行一行，然后选择只有那些行那里没有以前的时间戳（第一行）或以前的时间戳超过50ms。

with 
... 
pools_with_prev as (
    -- use correlated subquery 
    select 
    timestamp, Source, 
    timestamp - interval '00:00:00.05' 
     as timestamp_prev_limit, 
    (select max(t2.timestamp)from test as t2 
     where t2.timestamp < test.timestamp and 
    t2.Source = test.Source) 
     as timestamp_prev 
    from test 
), 
pools as (
    select timestamp, Source 
    from pools_with_prev 
    -- then select rows which are >50ms apart 
    where timestamp_prev is NULL or 
    timestamp_prev < timestamp_prev_limit 
) 

... 

https://www.db-fiddle.com/f/iVgSkvTVpqjNZ5F5RZVSd2/2

加入两个滑动表

而是为每一行运行子查询，我们就可以创造我们的表的副本并滑动所以每个池一行的前一行加入相同的源类型。

with 
... 
pools_rn as (
-- add extra row number column 
-- rows: 1, 2, 3 
select *, 
    row_number() over (order by timestamp) as rn 
from test 
where Source = 'Pool' 
), 
pools_rn_prev as (
-- add extra row number column increased by one 
-- like sliding a copy of the table one row down 
-- rows: 2, 3, 4 
select timestamp as timestamp_prev, 
    row_number() over (order by timestamp)+1 as rn 
from test 
where Source = 'Pool' 
), 
pools as (
-- now join prev two tables on this column 
-- each row will join with its predecessor 
select timestamp, source 
from pools_rn 
    left outer join pools_rn_prev 
    on pools_rn.rn = pools_rn_prev.rn 
where 
    -- then select rows which are >50ms apart 
    timestamp_prev is null or 
    timestamp - interval '00:00:00.05' > timestamp_prev 
) 

... 

https://www.db-fiddle.com/f/gXmSxbqkrxpvksE8Q4ogEU/2

滑动窗口

现代SQL可以通过源做类似的事情，使用分区，然后使用滑动窗口与前行加入。

with 
... 
pools_with_prev as (
    -- use sliding window to join prev timestamp 
    select *, 
    timestamp - interval '00:00:00.05' 
     as timestamp_prev_limit, 
    lag(timestamp) over(
     partition by Source order by timestamp 
    ) as timestamp_prev 
    from test 
), 
pools as (
    select timestamp, Source 
    from pools_with_prev 
    -- then select rows which are >50ms apart 
    where timestamp_prev is NULL or 
    timestamp_prev < timestamp_prev_limit 
) 


... 

https://www.db-fiddle.com/f/8KfTyqRBU62SFSoiZfpu6Q/1

我相信这是最优化的。