将门户网站建设龙岗建设企业网站

有temp表，使用SQL，对B列进行处理，形成C列，按A列顺序，B列值不变，则C列累计技术，B列值变化，则C列重新开始计数

建表语句如下

CREATE TABLE temp(A STRING ,B STRING 
);INSERT INTO TABLE temp VALUES('2010','1'),('2011','1'),('2012','1'),('2013','0'),('2014','0'),('2015','1'),('2016','1'),('2017','1'),('2018','0'),('2019','0')
;

解题SQL如下：

SELECT  a,b,ROW_NUMBER() OVER (PARTITION BY group_tag ORDER BY a ) c
FROM    (SELECT  *,SUM(lag_tag) OVER (ORDER BY a ) AS group_tagFROM    (SELECT  *,IF(LAG(b,1) OVER (ORDER BY a ) != bOR      LAG(b,1) OVER (ORDER BY a ) IS NULL,1,0) AS lag_tagFROM    tmp_train_sql) t1) t2
;