By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table Table1(CLIENT_ID int, START_DT date, END_DT date, CHANNEL varchar(20), chKEY int);
insert into table1 values
('124','17.11.18','31.12.99','Массовый', 1)
,('124','10.10.18','16.11.18','Массовый', 1)
,('123','13.11.18','31.12.99','Массовый', 3)
,('123','12.11.18','12.11.18','Премьер', 2)
,('123','08.11.18','11.11.18','Массовый', 1)
,('123','13.10.18','07.11.18','Массовый', 1)
;
select * from table1
CREATE TABLE
INSERT 0 6
| client_id | start_dt | end_dt | channel | chkey |
|---|---|---|---|---|
| 124 | 2018-11-17 | 1999-12-31 | Массовый | 1 |
| 124 | 2018-10-10 | 2018-11-16 | Массовый | 1 |
| 123 | 2018-11-13 | 1999-12-31 | Массовый | 3 |
| 123 | 2018-11-12 | 2018-11-12 | Премьер | 2 |
| 123 | 2018-11-08 | 2018-11-11 | Массовый | 1 |
| 123 | 2018-10-13 | 2018-11-07 | Массовый | 1 |
SELECT 6
select *
,sum(isChange)over(partition by client_id order by start_dt)+1 key
from(
select *
,case when channel=lag(channel,1,channel)over(partition by client_id order by start_dt)
then 0
else 1
end isChange
from Table1
)a
order by client_id desc,start_dt desc
| client_id | start_dt | end_dt | channel | chkey | ischange | key |
|---|---|---|---|---|---|---|
| 124 | 2018-11-17 | 1999-12-31 | Массовый | 1 | 0 | 1 |
| 124 | 2018-10-10 | 2018-11-16 | Массовый | 1 | 0 | 1 |
| 123 | 2018-11-13 | 1999-12-31 | Массовый | 3 | 1 | 3 |
| 123 | 2018-11-12 | 2018-11-12 | Премьер | 2 | 1 | 2 |
| 123 | 2018-11-08 | 2018-11-11 | Массовый | 1 | 0 | 1 |
| 123 | 2018-10-13 | 2018-11-07 | Массовый | 1 | 0 | 1 |
SELECT 6