PostgreSQL中按日期展开并统计每人每日重复次数的实现方法

嘿，我来帮你搞定这个行转列的需求！你想要把原始的带用户和时间的表，转成以用户名为行、日期为列，单元格显示用户当天出现次数的格式，这在PostgreSQL里用crosstab函数就能实现，咱们一步步来：

步骤1：安装tablefunc扩展 #

crosstab函数不属于PostgreSQL的默认内置函数，需要先安装tablefunc扩展才能使用：

CREATE EXTENSION IF NOT EXISTS tablefunc;

步骤2：静态日期范围的转置查询 #

如果你的日期范围是固定的（比如你明确知道要包含哪些日期），可以先统计每个用户每天的出现次数，再用crosstab转置。

先做基础统计 #

首先，我们先获取每个用户在每一天的出现次数：

SELECT 
    name,
    DATE(time) AS day,
    COUNT(*) AS count
FROM your_table_name  -- 替换成你的实际表名
GROUP BY name, DATE(time)
ORDER BY name, day;

转置成目标格式 #

接下来用crosstab把上面的结果转成列，同时用COALESCE把空值（用户当天无数据）替换成0：

SELECT 
    name,
    COALESCE("2018-05-13", 0) AS "2018-05-13",
    COALESCE("2018-05-14", 0) AS "2018-05-14",
    COALESCE("2018-05-16", 0) AS "2018-05-16",
    COALESCE("2018-05-18", 0) AS "2018-05-18"
    -- 继续添加你需要的日期列
FROM crosstab(
    -- 第一个参数：源数据查询（必须是三列：行分组键、列分组键、值）
    'SELECT name, DATE(time), COUNT(*) 
     FROM your_table_name 
     GROUP BY name, DATE(time) 
     ORDER BY 1,2',
    -- 第二个参数：指定要转成列的所有日期（按顺序）
    'SELECT DISTINCT DATE(time) FROM your_table_name ORDER BY 1'
) AS ct(
    name text,
    "2018-05-13" int,
    "2018-05-14" int,
    "2018-05-16" int,
    "2018-05-18" int
    -- 这里的列定义要和上面的日期列一一对应
);

注意：日期作为列名时需要用双引号括起来，否则PostgreSQL会把它当成无效标识符报错。

步骤3：动态处理日期范围（可选） #

如果你的日期范围不固定，或者需要自动包含所有出现过的日期，手动写列名太麻烦，可以用动态SQL来自动生成查询：

CREATE OR REPLACE FUNCTION get_user_daily_counts()
RETURNS SETOF record AS $$
DECLARE
    col_defs text;
BEGIN
    -- 自动生成所有日期对应的列定义
    SELECT string_agg(DISTINCT '"' || DATE(time) || '" int', ', ')
    INTO col_defs
    FROM your_table_name
    ORDER BY DATE(time);

    -- 构建并执行动态crosstab查询
    RETURN QUERY EXECUTE format(
        'SELECT * FROM crosstab(
            ''SELECT name, DATE(time), COUNT(*) 
             FROM your_table_name 
             GROUP BY name, DATE(time) 
             ORDER BY 1,2'',
            ''SELECT DISTINCT DATE(time) FROM your_table_name ORDER BY 1''
        ) AS ct(name text, %s)',
        col_defs
    );
END;
$$ LANGUAGE plpgsql;

调用这个函数时，需要指定返回的列结构（或者用json格式接收），比如：

SELECT * FROM get_user_daily_counts()
AS (name text, "2018-05-13" int, "2018-05-14" int, "2018-05-16" int, "2018-05-18" int);

如果需要把空值替换成0，可以在动态SQL里进一步处理每个列的COALESCE逻辑。

额外提示 #

如果需要包含某个时间段内的所有日期（即使当天没有用户数据），可以先用generate_series生成日期序列，再和用户表做笛卡尔积，左连接统计结果后再转置，这样就能保证所有日期都出现在列中。

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