按2列分组后，组之间有区别

Question

我有下表：

id	date_of_birth	性别
1	01/01/1990	男性
2	01/01/1961	女性
3	01/01/1992	女性
4	01/01/2000	男性
5	01/01/11/1980	男性
6	01/01/01/1990	女性

作为输出，我希望每十年的男性和女性人数之间的差异：

十年_of_birth	emair_minus_male
1960	1
1980	-1
1990 -190	1
2000	-1，

例如，在90年代的十年中，1个男性出生，2个女性和2个女性 - ＆gt; 2-1 = 1

到目前为止，我得到了：

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 *
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
  $$, $$ SELECT DISTINCT gender FROM person; $$) AS ct( decade_of_birth int, "Male" int, "Female" int);

唯一剩下的就是获得2列之间的差异。我知道我可以通过在子查询中编写并简单地提取2列，但是我想知道串扰中是否有更优雅的方式来获得所需的输出。

Answer 1

  WITH grouped_gender 
    AS (
       SELECT *
         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
                       $, $SELECT DISTINCT gender
                              FROM person
                           $) 
    AS ct(decade_of_birth int, 
          "Male" int, 
          "Female" int))
SELECT decade_of_birth,
       "Female" - "Male" AS female_minus_male
  FROM grouped_gender;    

• 的人数
• 内部查询每十年获得每个性别crosstab
• ，将封闭的性别包裹在CTE中，从CROSSTAB访问列（找不到更优雅的解决方案）