postgres检测零的重复模式 [英] postgres detect repeating patterns of zeros

问题描述

在Postgres中，有没有一种方法可以在一个时间序列中检测长度为零的子序列至少3个？

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

因此在此示例中，我想返回1-3年和6-9年，但不返回11年。 / p>

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

推荐答案

这将做到：

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;

这篇关于postgres检测零的重复模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！