我在桌子下面。“月份”列表示每个客户已下订单的数量。我想计算连续0的最大数目。
因此结果表将为：
我知道有一个窗口函数可以处理行的连续0，但不确定如何处理列。
我建议将其作为一个缺口和孤岛问题来处理，然后重新整合：
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;
