PostgreSQL：如何用Crosstab生成各威胁组按周统计的完整报表？

步骤1：补全缺失天数的0值 #

要显示所有威胁组每周各天的发帖数（无发帖时显示0），需先生成所有威胁组和**所有星期几（0-6，对应周日到周六）**的完整组合，再左连接原统计结果补全0值：

-- 生成所有威胁组与星期几的笛卡尔积
WITH all_groups_days AS (
    SELECT 
        tg.threat_group,
        w.weekday
    FROM (SELECT DISTINCT threat_group FROM ransomwatch_posts) tg
    CROSS JOIN (SELECT generate_series(0,6) AS weekday) w
),
-- 统计各威胁组各天的实际发帖数
daily_counts AS (
    SELECT
        threat_group,
        extract('dow' FROM post_date)::INT AS weekday,
        COUNT(post_id) AS reports
    FROM ransomwatch_posts
    WHERE post_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY threat_group, weekday
)
-- 左连接补全无发帖天数的0值
SELECT
    agd.threat_group AS "Group",
    agd.weekday AS "Weekday",
    COALESCE(dc.reports, 0) AS "Reports"
FROM all_groups_days agd
LEFT JOIN daily_counts dc ON agd.threat_group = dc.threat_group AND agd.weekday = dc.weekday
ORDER BY agd.threat_group, agd.weekday;

步骤2：用crosstab转置为行列格式 #

使用crosstab需先确保安装tablefunc扩展（仅需执行一次）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

基于补全0值的结果编写crosstab查询，明确指定返回列结构，避免"return and sql tuple descriptions are incompatible"错误：

WITH all_groups_days AS (
    SELECT 
        tg.threat_group,
        w.weekday
    FROM (SELECT DISTINCT threat_group FROM ransomwatch_posts) tg
    CROSS JOIN (SELECT generate_series(0,6) AS weekday) w
),
daily_counts AS (
    SELECT
        threat_group,
        extract('dow' FROM post_date)::INT AS weekday,
        COUNT(post_id) AS reports
    FROM ransomwatch_posts
    WHERE post_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY threat_group, weekday
),
full_counts AS (
    SELECT
        agd.threat_group,
        agd.weekday,
        COALESCE(dc.reports, 0) AS reports
    FROM all_groups_days agd
    LEFT JOIN daily_counts dc ON agd.threat_group = dc.threat_group AND agd.weekday = dc.weekday
)
SELECT * FROM crosstab(
    -- 源查询：返回分组字段、类别字段、值字段
    'SELECT threat_group, weekday, reports FROM full_counts ORDER BY 1, 2',
    -- 指定所有类别值，确保列顺序为周日到周六
    'SELECT generate_series(0,6)'
) AS ct(
    "Group" TEXT,
    "Sunday" INT,
    "Monday" INT,
    "Tuesday" INT,
    "Wednesday" INT,
    "Thursday" INT,
    "Friday" INT,
    "Saturday" INT
);

关键说明 #

• generate_series(0,6)生成0到6的星期数值，对应PostgreSQL中extract('dow')的规则（0=周日，6=周六）。
• COALESCE函数将缺失的发帖数替换为0。
• crosstab的第二个参数显式指定类别值，确保返回列的顺序和定义的别名完全匹配，解决结构不兼容问题。

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