Insert incrementing event_ids for every sequence_id counting up from 1-CodePudding

I have a big Oracle SQL table and I need an incrementing id counting up for every sequenceId. But if the session number is the same, it has the same id! I think it's easier to understand with an example:

Current situation:

Timestamp	ItemId	PersonId	Session	SequenceId
2020-01-14 16:15:21 UTC	5865526	465496	51c95e97-ffd6	1
2020-01-14 16:25:35 UTC	5769989	465496	61c95e97-ffd6	1
2020-01-14 16:27:31 UTC	5865524	465496	61c95e97-ffd6	1
2020-01-14 18:27:31 UTC	5865524	465496	71c95e97-ffd6	1
2019-11-04 15:28:57 UTC	5240	1120748	31c95e97-ffd6	2
2019-12-28 14:32:56 UTC	5881337	1180452	41c95e97-ffd6	3

Needed:

Timestamp	ItemId	PersonId	Session	SequenceId	EventId
2020-01-14 16:15:21 UTC	5865526	465496	51c95e97-ffd6	1	1
2020-01-14 16:25:35 UTC	5769989	465496	61c95e97-ffd6	1	2
2020-01-14 16:27:31 UTC	5865524	465496	61c95e97-ffd6	1	2
2020-01-14 18:27:31 UTC	5865524	465496	71c95e97-ffd6	1	3
2019-11-04 15:28:57 UTC	5240	1120748	31c95e97-ffd6	2	1
2019-12-28 14:32:56 UTC	5881337	1180452	41c95e97-ffd6	3	1

CodePudding user response：

Something like this?

SQL> with t as  (
  2  select 'a' sessionid, 1 sequenceid from dual union all
  3  select 'b' sessionid, 1 sequenceid from dual union all
  4  select 'b' sessionid, 1 sequenceid from dual union all
  5  select 'c' sessionid, 1 sequenceid from dual union all
  6  select 'e' sessionid, 2 sequenceid from dual union all
  7  select 'e' sessionid, 2 sequenceid from dual union all
  8  select 'g' sessionid, 2 sequenceid from dual union all
  9  select 'h' sessionid, 3 sequenceid from dual union all
 10  select 'i' sessionid, 3 sequenceid from dual union all
 11  select 'j' sessionid, 3 sequenceid from dual union all
 12  select 'k' sessionid, 3 sequenceid from dual
 13  )
 14  select
 15    t.*,
 16    dense_rank() over ( partition by sequenceid  order by sessionid ) as s
 17  from t;

S SEQUENCEID          S
- ---------- ----------
a          1          1
b          1          2
b          1          2
c          1          3
e          2          1
e          2          1
g          2          2
h          3          1
i          3          2
j          3          3
k          3          4

11 rows selected.