SQL rank rows by sessions of consecutive rows

Question

I have a table:

 id |   emp_id   |    telecom_id    |
----+------------+------------------+
  1 | 1          | 1                |
  2 | 1          | 1                |
  3 | 1          | 1                |
  4 | 1          | 2                |
  5 | 1          | 3                |
  6 | 1          | 3                |
  7 | 1          | 1                |
  8 | 2          | 5                |
  9 | 2          | 1                |
 10 | 1          | 1                |
 11 | 2          | 1                |
 12 | 2          | 1                |

Here are commands for table creation and filling for convenience:

CREATE TABLE table1 (
        id int NOT NULL,
        emp_id varchar(255),
        telecom_id varchar(255)
    );

    insert into table1 (id, emp_id, telecom_id) values(1, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(2, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(3, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(4, '1', '2');
    insert into table1 (id, emp_id, telecom_id) values(5, '1', '3');
    insert into table1 (id, emp_id, telecom_id) values(6, '1', '3');
    insert into table1 (id, emp_id, telecom_id) values(7, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(8, '2', '5');
    insert into table1 (id, emp_id, telecom_id) values(9, '2', '1');
    insert into table1 (id, emp_id, telecom_id) values(10, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(11, '2', '1');
    insert into table1 (id, emp_id, telecom_id) values(12, '2', '1');

I need to rank rows in this table the way, that for each session rows will have the same rank. Session is a series of consecutive rows with equal emp_id and telecom_id.

For example rows 1-3 form one session because emp_id = 1 and telecom_id = 1 for all 3 rows. Row 4 forms another session. Rows 5-6 form 3rd session etc.

It is crucial to use in ranking the order in which data is stored in table.

Desired output:

 id |   emp_id   |    telecom_id    | rnk
----+------------+------------------+------
  1 | 1          | 1                | 1
  2 | 1          | 1                | 1
  3 | 1          | 1                | 1
  4 | 1          | 2                | 2
  5 | 1          | 3                | 3
  6 | 1          | 3                | 3
  7 | 1          | 1                | 4
  8 | 2          | 5                | 5
  9 | 2          | 1                | 6
 10 | 1          | 1                | 7
 11 | 2          | 1                | 8
 12 | 2          | 1                | 8

I tried various options with window functions, but non of them worked the expected way. Here is the attempt that produced closest result to what I'm trying to achieve:

select emp_id, telecom_id, rank() 
over(partition by emp_id, telecom_id order by id) as rnk
from table1;

I'm using PostgreSQL.

Answer 1

You can try to use lag window function get pre-Val and use condition aggregate function SUM with window function to make your logic.

CREATE TABLE table1 (
        id int NOT NULL,
        emp_id varchar(255),
        telecom_id varchar(255)
    );

    insert into table1 (id, emp_id, telecom_id) values(1, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(2, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(3, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(4, '1', '2');
    insert into table1 (id, emp_id, telecom_id) values(5, '1', '3');
    insert into table1 (id, emp_id, telecom_id) values(6, '1', '3');
    insert into table1 (id, emp_id, telecom_id) values(7, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(8, '2', '5');
    insert into table1 (id, emp_id, telecom_id) values(9, '2', '1');
    insert into table1 (id, emp_id, telecom_id) values(10, '1', '1');
    insert into table1 (id, emp_id, telecom_id) values(11, '2', '1');
    insert into table1 (id, emp_id, telecom_id) values(12, '2', '1');

Query 1:

SELECT id,emp_id,telecom_id,
       SUM(CASE WHEN 
            pretelecomVal = telecom_id 
            and pre_emp_idVal = emp_id 
           then 0 else 1 end) over(order by id) rnk
FROM (
  select *,
         lag(telecom_id) over(partition by emp_id order by id) pretelecomVal,
         lag(emp_id) over(order by id) pre_emp_idVal
  from table1
) t1

Results:

| id | emp_id | telecom_id | rnk |
|----|--------|------------|-----|
|  1 |      1 |          1 |   1 |
|  2 |      1 |          1 |   1 |
|  3 |      1 |          1 |   1 |
|  4 |      1 |          2 |   2 |
|  5 |      1 |          3 |   3 |
|  6 |      1 |          3 |   3 |
|  7 |      1 |          1 |   4 |
|  8 |      2 |          5 |   5 |
|  9 |      2 |          1 |   6 |
| 10 |      1 |          1 |   7 |
| 11 |      2 |          1 |   8 |
| 12 |      2 |          1 |   8 |