PostgreSQL中使用crosstab交叉表后优雅计算男女数量差值的方法（无需子查询）

嘿，刚好有个简洁的办法能满足你的需求——不用子查询，直接基于你现有的crosstab查询结果计算差值就行，完全符合你要的“优雅”要求。

修改后的完整SQL #

CREATE TABLE person (
 id BIGSERIAL NOT NULL PRIMARY KEY,
 date_of_birth DATE,
 gender VARCHAR(50)
);

insert into person (id, date_of_birth, gender) values (1, '01/01/1990', 'male');
insert into person (id, date_of_birth, gender) values (2, '01/01/1961', 'female');
insert into person (id, date_of_birth, gender) values (3, '01/01/1992', 'female');
insert into person (id, date_of_birth, gender) values (4, '01/01/2000', 'male');
insert into person (id, date_of_birth, gender) values (5, '01/01/1980', 'male');
insert into person (id, date_of_birth, gender) values (6, '01/01/1990', 'female');

SELECT 
 decade_of_birth,
 "Female" - "Male" AS female_minus_male
FROM crosstab($$
 SELECT EXTRACT(DECADE FROM date_of_birth) * 10 AS decade_of_birth,
        gender,
        COUNT(DISTINCT id) AS nbr_persons
 FROM person
 GROUP BY 1,2
$$, $$
 -- 固定gender顺序，避免crosstab列映射错误
 SELECT unnest(ARRAY['male', 'female'])
$$) AS ct(
 decade_of_birth int,
 "Male" int,
 "Female" int);

核心思路说明 #

你原来的crosstab查询已经完美生成了按年代分组的男女数量列，我们只需要在外层SELECT语句中直接计算两列的差值，完全不需要嵌套子查询。这样既保留了crosstab处理交叉表的简洁性，又一步到位得到了你想要的female_minus_male结果。

重要注意事项 #

crosstab的第二个查询返回的gender顺序会直接和结果列的定义对应，如果用SELECT DISTINCT gender，数据库的默认排序可能会导致列映射错误（比如把女性数量放到"Male"列里）。所以我改成了用unnest(ARRAY['male', 'female'])固定顺序，确保"Male"列对应男性数量，"Female"列对应女性数量，计算差值时不会出错。

内容的提问来源于stack exchange，提问作者ItsChungus