PostgreSQL实现性别统计结果行列转换的SQL查询（含Union与非Union方案）

需求说明 #

现有数据集如下：

name|gender|
----+------+
AA  |Male  |
BB  |Male  |
CC  |Male  |
DD  |Female|
EE  |Female|

当前使用以下SQL统计性别数量：

select 
    gender,count(*)
from 
    schoolofit.user_request_1
group by gender

得到的结果为：

gender|count|
------+-----+
Female|    2|
Male  |    3|

但需要得到如下格式的结果：

gender-1 | gender-2
-------------------
Male     | Female
3        |  2 

以下是两种PostgreSQL实现方案：

方案1：使用UNION实现 #

通过UNION ALL将性别名称行和统计数值行合并，确保列结构一致：

-- 第一行返回性别名称
SELECT
    'Male' AS "gender-1",
    'Female' AS "gender-2"
UNION ALL
-- 第二行返回统计数量，转换为字符串对齐格式
SELECT
    CAST(SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS VARCHAR) AS "gender-1",
    CAST(SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS VARCHAR) AS "gender-2"
FROM schoolofit.user_request_1;

方案2：不使用UNION实现（基于行生成） #

先聚合得到男女数量，再通过生成两行数据分别展示名称和数值：

SELECT
    -- 根据行号判断显示名称还是数量
    CASE rn 
        WHEN 1 THEN 'Male' 
        ELSE CAST(male_count AS VARCHAR) 
    END AS "gender-1",
    CASE rn 
        WHEN 1 THEN 'Female' 
        ELSE CAST(female_count AS VARCHAR) 
    END AS "gender-2"
FROM (
    -- 一次性统计男女数量
    SELECT
        COUNT(*) FILTER (WHERE gender = 'Male') AS male_count,
        COUNT(*) FILTER (WHERE gender = 'Female') AS female_count
    FROM schoolofit.user_request_1
) t,
-- 生成2行数据，对应名称行和数值行
GENERATE_SERIES(1,2) AS rn;

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