prec, PostgreSQL - rextester

prec

--PostgreSQL 9.6
--'\\' is a delimiter

with t as (
      select *
      from (values (1, '10/10', 8),
                   (2, '10/10', 8),
                   (3, '10/10', 0),
                   (4, '10/10', 1),
                   (5, '10/10', 1),
                   (6, '10/10', 8),
                   (7, '11/10', 8),
                   (8, '11/10', 8),
                   (9, '11/10', 1),
                   (10, '11/10', 0),
                   (11, '11/10', 1),
                   (12, '11/10', 8),
                   (13, '11/10', 3),
                   (14, '11/10', 8)
          ) v(id, date, rain)
    )

select t.*, sum(rain) over (partition by grp order by id) as prec from
(select t.*, sum(real) over (order by id) grp from
(select t.*, case when (prev1=1 and prev2=1 and flag=1) then 1 else 0 end as real from
(select t.*, lag(flag, 1) over (order by id) as prev1, lag(flag, 2) over (order by id) as prev2   from
(select t.*, (case when rain <= 2 then 1 else 0 end) as flag from t order by id) t) t) t) t


run \| edit \| history \| help	0