有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_tag
FROM (
SELECT *
,IF(LAG(b,1) OVER (ORDER BY a ) != b
OR LAG(b,1) OVER (ORDER BY a ) IS NULL,1,0) AS lag_tag
FROM tmp_train_sql
) t1
) t2
;