一个小sql的问题记录 对分析函数的应用

附件：第1次提的需求
--1.处理前的数据如下
WITH TB1 AS
(SELECT 1 LSH,'001' USERID from DUAL
UNION ALL
SELECT 2,'001'   FROM DUAL
UNION ALL
SELECT 3,'001'   FROM DUAL
UNION ALL
SELECT 4,'002'   FROM DUAL
UNION ALL
SELECT 5,'003'   FROM DUAL
UNION ALL
SELECT 6,'008'   FROM DUAL
UNION ALL
SELECT 7,'003'   FROM DUAL
UNION ALL
SELECT 8,'003'   FROM DUAL
UNION ALL
SELECT 9,'002'   FROM DUAL
UNION ALL
SELECT 10,'002'   FROM DUAL
)
SELECT * FROM TB1;


LSH USERID
1    001
2    001
3    001
4    002
5    003
6    008
7    003
8    003
9    002
10  002


--2.希望得到的结果：
1）同一个USERID，如果LSH连续，如何为其生成相同的NEW_LSH?
2）NEW_LSH不能重复使用
3）不用游标
LSH USERID  NEW_LSH
1    001     1
2    001     1
3    001     1
4    002     4
5    003     5
6    008     6
7    003     7
8    003     7
9    002     9
10  002     9

可以达到要求的sql语句:

第一种方法:

select lsh, userid, min(lsh) over(partition by userid, flag) new_lsh
  from (SELECT lsh,
               userid,
               case
                 when userid = lead(userid)
                  over(order by rownum) or userid = lag(userid)
                  over(order by rownum) then
                  1
                 else
                  0
               end flag
          FROM TB1)
 order by lsh;

第二种方法:

t1 as
(select lsh,
         userid,
         lsh - row_number() over(partition by userid order by lsh) rn
    from tmp),
t2 as(    
select userid, rn, min(lsh) new_lsh from t1 group by userid, rn)
select a.lsh,a.userid, b.new_lsh
from t1 a
left join t2 b
on a.userid = b.userid
and a.rn = b.rn
order by 1;

第三种方法:(11g的递归with)

TMP(LSH,USERID,LEV) AS(
  SELECT LSH,USERID,1 LEV FROM TB1 T WHERE ROWNUM<=1
  UNION ALL
  SELECT T1.LSH,T1.USERID,CASE WHEN T1.USERID=T2.USERID THEN LEV ELSE T1.LSH END FROM  TMP T2,TB1 T1 WHERE T1.LSH-T2.LSH=1
)
select * from tmp;

第四种方法:

select x.*, last_value(flag ignore nulls) over(order by lsh) new_lsh
  from (select t.*,
               decode(userid, lag(userid) over(order by lsh), null, lsh) flag
          from TB1 t) x;

---------------------------

之后要求所有改变
--1.处理前的数据如下
WITH TB1 AS
(SELECT 1 LSH,'001' USERID from DUAL
UNION ALL
SELECT 2,'001'   FROM DUAL
UNION ALL
SELECT 3,'001'   FROM DUAL
UNION ALL
SELECT 4,'002'   FROM DUAL
UNION ALL
SELECT 5,'003'   FROM DUAL
UNION ALL
SELECT 6,'008'   FROM DUAL
UNION ALL
SELECT 7,'003'   FROM DUAL
UNION ALL
SELECT 8,'003'   FROM DUAL
UNION ALL
SELECT 9,'002'   FROM DUAL
UNION ALL
SELECT 10,'002'   FROM DUAL
UNION ALL
SELECT 13,'001'   FROM DUAL 
UNION ALL
SELECT 15,'002'   FROM DUAL
UNION ALL
SELECT 17,'001'   FROM DUAL 
UNION ALL
SELECT 19,'001'   FROM DUAL 
)
select * from tb1;


LSH USERID
1    001
2    001
3    001
4    002
5    003
6    008
7    003
8    003
9    002
10  002
13 001      
15 002     
17 001      
19 001      


--希望得到的结果
LSH USERID NEW_LSH
1   001      1
2   001      1
3   001      1
4   002      4
5   003      5
6   008      6
7   003      7
8   003      7
9   002      9
10 002      9
13 001      13
15 002      15
17 001      17
19 001      17
--希望得到的结果
1）同一个USERID，如果LSH连续（17,19两个序号算连续），如何为其生成相同的NEW_LSH?
2）NEW_LSH不能重复使用
3）不用游标

以上的sql只有第四种方依旧有效

第四种方法:

select x.*, last_value(flag ignore nulls) over(order by lsh) new_lsh
  from (select t.*,
               decode(userid, lag(userid) over(order by lsh), null, lsh) flag
          from TB1 t) x;