Postgres query for recursive operation on rows

Question

create table foo (a int, b float);

insert into foo values (1, 2), (2,3),(3,2.5),(4,1.5);

 a |  b  
---+-----
 1 |   2
 2 |   3
 3 | 2.5
 4 | 1.5

I want to calculate the difference of each b for any a

select RECURSIVE diff (?,?) on b where a=1

OUTPUT:

 a |  diff  
---+-----
 1 |   0
 2 |   1
 3 | .5
 4 | -.5

Is it possible to recursively apply a function over all of the table rows?

How can you have multiple values for a in the expected output when you use where a = 1 in the SQL query? — user330315
– user330315, Commented Apr 13, 2017 at 14:53

Clodoaldo Neto · Accepted Answer · 2017-04-13 18:13:32Z

3

select b - b0
from
    foo
    cross join
    (select b as b0 from foo where a = 1) s
;
 ?column? 
----------
        0
        1
      0.5
     -0.5

Due to the thousands of requests in the comments this is the elegant version:

select f1.b - f2.b
from
    foo f1
    cross join
    foo f2
where f2.a = 1

edited Apr 13, 2017 at 18:13

answered Apr 13, 2017 at 15:36

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Evan Carroll Over a year ago

As a matter of pure style, I think this looks a ton better with sane white space and no virtual table: SELECT f1.b - f2.b FROM foo AS f2 CROSS JOIN foo AS f2 WHERE f2.a = 1

Marth · Accepted Answer · 2017-04-13 15:02:06Z

2

You can use the LAG window function to get the diff between to rows:

SELECT b - LAG(b) OVER (ORDER BY a ASC) 
FROM foo;
┌──────────┐
│ ?column? │
├──────────┤
│   (null) │
│        1 │
│     -0.5 │
│       -1 │
└──────────┘
(4 rows)

You can then SUM that:

SELECT SUM(d) OVER (ORDER BY a ASC)
FROM (
    SELECT a, b - LAG(b) OVER (ORDER BY a ASC)
    FROM foo
) sub(a, d);
┌────────┐
│  sum   │
├────────┤
│ (null) │
│      1 │
│    0.5 │
│   -0.5 │
└────────┘
(4 rows)

answered Apr 13, 2017 at 15:02

Marth

24.9k3 gold badges68 silver badges76 bronze badges

Collectives™ on Stack Overflow

Postgres query for recursive operation on rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related