Postgres 13

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 t0225 (
  id int not null
, ytype text not null
, yvalue int not null
, ymin int not null
, ymax int not null); 

insert into t0225 
values 
(1,'TypeA',10,100,200),
(2,'TypeA',20,100,200),
(3,'TypeA',30,100,200),
(4,'TypeB',410,200,300),
(5,'TypeB',420,200,300),
(6,'TypeB',430,200,300),
(7,'TypeC',310,300,400),
(8,'TypeC',320,300,400),
(9,'TypeC',330,300,400),
(12,'TypeD',410,400,500),
(13,'TypeD',490,400,500),
(14,'TypeD',550,400,500),
(15,'TypeE',510,500,600),
(16,'TypeE',590,500,600),
(17,'TypeE',450,500,600);

select *
  from t0225;
15 rows affected



id
ytype
yvalue
ymin
ymax


1
TypeA
10
100
200

2
TypeA
20
100
200

3
TypeA
30
100
200

4
TypeB
410
200
300

5
TypeB
420
200
300

6
TypeB
430
200
300

7
TypeC
310
300
400

8
TypeC
320
300
400

9
TypeC
330
300
400

12
TypeD
410
400
500

13
TypeD
490
400
500

14
TypeD
550
400
500

15
TypeE
510
500
600

16
TypeE
590
500
600

17
TypeE
450
500
600


select ytype
     , case 
         when yvalue < ymin
          then 'G1'
         when yvalue > ymax
          then 'G3'
         when yvalue >= ymin and yvalue <= ymax
          then 'G2'
         else 'G4'
       end grp
  from t0225;


ytype
grp


TypeA
G1

TypeA
G1

TypeA
G1

TypeB
G3

TypeB
G3

TypeB
G3

TypeC
G2

TypeC
G2

TypeC
G2

TypeD
G2

TypeD
G2

TypeD
G3

TypeE
G2

TypeE
G2

TypeE
G1


with t1 as (
select id
     , ytype
     , case 
         when yvalue < ymin
          then 'G1'
         when yvalue > ymax
          then 'G3'
         when yvalue >= ymin and yvalue <= ymax
          then 'G2'
         else 'G4'
       end grp
  from t0225
), t2 as (
select ytype
     ,count(*) as typecnt
  from t0225
 group by ytype
), t3 as (
select t2.ytype
     , round(count(*) filter(where grp = 'G1')::numeric / t2.typecnt,2) as percg1
     , round(count(*) filter(where grp = 'G2')::numeric / t2.typecnt,2) as percg2
     , round(count(*) filter(where grp = 'G3')::numeric / t2.typecnt,2) as percg3
  from t2
  join t1
  using (ytype)
  group by t2.ytype, t2.typecnt
), t4 as (
select ytype
     , greatest(percg1, percg2, percg3) gst
  from t3
)
select t3.ytype
     , concat(case when percg1 = gst then 'G1' end
            , case when percg2 = gst then 'G2' end
            , case when percg3 = gst then 'G3' end)
  from t3
  join t4
  using(ytype)
  order by 1
;


ytype
concat


TypeA
G1

TypeB
G3

TypeC
G2

TypeD
G2

TypeE
G2

id	ytype	yvalue	ymin	ymax
1	TypeA	10	100	200
2	TypeA	20	100	200
3	TypeA	30	100	200
4	TypeB	410	200	300
5	TypeB	420	200	300
6	TypeB	430	200	300
7	TypeC	310	300	400
8	TypeC	320	300	400
9	TypeC	330	300	400
12	TypeD	410	400	500
13	TypeD	490	400	500
14	TypeD	550	400	500
15	TypeE	510	500	600
16	TypeE	590	500	600
17	TypeE	450	500	600