在R或PostgreSQL中形成时空接近轨迹的组

>群集存储在数组中:(我相信群集不会变得太大)
>该数组由递归查询构建：每对与当前集群共有一个成员的自行车将合并到其中.
>最后,数组包含碰巧在特定时间触手可及的所有bike_id.
>(加上一些需要稍后被uniq CTE压制的中间行)
>其余部分是时间序列中或多或少标准的间隙检测.

注意：代码信任(bike2> bike1).这需要保持数组排序,从而规范.实际内容不保证是规范的,因为无法保证递归查询中的添加顺序.这可能需要一些额外的工作.

CREATE TABLE nearness
        ( bike1 INTEGER NOT NULL,FALSE)    -- <<-- these False-records serve no pupose,FALSE)    -- <<-- result would be the same without them,FALSE)
        ;


        -- Recursive union-find to glue together sets of bike_ids
        --,occuring at the same moment.
        -- Sets are represented as {ordered,unique} arrays here
WITH RECURSIVE wood AS (
        WITH omg AS (
                SELECT bike1,row_number() OVER(ORDER BY bike1,stamp) AS seq,ARRAY[bike1,bike2]::integer[] AS arr
                FROM nearness n WHERE near = True
                )
        -- Find all existing combinations of bikes
        SELECT o1.stamp,o1.seq,ARRAY[o1.bike1,o1.bike2]::integer[] AS arr
        FROM omg o1
        UNION ALL
        SELECT o2.stamp,o2.seq -- avoid duplicates inside the array,CASE when o2.bike1 = ANY(w.arr) THEN w.arr || o2.bike2
                ELSE  w.arr || o2.bike1 END AS arr
        FROM omg o2
        JOIN wood w
                ON o2.stamp = w.stamp AND o2.seq > w.seq
                AND (o2.bike1 = ANY(w.arr) OR o2.bike2 = ANY(w.arr))
                AND NOT (o2.bike1 = ANY(w.arr) AND o2.bike2 = ANY(w.arr))
        ),uniq  AS (    -- suppress partial sets caused by the recursive union-find buildup
        SELECT * FROM wood w
        WHERE NOT EXISTS (SELECT * FROM wood nx
                WHERE nx.stamp = w.stamp
                AND nx.arr @> w.arr AND nx.arr <> w.arr -- contains but not equal 
                )
        ),xsets AS (    -- make unique sets of bikes
        SELECT DISTINCT arr
        --,MIN(seq) AS grp
        FROM uniq
        GROUP BY arr
        ),sets AS (     -- enumerate the sets of bikes
        SELECT arr,row_number() OVER () AS setnum
        FROM xsets
        ),drag AS (             -- Detect beginning and end of segments of consecutive observations
        SELECT u.*      -- within a constant set of bike_ids
        -- Edge-detection begin of group,NOT EXISTS (SELECT * FROM uniq nx
                WHERE nx.arr = u.arr
                AND nx.stamp < u.stamp
                AND nx.stamp >= u.stamp - '5 sec'::interval
                ) AS is_first
        -- Edge-detection end of group,NOT EXISTS (SELECT * FROM uniq nx
                WHERE nx.arr = u.arr
                AND nx.stamp > u.stamp
                AND nx.stamp <= u.stamp + '5 sec'::interval
                ) AS is_last,row_number() OVER(ORDER BY arr,stamp) AS nseq
        FROM uniq u
        ),top AS ( -- id and groupnum for the start of a group
        SELECT nseq,row_number() OVER () AS clust
        FROM drag
        WHERE is_first
        ),bot AS ( -- id and groupnum for the end of a group
        SELECT nseq,row_number() OVER () AS clust
        FROM drag
        WHERE is_last
        )
SELECT w.seq as orgseq  -- results,please ...,w.stamp,g0.clust AS clust,row_number() OVER(www) AS rn,s.setnum,s.arr AS bike_set
        FROM drag w
        JOIN sets s ON s.arr = w.arr
        JOIN top g0 ON g0.nseq <= w.seq
        JOIN bot g1 ON g1.nseq >= w.seq AND g1.clust = g0.clust
        WINDOW www AS (PARTITION BY g1.clust ORDER BY w.stamp)
        ORDER BY g1.clust,w.stamp
        ;

结果：

orgseq |        stamp        | clust | rn | setnum | bike_set 
--------+---------------------+-------+----+--------+----------
      1 | 2016-05-28 11:00:00 |     1 |  1 |      1 | {1,2}
      4 | 2016-05-28 11:00:20 |     3 |  1 |      1 | {1,2}
      5 | 2016-05-28 11:00:25 |     3 |  2 |      1 | {1,2}
      6 | 2016-05-28 11:00:05 |     4 |  1 |      3 | {1,3}
      7 | 2016-05-28 11:00:10 |     4 |  2 |      3 | {1,3}
      8 | 2016-05-28 11:00:10 |     4 |  3 |      2 | {4,5}
(6 rows)