Requête SQL pour la somme accumulée en utilisant la fonction fenêtre dans postgresql

Question

J'ai mis en place une table assez simple, représentant des points dans un environnement 2D. La colonne Id est l'id de chaque point et la colonne geom est une représentation binaire du point dans l'espace :

Tableau public.foo

 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)

Pour trouver la distance de chaque point au suivant, j'utilise la fonction fenêtre suivante :

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

ce qui donne le résultat suivant ( st_distance(geom,geom) donne la distance entre deux types de données geom) :

 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..

Mon objectif est de trouver la distance cumulée de chaque point au suivant depuis le début pour chaque nœud. Par exemple, comme ce tableau fictif ci-dessous :

 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..

J'ai essayé de combiner les fonctions sum et lag mais postgresql dit que les fonctions Windows ne peuvent pas être imbriquées. Je suis complètement désorienté sur la façon de procéder. Quelqu'un peut-il m'aider ?

Answer 1

Puisque vous ne pouvez pas avoir une fonction fenêtre sur une autre fonction fenêtre ("ne peut pas être imbriquée"), vous devez ajouter une couche de sous-requête (ou un 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;

Cela suppose que id est unique - ce qui est garanti par votre clé primaire.