我已从附带的Excel文件导入数据。当前数据集的结构如下：

ISO、名称、1993、1994、1995、…、2023

每一年都作为单独的一列来表示，收到新数据时会新增年份列。例如，在下次加载时，结构可能会变成：

ISO、名称、1993、1994、1995、…、2024

我想对年份列实施一个unpivot转换，将数据转换为规范化格式，如下所示：

ISO | Name | Year | Value

这将确保解决方案具有可扩展性，能够在无需结构性变更的情况下容纳新的年份列。

如下为预期输出

我已经使用基于JSON的方法构造了下面的查询（to_json(struct(*)) → from_json(..., 'map<string,string>') → explode）。这在Spark SQL中能够正确工作；然而，我们的 SQLGlot解析引擎不支持此模式，并且在复杂类型定义 'map<string,string>' 上会出错，导致结果不正确。

%sql
WITH base AS (
  SELECT
    Name AS COUNTRY,
    ISO3 AS ISO,
    *
  FROM  gouravtest.dev.gain

),
json_data AS (
  SELECT
    COUNTRY,
    ISO,
    from_json(to_json(struct(*)), 'map<string,string>') AS kv_map
  FROM base
)

SELECT
  COUNTRY,
  ISO,
  key AS year,
  value
FROM json_data
LATERAL VIEW explode(kv_map) t AS key, value
WHERE key RLIKE '^[0-9]{4}$';

此外，我还必须仅使用 符合ANSI标准的纯SQL——不允许使用Python/pyspark或任何动态代码生成。

鉴于这些约束，我正在探索是否可以通过使用一个 年份配置表 与源表进行连接来实现，从而：

• 新添加到源表的年份列将自动得到处理
• 解决方案具备可扩展性，无需修改查询

请问在ANSI SQL下，是否能够实现这样一个完全动态的解决方案？

解决方案

之所以失败，是因为JSON本身并非SQL的原生支持，而SparkQL在 SQL的基础上提供了一些额外的特性。

ANSI-SQL查询可以利用 UNPIVOT 转换，将年份及其各自的值展开为两列——键和值对集合。以下是在SQL中对表进行展开的查询。

SELECT
   ISO3 AS ISO,
   Name AS country,
   Year,
   Value
FROM gouravtest.dev.gain
UNPIVOT (
  Value FOR Year IN ("1993","1994","1995","1996" /* ... */ ,"2023")
) u;