有一个需求,对一列数字进行转换,转换成连续部分从1开始递增。例如 1212 转换成 1111,1121转换成1211,1111 转成1234,2222转成1234。实际数据如下图:
思路:
1,第一反应是要判断每一行跟上一行的值是否相等,用游标循环,但是因为表数据千万级的,就作罢。
2,假如一定存在方法,应该从开窗函数考虑,虽然直接得不到结果
测试数据如下:
create table test_seq (seq_id number,num_from number);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (1, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (2, 2);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (3, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (4, 2);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (5, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (6, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (7, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (8, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (9, 2);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (10, 2);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (11, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (12, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (13, 2);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (14, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (15, 2);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (16, 1);
insert into TEST_SEQ (SEQ_ID, NUM_FROM) values (17, 1);
---直接对num_from使用开窗函数,这样的结果是不对的。
--seq_id=3/4时,num与上一条不同,应该x=1,实际因为是第二次出现导致x=2了。
--seq_id=5~8是连续的,应该是1~4,记成3~6
SELECT SEQ_ID,
NUM_FROM,
ROW_NUMBER() OVER(PARTITION BY NUM_FROM ORDER BY SEQ_ID) X
FROM TEST_SEQ
ORDER BY SEQ_ID;
3,直接使用row_number()开窗不行,但是也发现有个规律:seq_id-x 实现类似结果。
4,对下图的结果Y字段再次使用开窗函数
SELECT SEQ_ID,
NUM_FROM,
ROW_NUMBER() OVER(PARTITION BY NUM_FROM ORDER BY SEQ_ID) X,
SEQ_ID - ROW_NUMBER() OVER(PARTITION BY NUM_FROM ORDER BY SEQ_ID) Y
FROM TEST_SEQ
ORDER BY SEQ_ID;
---对上图结果再次使用开窗函数,结果满足
SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY NUM_FROM, Y ORDER BY SEQ_ID) NUM_TO
FROM (SELECT SEQ_ID,
NUM_FROM,
ROW_NUMBER() OVER(PARTITION BY NUM_FROM ORDER BY SEQ_ID) X,
SEQ_ID - ROW_NUMBER() OVER(PARTITION BY NUM_FROM ORDER BY SEQ_ID) Y
FROM TEST_SEQ) A
ORDER BY SEQ_ID;
感想:
1,能得到这个结果自己事先其实并没有想到,很多事情的结果都不是事先就能决定的,试了才知道,所以不要害怕困难,这一点不止于编程的事情。
2,开始之后就多做试验,说不定就碰对了呢。