如何在PostgreSQL中生成按小时为行、月份为列的事件统计交叉表？

嘿，这个需求我太熟了！手动写CTE确实麻烦，其实有现成的方法能搞定这种行转列的统计需求，不用每次重复造轮子～

现成工具：PostgreSQL的crosstab函数 #

如果你用的是PostgreSQL（从你提到CTE来看大概率是），tablefunc扩展里的crosstab函数就是专门干这个的，完美适配你要的“按小时分组，月份转成列”的需求。

第一步：先安装扩展（如果没装的话） #

首先得确保tablefunc扩展已经安装，执行这条命令：

CREATE EXTENSION IF NOT EXISTS tablefunc;

第二步：用crosstab生成目标结果 #

先写一个基础的分组统计查询，拿到每个小时+月份的事件数，再用crosstab把月份转成列：

SELECT *
FROM crosstab(
    -- 第一个子查询：输出小时、月份、对应事件数
    'SELECT
        EXTRACT(HOUR FROM date_create)::INT AS hour,
        TO_CHAR(date_create, ''MM'') AS month,
        COUNT(*) AS event_count
     FROM your_log_table
     GROUP BY hour, month
     ORDER BY hour, month',
    -- 第二个子查询：指定要转成列的所有月份（自动去重排序）
    'SELECT DISTINCT TO_CHAR(date_create, ''MM'') FROM your_log_table ORDER BY 1'
) AS result_table(
    hour INT,
    -- 这里可以手动列出现有月份，或者如果要完全动态的话看下面的函数方案
    "03" INT, "04" INT
);

执行完这个就能得到你想要的那种“小时为行，月份为列”的统计结果啦。

动态适配所有月份：写个自定义函数 #

如果你的日志表会不断有新月份的数据，不想每次手动修改列名，可以写个PL/pgSQL函数自动处理：

CREATE OR REPLACE FUNCTION get_monthly_hourly_event_stats()
RETURNS TABLE(hour INT, monthly_counts JSONB) AS $$
DECLARE
    month_values TEXT;
BEGIN
    -- 先自动获取所有存在的月份
    SELECT string_agg(DISTINCT TO_CHAR(date_create, 'MM'), '", "'') INTO month_values
    FROM your_log_table;

    -- 动态构建查询，把每个月份的计数打包成JSONB返回（灵活不限制列数）
    RETURN QUERY EXECUTE format(
        'SELECT
            hour,
            jsonb_object_agg(month, event_count) AS monthly_counts
         FROM (
             SELECT
                 EXTRACT(HOUR FROM date_create)::INT AS hour,
                 TO_CHAR(date_create, ''MM'') AS month,
                 COUNT(*) AS event_count
             FROM your_log_table
             GROUP BY hour, month
         ) AS grouped_data
         GROUP BY hour
         ORDER BY hour'
    );
END;
$$ LANGUAGE plpgsql;

调用这个函数的方式很简单：

SELECT * FROM get_monthly_hourly_event_stats();

返回的结果里，monthly_counts是一个JSONB字段，比如{"03":2, "04":1}，既保留了所有月份的数据，又不用手动维护列名，非常省心。

其他数据库的替代方案 #

如果用的是MySQL，可以用GROUP_CONCAT配合动态SQL实现类似的行转列；SQL Server的话直接用PIVOT语法就行，核心思路都是先分组统计，再把行转成列。

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