sql - postgreSQL:试图过滤掉显示相同位置但时间戳略有不同的重复行？

Question

我使用的数据集包含字段“virtual_time”和“store_visited”，数据显示用户在不同时间戳不同位置的事件模式。

问题是有时用户可能在同一个位置，但在数据集上更新了同一个地方的多个不同记录，时间戳略有不同。

我想我想将每个位置的那些较小的时间戳组合在一起，这样数据对我来说更有意义，我以后可以区分用户在每个地方花费了多少时间。

例如当我输入:

SELECT DISTINCT virtual_time, store_visited
FROM public.consumer
WHERE user = 'e63a9'
ORDER BY 1;

我得到类似的东西:

    Store_visited   virtual_time
  1 M&S          2017-09-16 17:52:06
  2 WholeFoods   2017-09-16 18:26:17
  3 WholeFoods   2017-09-16 18:26:19
  4 WholeFoods   2017-09-16 18:26:20
  5 OysterRooms  2017-09-18 13:31:39

但我想过滤掉第 3,4 行访问过的重复商店，因为它们显示相同的位置，只显示大约 2 秒和 1 秒的时间差。

理想情况下过滤它会显示如下内容:

    Store_visited   virtual_time
   1 M&S          2017-09-16 17:52:06
   2 WholeFoods   2017-09-16 18:26:17
   5 OysterRooms  2017-09-18 13:31:39

这样更容易区分不同商店的不同时间戳。

希望这是有道理的。任何帮助将不胜感激!

如果您有任何问题，请告诉我!

非常感谢

Answer 1

您可以将时间戳四舍五入为分钟:

select distinct store_visited, date_trunc('minute', virtual_time) as virtual_time
from consumer
order by 2;  

这是最快但不是很准确的解决方案。更好的方法是检查连续行之间的差异并跳过那些落在特定范围内的行。使用 window function lag():

select store_visited, virtual_time
from (
    select 
        store_visited, virtual_time, 
        coalesce(virtual_time- lag(virtual_time) over w < '10 seconds', false) as neglible
    from consumer
    window w as (partition by store_visited order by virtual_time)
    ) s
where not neglible
order by 2;  

 store_visited |    virtual_time     
---------------+---------------------
 M&S           | 2017-09-16 17:52:06
 WholeFoods    | 2017-09-16 18:26:17
 OysterRooms   | 2017-09-18 13:31:39
(3 rows)