All geek questions in one place

Oracle Rank () in (sub) group

I am trying to get the right rank within a subgroup and I am having problems with the result. I need to get a rank every time a group changes depending on the timestamp.

For example, using this table:

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');

I'm currently getting

 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

The conclusion that is needed is:

  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

... where the "rank" begins each time the status value changes (sorted by date). I understand that the output that I get is evaluated by a common group of states, but I could not find any combination of functions to get this output.

It almost does, but not quite:

 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;
+5
source share
1 answer

You can use the difference between the number of rows to classify successive status bars as one group. (Run an internal query to find out how groups are assigned.) Then use row_number with these groups.

 SELECT transid, launchtime, status ,ROW_NUMBER() over(PARTITION BY transid,grp ORDER BY launchtime) as rnk FROM (SELECT transid, status, launchtime, ROW_NUMBER() OVER (PARTITION BY transid ORDER BY launchtime) -ROW_NUMBER() OVER (PARTITION BY transid, status ORDER BY launchtime) as grp FROM syntrans) t
+1
source

Source: https://habr.com/ru/post/1266977/

More articles:


All Articles