题目1：根据第一二列，计算出第三列。即：求每组KH_VALUE状态(1和0)变化的最小时间

--创建测试表create table tmp asselect to_date('2017-04-21 16:22:00','yyyy-mm-dd hh24:mi:ss') dt, 1 kv from dual union allselect to_date('2017-04-21 16:23:00','yyyy-mm-dd hh24:mi:ss') dt, 1 kv from dual union allselect to_date('2017-04-21 16:24:00','yyyy-mm-dd hh24:mi:ss') dt, 0 kv from dual union allselect to_date('2017-04-21 16:25:00','yyyy-mm-dd hh24:mi:ss') dt, 0 kv from dual union allselect to_date('2017-04-21 16:26:00','yyyy-mm-dd hh24:mi:ss') dt, 0 kv from dual union allselect to_date('2017-04-21 16:27:00','yyyy-mm-dd hh24:mi:ss') dt, 0 kv from dual union allselect to_date('2017-04-21 16:28:00','yyyy-mm-dd hh24:mi:ss') dt, 1 kv from dual union allselect to_date('2017-04-21 16:29:00','yyyy-mm-dd hh24:mi:ss') dt, 1 kv from dual union allselect to_date('2017-04-21 16:30:00','yyyy-mm-dd hh24:mi:ss') dt, 0 kv from dual union allselect to_date('2017-04-21 16:31:00','yyyy-mm-dd hh24:mi:ss') dt, 0 kv from dual;--SQL实现:select dt,kv,min(dt)over(partition by rn order by dt) new_dt from(select dt,kv,sum(kv2)over(order by dt) rn        from(select dt,kv,                    --case when lag(kv,1)over(order by dt) = kv then 0 else 1 end kv2                    case when lag(kv,1)over(order by dt) = kv then 0 else row_number()over(order by dt) end kv2               from tmp             )      )

题目2：按照c1的顺序，求出c2状态发生变化的开始及结束位置。

已知tmp表数据如下：

c1 c2

------

1 1

2 1

4 1

5 0

6 0

7 0

8 1

9 1

10 1

11 1

12 1

13 0

14 1

15 1

16 1

17 1

18 1

19 1

c1列为编号，c2为状态(0,1),要求实现下面的效果：

开始位置,结束位置,状态

1,4,1

5,7,0

8,12,1

13,13,0,

14,19,1

 

--创建测试表create table tmp(c1 int ,c2 int );insert into tmp values(1,1);insert into tmp values(2,1);insert into tmp values(4,1);insert into tmp values(5,0);insert into tmp values(6,0);insert into tmp values(7,0);insert into tmp values(8,1);insert into tmp values(9,1);insert into tmp values(10,1);insert into tmp values(11,1);insert into tmp values(12,1);insert into tmp values(13,0);insert into tmp values(14,1);insert into tmp values(15,1);insert into tmp values(16,1);insert into tmp values(17,1);insert into tmp values(18,1);insert into tmp values(19,1);--解法1：select min(c1) start_c1,max(c1) start_c2c2from(select c1,c2,sum(rn)over(order by c1) rnfrom(select c1,c2,decode(c2, lag(c2) over(order by c1), null, row_number() over(order by c1)) rnfrom tmp))group by rn, c2;--解法2：select min(c1), max(c1), c2from (select b.*,row_number()over(partition by g order by c1) r1,row_number()over(partition by g order by c1 desc) r2from (select a.*, sum(t) over(order by c1) gfrom (select t.*, decode(c2, lag(c2, 1, c2) over(order by c1), 0, 1) tfrom tmp t)a) b) cwhere r1 = 1 or r2 = 1group by g, c2order by 1;--解法3：select min(c1) s, max(c1) e, c2from (select c1, c2, sum(rn) over(order by c1) rnfrom (select c1, c2,case when lag(c2) over(order by c1) = c2 then 0 else 1 end rnfrom tmp))group by c2, rnorder by s