Postgres 16

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 it1013 (
  boy text
, sdate date
, code char(1)
);

insert into it1013
values ('小明','2024/10/1','A'),('小明','2024/10/2','A'),('小明','2024/10/3','A'),('小明','2024/10/4','A')
,('小明','2024/10/5','休'),('小明','2024/10/6','休'),('小明','2024/10/7','A'),('小明','2024/10/8','A')
,('小明','2024/10/9','A'),('小明','2024/10/10','休'),('小明','2024/10/11','A'),('小明','2024/10/12','休')
,('小明','2024/10/13','休'),('小明','2024/10/14','B'),('小明','2024/10/15','B'),('小明','2024/10/16','B')
,('小明','2024/10/17','B'),('小明','2024/10/18','B'),('小明','2024/10/19','休'),('小明','2024/10/20','休')
,('小明','2024/10/21','A'),('小明','2024/10/22','A'),('小明','2024/10/23','A'),('小明','2024/10/24','A')
,('小明','2024/10/25','A'),('小明','2024/10/26','休'),('小明','2024/10/27','休'),('小明','2024/10/28','A')
,('小明','2024/10/29','A'),('小明','2024/10/30','A'),('小明','2024/10/31','A')
,('大白','2024/10/1','特'),('大白','2024/10/2','特'),('大白','2024/10/3','B'),('大白','2024/10/4','B')
,('大白','2024/10/5','休'),('大白','2024/10/6','休'),('大白','2024/10/7','B'),('大白','2024/10/8','B')
,('大白','2024/10/9','B'),('大白','2024/10/10','休'),('大白','2024/10/11','B'),('大白','2024/10/12','休')
,('大白','2024/10/13','休'),('大白','2024/10/14','A'),('大白','2024/10/15','A'),('大白','2024/10/16','A')
,('大白','2024/10/17','A'),('大白','2024/10/18','A'),('大白','2024/10/19','休'),('大白','2024/10/20','休')
,('大白','2024/10/21','B'),('大白','2024/10/22','B'),('大白','2024/10/23','B'),('大白','2024/10/24','B')
,('大白','2024/10/25','B'),('大白','2024/10/26','休'),('大白','2024/10/27','休'),('大白','2024/10/28','B')
,('大白','2024/10/29','B'),('大白','2024/10/30','B'),('大白','2024/10/31','B');

CREATE TABLE

INSERT 0 62

with t1 as (
select boy, sdate
     , case when code = '休' or  code = '特' then null
       else code
       end
  from it1013
), t2 as (
select *
     , sum(case when code is null then 0 else 1 end) over(order by boy, sdate) as code_grp
  from t1
), t3 as (
select boy, sdate
     , first_value(code) over(partition by code_grp order by sdate) as new_code
  from t2
), t4 as (
select boy, sdate, new_code
     , sum(case when new_code is null then 0 else 1 end) over(order by boy, sdate desc) as new_code_grp
  from t3
), t5 as (
select boy, sdate
     , first_value(new_code) over(partition by new_code_grp order by sdate desc) as new_code2
  from t4
), t6 as (
select boy, sdate
     , new_code2
     , lag(new_code2) over(partition by boy order by sdate) lagcode
  from t5
), t7 as (
select boy, sdate, new_code2 as code
     , case when new_code2 = 'A' and lagcode = 'B' then true
            when new_code2 = 'B' and lagcode = 'A' then true
            else false
       end as cut
  from t6
), t8 as (
select boy, sdate, code, date_part('day', sdate)::int as cutdate
  from t7
 where cut
    or date_part('day', sdate)::int = 1
), t9 as (
select boy, code, cutdate, coalesce(lead(cutdate) over (partition by boy order by sdate) - 1, 31) as enddate
  from t8
) , t10 as (
select boy
     , cutdate
     , code || ':' || cutdate::text || '~' || enddate::text as datestr
  from t9
)
select boy
     , string_agg(datestr, ',' order by cutdate)
  from t10
 group by boy;

boy	string_agg
大白	B:1~13,A:14~20,B:21~31
小明	A:1~13,B:14~20,A:21~31

SELECT 2