如何使用crosstab()实现多值列表格旋转？编写查询遇阻

嘿，我完全懂你现在的纠结——面对50多种不同的week值，想用crosstab()实现行转列，看着常规示例都是少量固定列，一下子就卡壳了对吧？别慌，咱们一步步来搞定这个需求。

首先得确认你已经启用了PostgreSQL的tablefunc扩展，因为crosstab()是这个扩展提供的：

CREATE EXTENSION IF NOT EXISTS tablefunc;

先明确你的表结构（我先假设一个通用示例，你可以对应自己的表调整） #

假设你的原表叫weekly_metrics，核心列是：

• 分组列（比如product_id/category，用来标识每一行的主体，要是没有分组列，后面可以用固定值替代）
• week：50+种取值的列（比如W01到W52或者2024-W01这类格式）
• 数值列（比如sales/count，就是你要转到列里的值）

情况1：静态枚举所有week列（适合week值固定不变的场景） #

如果你的week值是固定的50+个（比如每年固定52周），可以直接手动写出所有列的定义：

SELECT *
FROM crosstab(
    -- 第一个参数：源查询，必须返回「分组列 + week列 + 值列」，一定要排序
    'SELECT category, week, sales
     FROM weekly_metrics
     ORDER BY 1, 2',
    -- 第二个参数：指定要转成列的所有week值，按你想要的顺序排列
    'SELECT DISTINCT week FROM weekly_metrics ORDER BY week'
) AS pivot_result (
    -- 定义结果表结构：先写分组列，再依次写出每个week对应的列
    category VARCHAR(50),
    "W01" NUMERIC,
    "W02" NUMERIC,
    -- ... 这里把剩下的W03到W52（或你的实际week值）都列出来
    "W52" NUMERIC
);

要是某个分组在某周没有数据，结果会显示NULL，你可以在源查询里用COALESCE替换成默认值（比如0）：

'SELECT category, week, COALESCE(sales, 0) FROM weekly_metrics ORDER BY 1, 2'

情况2：动态生成week列（适合week值可能变化，不想手动写50+列的场景） #

手动写50多列太折磨人了，咱们用PL/pgSQL写个动态函数自动生成查询：

CREATE OR REPLACE FUNCTION get_weekly_pivot()
RETURNS TABLE (
    category VARCHAR(50)
    -- 函数会自动拼接所有week对应的列，无需手动定义
) AS $$
DECLARE
    week_col_defs TEXT;
    dynamic_query TEXT;
BEGIN
    -- 第一步：自动拼接所有week列的定义（比如"W01" NUMERIC, "W02" NUMERIC...）
    SELECT string_agg(DISTINCT quote_ident(week) || ' NUMERIC', ', ')
    INTO week_col_defs
    FROM weekly_metrics
    ORDER BY week;

    -- 第二步：构建动态的crosstab查询语句
    dynamic_query := format(
        'SELECT *
         FROM crosstab(
             ''SELECT category, week, sales FROM weekly_metrics ORDER BY 1, 2'',
             ''SELECT DISTINCT week FROM weekly_metrics ORDER BY week''
         ) AS ct (category VARCHAR(50), %s)',
        week_col_defs
    );

    -- 执行动态查询并返回结果
    RETURN QUERY EXECUTE dynamic_query;
END;
$$ LANGUAGE plpgsql;

调用这个函数就能直接得到转好的表：

SELECT * FROM get_weekly_pivot();

额外注意事项 #

• 如果你的表没有分组列（比如只有week和对应的值，每条数据对应一个week），可以用一个固定的分组值，比如：

  'SELECT ''all_data'' AS group_label, week, value FROM your_table ORDER BY 1, 2'
  

• 如果week值里有特殊字符（比如空格、连字符），quote_ident()会自动给它加上双引号，避免语法错误，上面的动态函数已经处理了这个问题。

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