PostgreSQL: Joining with Window Function / Partial Table

Question

Issue

I'm working a project with kernels in databases, and my PostgreSQL skills have hit a wall. I am joining between two tables to compute the cross product, i.e.

SELECT (d1.a * d2.a + d1.b * d2.b) AS dot FROM data d1, data d2

This gives me the cross product between all vectors. Having the following data in my table

 a | b | c 
---+---+---
 1 | 1 | 1
 2 | 2 | 2
 3 | 3 | 3

The above command yields

 dot 
-----
   2
   4
   6
 ...

If I want to compute the dot product between, say, row 2 with its preceding row and its following row, how would I do that efficiently?

Attempts

I have tried to use window functions, but failed to do so since they only compute aggregate functions. I want to join a row with its neighbouring rows (i.e. its window), but not compute any aggregate over these. Something along these lines:

SELECT a * a + b * b + c * c
    OVER(rows between 1 preceding and 1 following) as value FROM data data;

I have also tried to use row_number() OVER() which works. But this seems clumsy and inefficient with nested subqueries.

SELECT d1.a * d3.a + d1.b * d3.b + d1.c * d3.c 
    FROM data d1, 
    (SELECT * FROM 
        (SELECT *, row_number() OVER() as index from data) d2 
    WHERE d2.index >= 1 AND d2.index <=3) d3;

Lastly, I tried to dig into LATERALs with no luck.

Any thoughts?

Answer 1

You can get the values of preceding/following rows by lag()/lead().
If the order of rows is determined by a, the query would be like:

SELECT
  a,
  (lag(a, 1, 0) OVER (ORDER BY a)) * (lead(a, 1, 0) OVER (ORDER BY a))
  + (lag(b, 1, 0) OVER (ORDER BY a)) * (lead(b, 1, 0) OVER (ORDER BY a))
  + (lag(c, 1, 0) OVER (ORDER BY a)) * (lead(c, 1, 0) OVER (ORDER BY a)) AS dot_preceding_and_following
FROM ( VALUES
  (1, 1, 1),
  (2, 2, 2),
  (3, 3, 3)
) T(a, b, c)
ORDER BY
  a
;