Calculate distance between two consecutive points within the same group in PostgreSQL

Question

I have a table with Point geometries representing GPS data of participants. I am calculating the distance between two consecutive points:

SELECT id, participant, ST_Distance(geom, lag(geom, 1) OVER (ORDER by id ASC))
FROM table;

but I would like to do the calculation separately for each participant, so instead of:

id;participant;distance
1;1;NULL
2;1;dist(1,2)
3;1;dist(2,3)
4;2;dist(3,4)
5;2;dist(4,5)
6;2;dist(5,6)

I would like to get

id;participant;distance
1;1;NULL
2;1;dist(1,2)
3;1;dist(2,3)
4;2;NULL
5;2;dist(4,5)
6;2;dist(5,6)

Answer 1

You can use partition by in your window:

SELECT 
  id, 
  participant, 
  ST_Distance(geom, lag(geom, 1) OVER (PARTITION BY participant ORDER by id ASC))
FROM table;