如何在Amazon Redshift中无需crosstab实现动态行列转置

你需要将记录企业间迁移数据的表动态转置为以Current_to_Previous为列名的宽表，替代硬编码企业名称的方案，以下是基于Amazon Redshift的实现方法：

核心思路 #

Redshift没有原生的动态PIVOT功能，需通过动态拼接SQL语句实现：

• 提取表中所有唯一的Current与Previous组合，生成对应列名（如A_to_B）
• 拼接包含所有动态列的CASE WHEN聚合逻辑
• 执行最终生成的完整SQL

实现方案 #

方法1：存储过程（推荐用于重复执行） #

创建存储过程自动生成并执行转置SQL：

CREATE OR REPLACE PROCEDURE dynamic_migration_pivot()
LANGUAGE plpgsql
AS $$
DECLARE
    pivot_columns TEXT;
    full_sql TEXT;
BEGIN
    -- 生成所有Current_to_Previous的CASE WHEN聚合语句
    SELECT STRING_AGG(
        'SUM(CASE WHEN Current = ''' || Current || ''' AND Previous = ''' || Previous || ''' THEN "Count" ELSE 0 END) AS ' || quote_ident(Current || '_to_' || Previous),
        ', '
    ) INTO pivot_columns
    FROM (SELECT DISTINCT Current, Previous FROM your_table_name) AS combinations;

    -- 拼接完整查询SQL
    full_sql := '
        SELECT 
            DATE(Date) AS transaction_date,
            ' || pivot_columns || '
        FROM your_table_name
        WHERE DATE_TRUNC(''month'', Date) = ''2021-01-01''::DATE
        GROUP BY DATE(Date)
        ORDER BY DATE(Date);
    ';

    -- 执行动态生成的SQL
    EXECUTE full_sql;
END;
$$;

调用存储过程：

CALL dynamic_migration_pivot();

方法2：临时生成SQL片段（适合单次查询） #

如果不需要持久化存储过程，可分步手动生成：

1. 生成动态列的CASE WHEN片段：

SELECT STRING_AGG(
    'SUM(CASE WHEN Current = ''' || Current || ''' AND Previous = ''' || Previous || ''' THEN "Count" ELSE 0 END) AS ' || quote_ident(Current || '_to_' || Previous),
    ', ' || CHR(10) || '    '
) AS pivot_sql_fragment
FROM (SELECT DISTINCT Current, Previous FROM your_table_name) AS combinations;

2. 将查询返回的pivot_sql_fragment内容替换到以下模板中执行：

SELECT 
    DATE(Date) AS transaction_date,
    -- 替换为上面生成的片段
    SUM(CASE WHEN Current = 'A' AND Previous = 'B' THEN "Count" ELSE 0 END) AS A_to_B,
    SUM(CASE WHEN Current = 'A' AND Previous = 'C' THEN "Count" ELSE 0 END) AS A_to_C,
    -- ...其他动态列
FROM your_table_name
WHERE DATE_TRUNC('month', Date) = '2021-01-01'::DATE
GROUP BY DATE(Date)
ORDER BY DATE(Date);

关键注意事项 #

• Count是Redshift保留字，必须用双引号"Count"引用
• quote_ident()函数确保列名包含特殊字符时语法合法
• 添加日期过滤条件可大幅提升大表查询性能

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