获取连续0的最大数量SQL

Question

我在桌子下面。“月份”列表示每个客户已下订单的数量。我想计算连续0的最大数目。

因此结果表将为：

我知道有一个窗口函数可以处理行的连续0，但不确定如何处理列。

Answer 1

我建议将其作为一个缺口和孤岛问题来处理，然后重新整合：

select customerid,
       max(case when val = 0 then cnt else 0 end) as max_zeros
from (select customerid, val, (n - seqnum), count(*) as cnt
      from (select cn.*,
                   row_number() over (partition by customerid, val order by n) as seqnum
            from (select customerid, 1 as n, jan as val from t union all
                  select customerid, 2 as n, feb as val from t union all
                  select customerid, 3 as n, mar as val from t union all
                  select customerid, 4 as n, apr as val from t union all
                  select customerid, 5 as n, may as val from t union all
                  select customerid, 6 as n, jun as val from t 
                 ) cn
           ) cn
      group by customerid, val, (n - seqnum)
     ) cn
group by customerid;