Vertica百万级数据动态透视/转换技术求助

针对你描述的场景——将多Mask列转成行，同时将Timestamp行转成列，而且有200个Mask列、百万级数据量，Vertica的原生UNPIVOT+PIVOT组合是最优方案，比导出到Python处理高效得多，避免了数据导出导入的开销。下面分步骤讲解具体实现：

1. 核心思路：先逆透视（UNPIVOT），再透视（PIVOT） #

第一步：逆透视（把多Mask列转成行） #

先把原来的Mask1、Mask2...这些列转换成两行数据（每个Mask对应一行），生成rows列（存储'Mask1'/'Mask2'）和对应的数值列value。

基础示例SQL（手动指定Mask列）：

SELECT id, Timestamp, rows, value
FROM your_table
UNPIVOT (
    value FOR rows IN (Mask1, Mask2) -- 这里列出所有Mask列
) AS unpvt;

执行后，原表中id=1 11:30 50 100这行会变成两行：

id | Timestamp | rows  | value
---|-----------|-------|------
1  | 11:30     | Mask1 | 50
1  | 11:30     | Mask2 | 100

第二步：透视（把Timestamp行转成列） #

基于逆透视后的结果，用CASE语句+聚合函数（这里用MAX，因为每个id+rows+Timestamp唯一对应一个值）把每个Timestamp转换成单独的列。

基础示例SQL：

SELECT id, rows,
    MAX(CASE WHEN Timestamp = '09:00' THEN value ELSE NULL END) AS "09:00",
    MAX(CASE WHEN Timestamp = '11:30' THEN value ELSE NULL END) AS "11:30",
    MAX(CASE WHEN Timestamp = '11:35' THEN value ELSE NULL END) AS "11:35",
    MAX(CASE WHEN Timestamp = '12:00' THEN value ELSE NULL END) AS "12:00",
    MAX(CASE WHEN Timestamp = '22:10' THEN value ELSE NULL END) AS "22:10"
FROM (
    -- 这里嵌套第一步的逆透视SQL
    SELECT id, Timestamp, rows, value
    FROM your_table
    UNPIVOT (
        value FOR rows IN (Mask1, Mask2)
    ) AS unpvt
) AS piv_source
GROUP BY id, rows
ORDER BY id, rows;

2. 适配200个Mask列的动态SQL方案 #

手动写200个Mask列和大量Timestamp列显然不现实，我们可以利用Vertica的系统表生成动态SQL：

步骤1：生成UNPIVOT的Mask列列表 #

从系统表v_catalog.columns中自动获取所有以Mask开头的列：

SELECT STRING_AGG('"' || column_name || '"', ', ') AS mask_columns
FROM v_catalog.columns
WHERE table_name = 'your_table' -- 替换成你的表名
  AND column_name LIKE 'Mask%';

执行后会得到类似"Mask1", "Mask2", ..., "Mask200"的字符串，用于替换UNPIVOT中的列列表。

步骤2：生成PIVOT的Timestamp列语句 #

自动获取所有唯一的Timestamp值，并生成对应的CASE语句：

SELECT STRING_AGG(
    'MAX(CASE WHEN Timestamp = ''' || Timestamp || ''' THEN value ELSE NULL END) AS "' || Timestamp || '"',
    ', '
) AS pivot_clauses
FROM (
    SELECT DISTINCT Timestamp FROM your_table
) AS unique_timestamps
ORDER BY Timestamp;

执行后会生成每个Timestamp对应的列处理语句，比如MAX(CASE WHEN Timestamp = '09:00' THEN value ELSE NULL END) AS "09:00"。

步骤3：拼接完整动态SQL #

把上面两部分结果拼接成完整的SQL语句，执行即可得到目标结构：

WITH unpivot_cols AS (
    SELECT STRING_AGG('"' || column_name || '"', ', ') AS cols
    FROM v_catalog.columns
    WHERE table_name = 'your_table'
      AND column_name LIKE 'Mask%'
),
pivot_clauses AS (
    SELECT STRING_AGG(
        'MAX(CASE WHEN Timestamp = ''' || Timestamp || ''' THEN value ELSE NULL END) AS "' || Timestamp || '"',
        ', '
    ) AS clauses
    FROM (
        SELECT DISTINCT Timestamp FROM your_table
    ) AS ts_list
)
SELECT '
SELECT id, rows, ' || clauses || '
FROM (
    SELECT id, Timestamp, rows, value
    FROM your_table
    UNPIVOT (
        value FOR rows IN (' || cols || ')
    ) AS unpvt
) AS piv_source
GROUP BY id, rows
ORDER BY id, rows;'
FROM unpivot_cols, pivot_clauses;

执行这个查询会得到完整的可执行SQL，复制后直接运行就能得到你想要的结果。

3. 性能与注意事项 #

• 效率优势：全程在Vertica内部处理，避免了百万数据导出到CSV再导入的IO开销，比Python Pandas方案效率高一个数量级。
• Timestamp格式：如果实际是HH:MM:SS格式，确保字符串匹配完全一致，或者可以把Timestamp转换成时间类型（比如TO_TIMESTAMP(Timestamp, 'HH24:MI:SS')）再进行判断，避免格式问题。
• 权限要求：需要有访问v_catalog.columns的权限，普通业务用户一般默认拥有，若没有请联系DBA授权。
• 列名特殊字符：如果Timestamp包含特殊字符，Vertica会自动用双引号括起列名，确保列名合法。

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