CODEWITHSUNDEEP
sqlpostgresql
PtF
PtF

Reputation: 151

Row of the first positive number preceding a negative number in Postgres?

I have a table like:

enter image description here

I'm doing some computations and in some point when I get a negative number in column 2 I must find the first positive number preceding this negative number. For instance, if the negative number were -2.2 I would like to have access to 1.2, if it were -0.8 then I would like to have access to 1.5.

How can I do that?

Thanks.

Upvotes: 1

Views: 1165

Answers (1)

Jeremy
Jeremy

Reputation: 6723

You can use a combination of window functions to do this. First, use count as a window function, incrementing the count only when column2>0. This will segregate your rows into the groups you'll need to pick out the positive value. We can then use first_value to grab the first row from the partition, which will be the positive value:

create table test (column1 int, column2 numeric, column3 date);
insert into test VALUES (30, 2.5, '2019-01-01'), (31, 1.5, '2019-01-02'), (28, -0.8, '2019-01-03'), (29, 1.0, '2019-01-04'), (30, 1.2, '2019-01-05'), (38, -2.1, '2019-01-06'), (37, -2.2, '2019-01-07');


SELECT column1,
       column2,
       first_value(column2) OVER (PARTITION BY col2_group ORDER BY column3)
  FROM (
  select column1, 
         column2, 
         -- Create groups of rows
         count(column2) FILTER (WHERE column2>0) OVER(ORDER BY column3) as col2_group, 
         column3
  FROM test) as sub
ORDER BY column3
;
 column1 | column2 | first_value
---------+---------+-------------
      30 |     2.5 |         2.5
      31 |     1.5 |         1.5
      28 |    -0.8 |         1.5
      29 |     1.0 |         1.0
      30 |     1.2 |         1.2
      38 |    -2.1 |         1.2
      37 |    -2.2 |         1.2
(7 rows)

In the future, it would be very helpful if you would add the data and the expected output, separately, as text, not an image.

Upvotes: 1

Related Questions