sql - 基于一列将点分组到线串中，但前提是顺序遵循另一列的顺序

Question

我有一个结构如下的表:

datetime            | the_geom                   | m_status
2017-01-01T00:00:00 | POINT(13.048133,57.798882) | status_1
2017-01-01T00:00:01 | POINT(14.048133,56.798882) | status_1
2017-01-01T00:00:02 | POINT(15.048133,55.798882) | status_1
2017-01-01T00:00:04 | POINT(17.048133,53.798882) | status_2
2017-01-01T00:00:05 | POINT(18.048133,52.798882) | status_2
2017-01-01T00:00:06 | POINT(19.048133,51.798882) | status_2
2017-01-01T00:00:07 | POINT(20.048133,50.798882) | status_1
2017-01-01T00:00:08 | POINT(21.048133,49.798882) | status_1
2017-01-01T00:00:09 | POINT(22.048133,48.798882) | status_3
2017-01-01T00:00:10 | POINT(23.048133,47.798882) | status_3

我想按 m_status 将顺序点分组到单独的线串中，但前提是它们在日期时间列之后按顺序排列。

感谢任何建设性的帮助。

PostgreSQL 版本:PostgreSQL 9.5.2 on x86_64-pc-linux-gnu，由 gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3 编译，64 位

编辑 1:

预期输出:

id | the_geom                                                                  | m_status
1  | LINESTRING(13.048133 57.798882, 14.048133 56.798882, 15.048133 55.798882) | status_1
2  | LINESTRING(17.048133 53.798882, 18.048133 52.798882, 19.048133 51.798882) | status_2
3  | LINESTRING(20.048133 50.798882, 21.048133 49.798882)                      | status_1
4  | LINESTRING(22.048133 48.798882, 23.048133 47.798882)                      | status_3

编辑 2:我不需要每个线串的开始和结束日期，但是我需要某种 ID 来了解它们的时间顺序(请参阅上面预期输出中的 ID)。

Answer 1

您可以使用不同的行号方法对组进行分类(运行内部查询以查看组分配)，然后使用 ST_MakeLine 将所有这些点放在一行中。

select ST_MakeLine(the_geom order by datetime) as the_geom, max(m_status) as m_status
from (select t.*,
      row_number() over(order by datetime)
      -row_number() over(partition by m_status order by datetime) as grp
      from tbl t
     ) t
group by grp

编辑:根据 OP 在输出中包含行号的编辑，获取先前确定的每个组的最短或最长时间，并将其用于排序。

SELECT row_number() over(ORDER BY min_time) AS id,
       ST_MakeLine(the_geom ORDER BY datetime) AS the_geom,
       max(m_status) AS m_status
FROM
  (SELECT t.*,
           min(datetime) over(partition BY grp,m_status) AS min_time
   FROM
     (SELECT t.*,
             row_number() over(ORDER BY datetime) -row_number() over(partition BY m_status ORDER BY datetime) AS grp
      FROM tbl t
   ) t 
) t
GROUP BY grp,min_time