postgres detect repeating patterns of zeros

Question

Is there a way to detect subseries of zeros of length at least 3 within a time series in Postgres?

year    value
--------------
  1       0
  2       0
  3       0
  4       33
  5       72
  6       0
  7       0
  8       0
  9       0
  10      25
  11      0
  12      56
  13      37

So in this example I'd like to return years 1-3 and 6-9, but not year 11.

Answer 1

This one will do it:

WITH d(y,v) AS (VALUES
    (1,0),(2,0),(3,0),(4,33),(5,72),
    (6,0),(7,0),(8,0),(9,0),(10,25),
    (11,0),(12,56),(13,37)
)
SELECT grp, numrange(min(y),max(y),'[]') as ys, count(*) as len
  FROM (
    /* group identifiers via running total */
    SELECT y, v, g, sum(g) OVER (ORDER BY y) grp
      FROM (
        /* group boundaries */ 
        SELECT y, v, CASE WHEN
                     v IS DISTINCT FROM lag(v) OVER (ORDER BY y) THEN 1
                     END g
          FROM d) s
     WHERE v=0) s
 GROUP BY grp
HAVING count(*) >= 3;