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 XX (
P int NULL , --[編號]
Q varchar(40) NULL , --[人員]
R varchar(40) NULL); --[等級]

INSERT INTO XX (P,Q,R)
VALUES 
(1,'A','甲'),
(2,'A','丙'),
(3,'D','甲'),
(4,'C','丙'),
(5,'X','乙'),
(6,'A','甲'),
(7,'D','甲'),
(8,'C','乙'),
(9,'X','甲'),
(10,'D',NULL),
(11,'C','乙'),	
(12,'C','乙');

SELECT *
FROM XX;
12 rows affected



p
q
r


1
A
甲

2
A
丙

3
D
甲

4
C
丙

5
X
乙

6
A
甲

7
D
甲

8
C
乙

9
X
甲

10
D
null

11
C
乙

12
C
乙


CREATE TABLE YY (
S varchar(40) NULL, --[部門]
T varchar(40) NULL); --[人員]

INSERT INTO YY (S,T)
VALUES 
('ㄅ','A'),
('ㄅ','D'),
('ㄆ','C'),
('ㄇ','X'),
('ㄉ','Y');

SELECT *
FROM YY;
5 rows affected



s
t


ㄅ
A

ㄅ
D

ㄆ
C

ㄇ
X

ㄉ
Y


SELECT MM.S 部門,LL.R 等級,COALESCE(UU.NUM,0) 數量,
COALESCE(UU.PERCENT,'  0'||'%') 百分比
FROM 
(SELECT DISTINCT YY.S
FROM YY) AS MM
FULL JOIN (SELECT DISTINCT XX.R
FROM XX
WHERE XX.R IS NOT NULL) AS LL ON 1=1
LEFT JOIN (
SELECT YY.S,XX.R,
COUNT(1) AS NUM,
TO_CHAR(ROUND(COUNT(1)*100/SUM(COUNT(1)) OVER (PARTITION BY YY.S),2),'99')||'%' PERCENT
FROM XX
LEFT JOIN YY ON YY.T=XX.Q
WHERE XX.R IS NOT NULL
GROUP BY YY.S,XX.R
) AS UU ON UU.S=MM.S AND UU.R=LL.R
ORDER BY 
CASE WHEN LL.R ='甲' THEN 1
WHEN LL.R ='乙' THEN 2
WHEN LL.R ='丙' THEN 3
ELSE 4 END,
CASE WHEN MM.S='ㄅ' THEN 1
WHEN MM.S='ㄆ' THEN 2
WHEN MM.S='ㄇ' THEN 3
WHEN MM.S='ㄉ' THEN 4
ELSE 5 END


部門
等級
數量
百分比


ㄅ
甲
4
 80%

ㄆ
甲
0
  0%

ㄇ
甲
1
 50%

ㄉ
甲
0
  0%

ㄅ
乙
0
  0%

ㄆ
乙
3
 75%

ㄇ
乙
1
 50%

ㄉ
乙
0
  0%

ㄅ
丙
1
 20%

ㄆ
丙
1
 25%

ㄇ
丙
0
  0%

ㄉ
丙
0
  0%

p	q	r
1	A	甲
2	A	丙
3	D	甲
4	C	丙
5	X	乙
6	A	甲
7	D	甲
8	C	乙
9	X	甲
10	D	null
11	C	乙
12	C	乙

部門	等級	數量	百分比
ㄅ	甲	4	80%
ㄆ	甲	0	0%
ㄇ	甲	1	50%
ㄉ	甲	0	0%
ㄅ	乙	0	0%
ㄆ	乙	3	75%
ㄇ	乙	1	50%
ㄉ	乙	0	0%
ㄅ	丙	1	20%
ㄆ	丙	1	25%
ㄇ	丙	0	0%
ㄉ	丙	0	0%

部門	等級	數量	百分比
ㄅ	甲	4	80%
ㄆ	甲	0	0%
ㄇ	甲	1	50%
ㄉ	甲	0	0%
ㄅ	乙	0	0%
ㄆ	乙	3	75%
ㄇ	乙	1	50%
ㄉ	乙	0	0%
ㄅ	丙	1	20%
ㄆ	丙	1	25%
ㄇ	丙	0	0%
ㄉ	丙	0	0%

部門	等級	數量	百分比
ㄅ	甲	4	80%
ㄆ	甲	0	0%
ㄇ	甲	1	50%
ㄉ	甲	0	0%
ㄅ	乙	0	0%
ㄆ	乙	3	75%
ㄇ	乙	1	50%
ㄉ	乙	0	0%
ㄅ	丙	1	20%
ㄆ	丙	1	25%
ㄇ	丙	0	0%
ㄉ	丙	0	0%