SQL query for accumulated amount using window function in postgresql

Question

I created a fairly simple table representing the points in a 2D environment. The Id column is the identifier of each point, and the geom column is a binary representation of the point in space:

public.foo table

  Column | Type | Modifiers --------+----------------------+-------------------------------------------- id | integer | not null default nextval('mseq'::regclass) geom | geometry(Point,2100) | 

Indices:

  "foo_pkey" PRIMARY KEY, btree (id) "foo_index_gist_geom" gist (geom) 

To find the distance from each point to the next, I use this window function:

 select id, st_distance(geom,lag(geom,1) over (order by id asc)) distance from foo; 

which leads to the following (st_distance (geom, geom) gives the distance between two types of geometry data):

  id | distance ----+------------------ 1 | 2 | 27746.1563439608 3 | 57361.8216245281 4 | 34563.3607734946 5 | 23421.2022073633 6 | 41367.8247514439 .... distance(1) -> null since its the first point distance(2) -> ~28km from point 1 to point 2 distance(3) -> ~57km from point 2 to point 3 and etc.. 

My goal is to find the cumulative distance from each point to the next from the beginning for each node. for example, like this breadboard below:

  id | distance | acc ----+------------------+----- 1 | | 2 | 27746.1563439608 | 27746.1563439608 3 | 57361.8216245281 | 85107.97797 4 | 34563.3607734946 | 119671.33874 where acc(1) is null because it is the first node, acc(2) = acc(1) + dist(2) acc(3) = acc(2) + dist(3) and etc.. 

I tried combining the sum and lag functions, but postgresql says that windows functions cannot be nested. I am completely puzzled by how to proceed. Who can help me?

Answer 1

Since you cannot have a window function over another window function ("cannot be nested"), you need to add a subquery level (or CTE):

 SELECT id, sum(distance) OVER (ORDER BY id) AS cum_dist FROM ( SELECT id, st_distance(geom, lag(geom, 1) OVER (ORDER BY id)) AS distance FROM foo ) sub ORDER BY id; 

This assumes the id is unique, which is guaranteed by your primary key.