sql求连续值问题

一. 找出表test1中tflag字段连续出现3次及以上为1的行

思路：1. 对行进行编号，2. 对相邻三行进行求和算出值作为sumflag，3. 如果值为3，则该行以及接下来的2行都输出出来，通过自关联解决。

WITH tmp AS (
  SELECT tday, tflag, 
         row_number() over(partition by null order by tday) as rn, -- 对表进行排序，并编rownumber
         sum(tflag) over (partition by null order by tday rows between current row and 2 following) sumflag -- 对字段tflag往下2行求和
    FROM test1) 
SELECT tday, tflag 
  FROM tmp a 
 WHERE EXISTS (SELECT 1 FROM tmp b WHERE a.rn >= b.rn AND a.rn <= b.rn + 2 AND b.sumflag = 3); -- 对表进行关联取值

二. 找出表test2中price字段连续上涨3次及以上的行。

思路：这题较上一题有点难度，连续上涨3次，也就是至少输出4行。需要用到错位函数，相邻行相减。

1. 对行进行编号，2. 对相邻行相减，3. 对减法的flag进行求和，4. 自关联

WITH tmp1 AS (
  SELECT tday, price, 
         row_number() over(partition by null order by tday) as rn, -- 对表进行排序，并编rownumber
         case when (price - lag(price) over(partition by null order by tday) >0) then 1 else 0 end as tflag -- 对上一行相减 
    FROM test2),
     tmp2 AS (  
  SELECT rn 
    FROM (SELECT rn, 
                 sum(tflag) over (partition by null order by tday rows between current row and 2 following) sumflag 
            FROM tmp1) x 
   WHERE sumflag = 3) -- 过滤出连续3次上涨的第1次上涨的行 
SELECT tday, price FROM tmp1 
 WHERE EXISTS (SELECT 1 FROM tmp2 WHERE tmp1.rn >= tmp2.rn - 1 AND tmp1.rn <= tmp2.rn + 2); -- 需要找出前1行和后2行组成连续的4行

附件：

CREATE TABLE test1 (
  tday varchar(10),
  tflag int 
);

INSERT INTO test1 VALUES ('1201',0),('1202',1),('1203',0),('1205',0),('1208',1),('1209',1),('1210',1),('1213',1),('1215',0),('1216',0),('1219',1),('1220',1),('1222',0),('1223',1),('1224',1),('1225',1),('1226',0),('1227',0),('1228',1),('1229',1),('1230',0),('1231',1);

CREATE TABLE test2 (
  tday varchar(10),
  price int
);

INSERT INTO test2 VALUES ('1201',10),('1203',20),('1204',17),('1205',15),('1207',23),('1208',26),('1209',29),('1210',11),('1211',9),('1213',5),('1214',7),('1215',15),('1217',15),('1218',13),('1219',11),('1220',9),('1221',10),('1223',30),('1224',40),('1225',50),('1227',21),('1228',40),('1229',30),('1230',11);

我一边听着凤凰传奇的动次打次，一边去想sql，这歌真提神！