如何根据多列的排序对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) 

那么，这应该给结果你所需要的，但如果

“点”表会的结果联接和由几百万行

那么可能的性能不会那么理想。试试吧