Presto SQL未知列名时动态生成透视表的实现方案求助

由于你的App名称是动态变化的，无法预先硬编码列名，需要根据数据库类型采用动态SQL生成的方式实现透视，以下是主流数据库的具体实现方案：

PostgreSQL #

利用crosstab函数结合动态SQL，先获取所有唯一的App名称，再拼接透视查询语句：

-- 1. 生成动态透视的SQL语句
WITH app_list AS (
    SELECT string_agg(DISTINCT quote_ident(product), ', ') AS app_cols
    FROM your_table_name
)
SELECT format(
    'SELECT * FROM crosstab(
        ''SELECT feature_area, product, status FROM your_table_name ORDER BY 1,2'',
        ''SELECT DISTINCT product FROM your_table_name ORDER BY 1''
    ) AS ct(feature_area text, %s);',
    app_cols
) INTO @dynamic_sql
FROM app_list;

-- 2. 执行动态SQL
EXECUTE @dynamic_sql;

MySQL #

通过GROUP_CONCAT拼接动态列，再用PREPARE和EXECUTE执行：

-- 1. 生成动态列和透视SQL
SET @app_cols = (
    SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(product = ''', product, ''', status, ''N/A'')) AS ''', product, ''''))
    FROM your_table_name
);

SET @dynamic_sql = CONCAT(
    'SELECT feature_area, ', @app_cols, '
     FROM your_table_name
     GROUP BY feature_area;'
);

-- 2. 执行动态SQL
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server #

使用动态PIVOT，先获取所有App名称拼接成IN子句：

DECLARE @app_cols NVARCHAR(MAX), @dynamic_sql NVARCHAR(MAX);

-- 1. 生成动态列列表
SELECT @app_cols = STRING_AGG(DISTINCT QUOTENAME(product), ', ')
FROM your_table_name;

-- 2. 构造动态透视SQL
SET @dynamic_sql = CONCAT(
    'SELECT feature_area, ', @app_cols, '
     FROM (
         SELECT feature_area, product, status
         FROM your_table_name
     ) AS src
     PIVOT (
         MAX(status) FOR product IN (', @app_cols, ')
     ) AS pvt;'
);

-- 3. 执行动态SQL
EXEC sp_executesql @dynamic_sql;

大数据引擎（Spark SQL/Snowflake） #

Spark SQL #

直接使用pivot函数配合动态列集合：

// Scala示例：先获取所有App列，再执行透视
val appList = spark.table("your_table_name").select("product").distinct().collect().map(_.getString(0))
val resultDF = spark.table("your_table_name")
    .groupBy("feature_area")
    .pivot("product", appList)
    .agg(first("status"))
resultDF.show()

Snowflake #

使用存储过程生成动态透视SQL：

CREATE OR REPLACE PROCEDURE dynamic_pivot()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    app_cols VARCHAR;
    dynamic_sql VARCHAR;
BEGIN
    -- 获取所有App列
    SELECT LISTAGG(DISTINCT product, ', ') INTO app_cols FROM your_table_name;
    
    -- 构造透视SQL
    dynamic_sql := CONCAT(
        'SELECT feature_area, ', 
        LISTAGG(DISTINCT CONCAT('MAX(IF(product = ''', product, ''', status, ''N/A'')) AS ''', product, ''''), ', '),
        ' FROM your_table_name GROUP BY feature_area;'
    ) FROM your_table_name;
    
    -- 执行SQL
    EXECUTE IMMEDIATE dynamic_sql;
    RETURN '透视执行完成';
END;
$$;

-- 调用存储过程
CALL dynamic_pivot();

注意：所有示例中的your_table_name需要替换为你的实际表名，部分语法可能需要根据数据库版本微调。

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