PostgreSQL查询每月Top X行业/国家统计结果报错求助

错误原因 #

你遇到的ERROR: return and sql tuple descriptions are incompatible错误，核心原因是crosstab函数要求输入查询的每个分组（此处为月份）对应的类别（排名）数量必须固定，且与输出定义的列数完全一致。如果某个月份的Top X结果因并列排名导致条目数超过X，或者输出列定义的数量与实际返回的类别数不匹配，就会触发这个错误。

前置准备 #

首先确保已安装tablefunc扩展（PostgreSQL默认不预装）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

正确查询实现 #

以下以**每月Top3受害行业（victim_industry）**为例给出交叉表查询，若需调整Top数量，只需替换代码中的3为你需要的X值即可。

步骤1：生成每月带唯一排名的行业统计 #

使用ROW_NUMBER()强制每个排名唯一，确保每个月份最多返回X条数据：

WITH monthly_industry_rank AS (
    SELECT
        DATE_TRUNC('month', post_date)::DATE AS month,
        victim_industry,
        COUNT(*) AS attack_count,
        -- 按攻击数降序生成唯一排名，避免并列导致条目数超标
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', post_date)
            ORDER BY COUNT(*) DESC, victim_industry
        ) AS rank
    FROM ransomware_posts
    WHERE EXTRACT(YEAR FROM post_date) = 2022
        AND victim_industry IS NOT NULL -- 过滤空值
    GROUP BY month, victim_industry
),
filtered_top AS (
    SELECT month, rank, CONCAT(victim_industry, ' (', attack_count, ')') AS industry_stats
    FROM monthly_industry_rank
    WHERE rank <= 3 -- 替换为你的Top X值
)

步骤2：用crosstab生成交叉表 #

SELECT * FROM crosstab(
    -- 输入查询：按月份、排名排序，返回三列（行标识、类别、值）
    'SELECT month, rank, industry_stats FROM filtered_top ORDER BY month, rank',
    -- 固定类别序列：1到X，确保每个月份都对应X个列
    'SELECT generate_series(1, 3)'
) AS ct_result(
    month DATE,
    top_1_industry TEXT,
    top_2_industry TEXT,
    top_3_industry TEXT
);

受害国家（victim_country）的交叉表查询 #

只需将上述查询中的victim_industry替换为victim_country即可：

WITH monthly_country_rank AS (
    SELECT
        DATE_TRUNC('month', post_date)::DATE AS month,
        victim_country,
        COUNT(*) AS attack_count,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', post_date)
            ORDER BY COUNT(*) DESC, victim_country
        ) AS rank
    FROM ransomware_posts
    WHERE EXTRACT(YEAR FROM post_date) = 2022
        AND victim_country IS NOT NULL
    GROUP BY month, victim_country
),
filtered_top AS (
    SELECT month, rank, CONCAT(victim_country, ' (', attack_count, ')') AS country_stats
    FROM monthly_country_rank
    WHERE rank <= 3
)
SELECT * FROM crosstab(
    'SELECT month, rank, country_stats FROM filtered_top ORDER BY month, rank',
    'SELECT generate_series(1, 3)'
) AS ct_result(
    month DATE,
    top_1_country TEXT,
    top_2_country TEXT,
    top_3_country TEXT
);

关键注意事项 #

• 必须使用ROW_NUMBER()而非RANK()/DENSE_RANK()：前者会为并列结果生成唯一排名，确保每个月份的条目数不超过X，避免crosstab列数不匹配。
• 输入查询必须严格按行标识, 类别, 值的三列结构返回，且按行标识+类别排序。
• 输出列的数量必须与generate_series(1, X)中的X值完全一致。

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