如何使用兼容 SQLGLOT 解析器的 SQL 实现动态 unpivot？

2026-11-04排障赵师傅

如果要求仅使用 ANSI 标准的静态 SQL、禁止动态代码生成，同时源表还会不断增加年份列，就无法实现自动处理未知年份列的完全动态 unpivot。将年份配置表与源表进行 JOIN，也无法解决这个问题。

静态 SQL 可以把已知年份列转换为固定的 ISO | Name | Year | Value 结构。每次新增年份列后，仍需修改查询中的列引用。要让查询长期不变，需要调整数据导入方式，把年份存为行中的数据。

为什么年份配置表不能动态读取列

SQL 中，列标识符和数据值的含义不同：

s."1993"

读取源表中名为 1993 的列，而：

'1993'

是一个字符串。

假设年份配置表中存有 1993、1994 和 2024，连接后可以得到这些年份值。但 ANSI SQL 没有通用语法可以将行中的字符串自动解释为源表的列引用。

例如，s.column_name 访问的是名为 column_name 的列，不会根据配置表中 column_name 字段的内容去读取 s."2024"。

从 INFORMATION_SCHEMA.COLUMNS 查到新增年份列，得到的也只是元数据。要将列名转换为可执行的列引用，需要动态 SQL 或数据库专有能力，超出了题设限制。SELECT * 可以包含新增列，但不会自动将它们转换为行。

已知年份可以使用静态 `UNION ALL`

可以用 UNION ALL 显式列出要处理的年份。下面演示两个年份，完整查询需要补齐所有已知年份。

示例沿用原查询的源字段 ISO3，输出时命名为 ISO。如果实际字段名就是 ISO，请相应替换。

SELECT
    s.ISO3 AS ISO,
    s.Name AS Name,
    1993 AS "Year",
    CAST(s."1993" AS DECIMAL(18, 6)) AS "Value"
FROM gouravtest.dev.gain AS s

UNION ALL

SELECT
    s.ISO3 AS ISO,
    s.Name AS Name,
    1994 AS "Year",
    CAST(s."1994" AS DECIMAL(18, 6)) AS "Value"
FROM gouravtest.dev.gain AS s;

1993 AS "Year" 输出年份数值，s."1993" 读取对应年份列。

查询始终输出四列。源表增加 "2024" 后，需要再添加一个对应的分支，才能将这一列的数据纳入结果。

使用时还需注意：

年份列以数字命名，ANSI SQL 使用双引号引用标识符。实际执行引擎的方言和配置需要支持这种写法。
DECIMAL(18, 6) 是示例类型，应根据数值范围和精度要求调整。各分支的 Value 类型需要兼容。
UNION ALL 保留重复行，也保留值为 NULL 的行。如果业务要求丢弃空值，需要显式过滤。
各分支如何扫描和优化，要通过执行计划判断，不能假定源表只会被扫描一次。

年份配置表能做什么

配置表可以控制哪些年份参与处理，年份与源列的对应关系仍需写在静态表达式中。

假设 year_config 包含整数类型的 year_number：

SELECT
    s.ISO3 AS ISO,
    s.Name AS Name,
    y.year_number AS "Year",
    CASE y.year_number
        WHEN 1993 THEN CAST(s."1993" AS DECIMAL(18, 6))
        WHEN 1994 THEN CAST(s."1994" AS DECIMAL(18, 6))
    END AS "Value"
FROM gouravtest.dev.gain AS s
CROSS JOIN year_config AS y
WHERE y.year_number IN (1993, 1994);

这段查询只支持显式写出的两个年份。往配置表插入 2024，不会自动增加 WHEN 2024 THEN s."2024"。即使移除 WHERE 条件，缺少对应 CASE 分支的 2024 也只会得到 NULL。

配置表适合管理启用年份、展示顺序或业务标签，无法单独实现动态列访问。采用这种写法时，还需保证配置表中的年份唯一，避免重复生成结果行。

要让查询不再修改，应把年份存成数据

如果能调整导入环节，建议让下游直接接收长表：

ISO | Name | Year | Value

以后接收 2024 年的数据时，只增加记录，无需增加字段。假设长表名为 gain_long，就可以通过普通连接，用年份配置表控制查询范围：

SELECT
    g.ISO,
    g.Name,
    g."Year",
    g."Value"
FROM gain_long AS g
JOIN year_config AS y
    ON y.year_number = g."Year";

查询没有引用具体年份，新增年份记录后无需修改 SQL。如果配置表用于限制可查询年份，还需同步加入对应配置。

采用这一方案，需要导入环节完成宽表到长表的转换，或者由数据提供方直接交付长表。如果导入仍只能生成不断增加列的宽表，且转换环节也受到同样限制，就依然无法自动处理未知列。

SQLGlot 兼容性需要单独确认

SQLGlot 负责解析和转换 SQL，实际查询由执行引擎运行。SQL 能否通过解析、能否正确执行，需要分别验证。

题述的 to_json(struct(*)) → from_json(..., 'map<string,string>') → explode 使用了 Spark SQL 的函数和语法组合，不能作为可移植的 ANSI SQL 方案。即使解决当前环境对 'map<string,string>' 的处理问题，也仍不满足“仅使用 ANSI SQL”的要求。

现有信息不足以断言所有 SQLGlot 版本都不支持这一模式，具体兼容性取决于部署版本、输入方言和后续转换流程。上面的静态示例使用了 SELECT、CAST、CASE、JOIN 和 UNION ALL 等常规 SQL 构造，仍需在实际解析和执行链路中验证。

所贴原始代码还存在正则字符串未闭合、混入英文问题描述的问题，无法直接作为完整 SQL 执行；%sql 则是 Notebook 指令。这些文本问题应与复杂类型解析问题分开检查。

保留所有限制时，需要维护显式年份列表。若必须做到“新增年份无需修改查询”，则需要允许导入阶段生成长表，或放宽对动态 SQL、数据库专有功能的限制。