postgresql - 如何根据多列的顺序对 PostgreSQL 中的聚合进行分组？

Question

给定一个表“点”，如:

time  | session_id  | trail_id
------------------------------
  1   |      1      |    1
  2   |      1      |    1
  3   |      1      |    3
  4   |      1      |    3
  5   |      1      |    3
  6   |      1      |    1
  7   |      1      |    1
  8   |      1      |    1
  9   |      1      |    1
  10  |      1      |    1

如何对这些项目进行分组，以便我可以在“session_id”上使用关于 trail_id 的聚合函数，同时按时间排序？即)我希望在 trail_id 随时间变化时拆分分组。

这样的查询:

SELECT count(session_id), session_id, trail_id
FROM <?>

会产生:

count  | session_id  | trail_id
-------------------------------
   2   |     1       |    1
   3   |     1       |    3
   5   |     1       |    1

我相信这可以用窗口函数来完成，但到目前为止还没有成功。

以下内容并没有完全到达我需要的位置，因为它对所有 trail_ids 进行了分组，而不考虑时间:

SELECT session_id, trail_id, 
  first_value(time) OVER (PARTITION BY session_id, trail_id ORDER BY time) as v
FROM points

---

此外，在我的生产用例中，“点”表将是 JOIN 的结果，由几百万行组成。这些点将具有 PostGIS 几何类型，并使用 ST_MakeLine() 函数进行聚合。在性能方面，在 PL/pgSQL 中尝试会更好吗？

Answer 1

with points(time  , session_id  , trail_id) as(
    select 1   ,      1      ,    1 union all
    select  2   ,      1      ,    1 union all
    select  3   ,      1      ,    3 union all
    select  4   ,      1      ,    3 union all
    select  5   ,      1      ,    3 union all
    select  6   ,      1      ,    1 union all
    select  7   ,      1      ,    1 union all
    select  8   ,      1      ,    1 union all
    select  11   ,      1      ,    1 union all
    select  12  ,      1      ,    1  
)

select count(*), session_id, trail_id
from (
    select time, session_id, trail_id,
    row_number() over(order by time) -
    row_number() over(partition by session_id, trail_id order by time) as grp
    from points
)t
group by grp, session_id, trail_id
order by min(time)

好吧，这应该会给出你需要的结果，但是如果

'points' table will be the result of JOINs and consist of a few millions rows

那么性能可能不会那么理想。试试看