Cumulated sum based on condition in other column

Question

I would like to create a view based on data in following structure:

CREATE TABLE my_table (
  date date,
  daily_cumulative_precip float4
);

INSERT INTO my_table (date, daily_cumulative_precip)
VALUES
  ('2016-07-28', 3.048)
, ('2016-08-04', 2.286)
, ('2016-08-11', 5.334)
, ('2016-08-12', 0.254)
, ('2016-08-13', 2.794)
, ('2016-08-14', 2.286)
, ('2016-08-15', 3.302)
, ('2016-08-17', 3.81)
, ('2016-08-19', 15.746)
, ('2016-08-20', 46.739998);

I would like to accumulate the precipitation for consecutive days only.

Below is the desired result for a different test case - except that days without rain should be omitted:

I have tried window functions with OVER(PARTITION BY date, rain_on_day) but they do not yield the desired result.

How could I solve this?

Answer 1

SELECT date
     , dense_rank() OVER (ORDER BY grp) AS consecutive_group_nr  -- optional
     , daily_cumulative_precip
     , sum(daily_cumulative_precip) OVER (PARTITION BY grp ORDER BY date) AS cum_precipitation_mm
FROM  (
   SELECT date, t.daily_cumulative_precip
        , row_number() OVER (ORDER BY date) - t.rn AS grp
   FROM  (
      SELECT generate_series (min(date), max(date), interval '1 day')::date AS date
      FROM   my_table
      ) d
   LEFT   JOIN (SELECT *, row_number() OVER (ORDER BY date) AS rn FROM my_table) t USING (date)
   ) x
WHERE  daily_cumulative_precip > 0
ORDER  BY date;

db<>fiddle here

Returns all rainy days with cumulative sums for consecutive days (and a running group number).

Basics:

• Select longest continuous sequence