RedShift：如何将子查询字段值作为主查询字段名实现数据扁平化

要实现不硬编码date_name值的动态扁平化，可以利用Redshift的动态SQL结合字符串聚合函数自动生成透视所需的CASE语句，替代手动编写大量CASE的繁琐操作。

实现步骤 #

1. 生成动态列定义
   通过LISTAGG函数遍历engagement_dates表中的所有唯一date_name，自动生成对应的CASE表达式，将每个date_name转为xxx_date格式的字段。

2. 拼接并执行完整SQL
   将生成的动态列定义与基础查询拼接成完整的SQL语句，通过EXECUTE命令执行。

具体代码示例 #

-- 生成动态SQL并执行
DO $$
DECLARE
    dynamic_columns TEXT;
BEGIN
    -- 生成所有date_name对应的CASE语句
    SELECT LISTAGG(
        'MAX(CASE WHEN date_name = ''' || date_name || ''' THEN date END) AS ' || date_name || '_date',
        ', '
    ) INTO dynamic_columns
    FROM (SELECT DISTINCT date_name FROM engagement_dates) AS distinct_dates;

    -- 拼接完整查询语句
    EXECUTE '
        SELECT 
            e.engagementid,
            e.title,
            e.description,
            ' || dynamic_columns || '
        FROM engagements e
        LEFT JOIN engagement_dates ed ON e.engagementid = ed.engagementid
        GROUP BY e.engagementid, e.title, e.description
        ORDER BY e.engagementid;
    ';
END $$;

注意事项 #

• 如果date_name包含特殊字符（如空格、连字符），需要用QUOTE_IDENT函数处理字段名避免语法错误，修改后的列定义部分如下：

  'MAX(CASE WHEN date_name = ''' || date_name || ''' THEN date END) AS ' || QUOTE_IDENT(date_name || '_date')
  

• 执行此SQL需要具备EXECUTE权限，同时要确保engagement_dates表中的date_name数量不会过多，避免生成的SQL超出Redshift的语句长度限制。
• 结果会自动包含所有存在的date_name对应的日期字段，无需手动维护CASE语句。

示例输出 #

执行后会得到类似以下结构的结果：

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