Блог

Допустим, у нас есть набор данных временных рядов метаданных объектов, импортированных в таблицу postgres Stats :

 CREATE EXTENSION IF NOT EXISTS POSTGIS;
DROP TABLE IF EXISTS "Stats";
CREATE TABLE IF NOT EXISTS "Stats"
(
    "time" BIGINT,
    "id" BIGINT,
    "position" GEOGRAPHY(PointZ, 4326)
);
 

И вот примеры таблицы:

 SELECT 
    "id",
    "time"
FROM
    "Stats"
ORDER BY 
    "id", "time" ASC

id|time|
-- ---- 
 1|   3|
 1|   4|
 1|   6|
 1|   7|
 2|   2|
 2|   6|
 3|  14|
 4|   2|
 4|   9|
 4|  10|
 4|  11|
 5|  32|
 6|  15|
 7|  16|
 

Бизнес-требование состоит в том, чтобы назначить route-id объектам в этой таблице, поэтому, когда время для каждого объекта превышает 1 second его, это означает новый рейс или маршрут для этого объекта. конечный результат будет похож на это для предыдущих образцов:

 id|time|route_id|
-- ---- -------- 
 1|   3|       1|
 1|   4|       1|
 1|   6|       2|
 1|   7|       2|
 2|   2|       1|
 2|   6|       2|
 3|  14|       1|
 4|   2|       1|
 4|   9|       2|
 4|  10|       2|
 4|  11|       2|
 5|  32|       1|
 6|  15|       1|
 7|  16|       1|
 

И это будет новая сводная таблица маршрутов:

 id|start_time|end_time|route_id|
-- ---------- -------- -------- 
 1|         3|       4|       1|
 1|         6|       7|       2|
 2|         2|       2|       1|
 2|         6|       6|       2|
 3|        14|      14|       1|
 4|         2|       2|       1|
 4|         9|      11|       2|
 5|        32|      32|       1|
 6|        15|      15|       1|
 7|        16|      16|       1|
 

Итак, как должен быть построен этот сложный запрос?

 with data as (
    select *, row_number() over (partition by id order by "time") rn from Stats
)
select id,
    min("time") as start_time, max("time") as end_time,
    row_number() over (partition by id order by "time" - rn) as route_id
from data
group by id, "time" - rn
order by id, "time" - rn
 

https://dbfiddle.uk/?rdbms=postgres_9.5amp;fiddle=c272bc57786487b0b664648139530ae4

Предполагая, что у вас есть таблица stats , следующий запрос создаст таблицу, назначив route_id :

Запрос для назначения route_id с помощью recursive-cte :

 CREATE TABLE tbl_route AS 
with recursive cte AS 
(
  SELECT id,  prev_time, time, rn, rn AS ref_rn, rn AS route_id 
  FROM 
  (    
    SELECT 
      *,
      lag(time) OVER(partition BY id ORDER BY time) AS prev_time,
      row_number() OVER(partition BY id ORDER BY time) AS rn 
    FROM stats
  ) AS rnt
  WHERE rn=1

  UNION

  SELECT rnt2.id, rnt2.prev_time, rnt2.time, rnt2.rn, cte.rn AS ref_rn,
    CASE 
      WHEN abs(rnt2.time-rnt2.prev_time)<=1 THEN cte.route_id
      ELSE cte.route_id 1
    END AS route_id
  FROM cte
  INNER JOIN
  (
   SELECT 
     *,
     lag(time) OVER(partition BY id ORDER BY time) AS prev_time,
     row_number() OVER(partition BY id ORDER BY time) AS rn 
   FROM stats
  ) AS rnt2
   ON cte.id=rnt2.id AND cte.rn 1 = rnt2.rn
)

SELECT id, time, route_id FROM cte;
 

Запрос для проверки правильности присвоения route_id:

 select id, time, route_id 
from tbl_route 
order by id, time
 

Запрос для создания new summary таблицы:

 select id, min(time) as start_time, max(time) as end_time, route_id
from tbl_route
group by id, route_id
order by id, route_id, start_time, end_time
 

Рекурсивный -разбивка запросов CTE:

Поскольку использовался рекурсивный cte, запрос может выглядеть беспорядочно. Тем не менее, я попытался разбить его следующим образом:

1. Есть 2 основных запроса, которые добавляются с использованием UNION , первый будет назначен route_id для начала каждого идентификатора, второй сделает это для остальных строк для каждого идентификатора
2. rnt и rnt2 был создан, потому что нам нужны ROW_NUMBER LAG значения и для достижения этой цели
3. Мы объединили cte и rnt2 recursively для назначения route_id , проверив разницу во времени

ДЕМОНСТРАЦИЯ