如何在PostgreSQL中按年份统计各类房屋数量并转置显示？

假设两张表结构如下：

• property_records：包含unique_id（唯一标识）、land_use_id（用地类型ID）、date（记录日期）字段
• land_use_types：包含land_use_id（用地类型ID）、land_use_name（房屋/用地类型名称）字段

以下提供两种可行的实现方法：

方法1：使用PostgreSQL专属crosstab函数（推荐多年份场景） #

crosstab是PostgreSQL的专用转置函数，需依赖tablefunc扩展，先启用扩展（仅需执行一次）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

编写转置查询：

SELECT *
FROM crosstab(
    -- 子查询：按类型、年份统计数量
    'SELECT 
        lut.land_use_name,
        EXTRACT(YEAR FROM pr.date)::INT AS year,
        COUNT(pr.unique_id) AS record_count
    FROM property_records pr
    JOIN land_use_types lut ON pr.land_use_id = lut.land_use_id
    GROUP BY lut.land_use_name, year
    ORDER BY lut.land_use_name, year',
    
    -- 指定要转为列的所有年份
    'SELECT DISTINCT EXTRACT(YEAR FROM date)::INT FROM property_records ORDER BY 1'
) AS transposed_result(
    land_use_name TEXT,
    -- 替换为实际存在的年份列，例如：
    "2020" INT,
    "2021" INT,
    "2022" INT,
    "2023" INT
);

如果年份是动态变化的，可通过PL/pgSQL编写存储过程动态生成列名，静态场景直接列出年份即可。

方法2：条件聚合（通用SQL写法，无需扩展） #

通过CASE WHEN对每个年份单独统计，适合年份数量较少的场景：

SELECT
    lut.land_use_name,
    COUNT(CASE WHEN EXTRACT(YEAR FROM pr.date) = 2020 THEN pr.unique_id END) AS "2020",
    COUNT(CASE WHEN EXTRACT(YEAR FROM pr.date) = 2021 THEN pr.unique_id END) AS "2021",
    COUNT(CASE WHEN EXTRACT(YEAR FROM pr.date) = 2022 THEN pr.unique_id END) AS "2022",
    COUNT(CASE WHEN EXTRACT(YEAR FROM pr.date) = 2023 THEN pr.unique_id END) AS "2023"
FROM property_records pr
JOIN land_use_types lut ON pr.land_use_id = lut.land_use_id
GROUP BY lut.land_use_name
ORDER BY lut.land_use_name;

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