我正在尝试获取子组内正确的排名,并且在结果方面遇到了问题。 我需要根据时间戳获取每次组更改的排名。
例如,使用此表:
create table syntrans (
transid number,
launchtime timestamp,
status varchar2(10)
);
insert into syntrans values ( 1, '19-APR-17 07.34.05.824875 PM','FAIL');
insert into syntrans values ( 1, '19-APR-17 07.34.06.828753 PM','FAIL');
insert into syntrans values ( 1, '19-APR-17 07.34.08.567579 PM','SUCCESS');
insert into syntrans values ( 1, '19-APR-17 08.07.31.731745 PM','SUCCESS');
insert into syntrans values ( 1, '19-APR-17 08.07.32.735582 PM','SUCCESS');
insert into syntrans values ( 2, '19-APR-17 08.17.51.332804 PM','FAIL');
insert into syntrans values ( 2, '19-APR-17 08.17.52.336530 PM','FAIL');
insert into syntrans values ( 2, '19-APR-17 08.19.27.993327 PM','SUCCESS');
insert into syntrans values ( 2, '19-APR-17 08.25.54.860077 PM','FAIL');
insert into syntrans values ( 2, '19-APR-17 08.25.55.862830 PM','SUCCESS');
我现在得到的是:
SELECT transid,
launchtime,
status,
rank() over (partition by status order by launchtime) rnk
FROM syntrans
order by transid, launchtime, status;
TRANSID LAUNCHTIME STATUS RNK
---------- ------------------------------ ---------- ----------
1 19-APR-17 07.34.05.824875 PM FAIL 1
1 19-APR-17 07.34.06.828753 PM FAIL 2
1 19-APR-17 07.34.08.567579 PM SUCCESS 1
1 19-APR-17 08.07.31.731745 PM SUCCESS 2
1 19-APR-17 08.07.32.735582 PM SUCCESS 3
2 19-APR-17 08.17.51.332804 PM FAIL 3
2 19-APR-17 08.17.52.336530 PM FAIL 4
2 19-APR-17 08.19.27.993327 PM SUCCESS 4
2 19-APR-17 08.25.54.860077 PM FAIL 5
2 19-APR-17 08.25.55.862830 PM SUCCESS 5
需要的输出结果应该像这样:
TRANSID LAUNCHTIME STATUS RNK
---------- ------------------------------ ---------- ----------
1 19-APR-17 07.34.05.824875 PM FAIL 1
1 19-APR-17 07.34.06.828753 PM FAIL 2
1 19-APR-17 07.34.08.567579 PM SUCCESS 1
1 19-APR-17 08.07.31.731745 PM SUCCESS 2
1 19-APR-17 08.07.32.735582 PM SUCCESS 3
2 19-APR-17 08.17.51.332804 PM FAIL 1
2 19-APR-17 08.17.52.336530 PM FAIL 2
2 19-APR-17 08.19.27.993327 PM SUCCESS 1
2 19-APR-17 08.25.54.860077 PM FAIL 1
2 19-APR-17 08.25.55.862830 PM SUCCESS 1
每次状态值改变时(按日期排列),"rank"就会重新开始计数。我理解我得到的输出是按整体状态组排名,但我没有找到任何函数组合可以得到所需的输出。
这个方法 几乎 能做到,但还不够:
SELECT transid, launchtime, status, rnk
FROM (SELECT transid,
status,
launchtime,
RANK() OVER (PARTITION BY transid, status ORDER BY launchtime) rnk
FROM syntrans )
ORDER BY transid, launchtime;