如何在Spark SQL中拆分数字字符串并计算其平均值

开发者阿泽

2026-9-27

实现方法

注意你当前直接用split拆分的话，得到的第一个元素会带开头的[，最后一个元素会带结尾的]，会导致类型转换失败，所以要先移除字符串首尾的方括号和多余空格，再做后续处理。

通用实现思路

第一步：清理原字符串，移除首尾[、]及元素间多余空格
第二步：按逗号拆分得到字符串数组
第三步：将数组内每个字符串元素转换为float/double类型
第四步：对转换后的数值序列计算平均值

不同SQL引擎的具体实现

Spark SQL / Hive SQL

方法1：用数组函数直接计算（无需打散行，性能更高）

SELECT 
  aggregate(
    -- 先对数组元素做类型转换
    transform(
      split(regexp_replace(FFTData, '[\\[\\] ]', ''), ','),
      x -> cast(x AS DOUBLE)
    ),
    0.0, -- 初始累加值
    (acc, val) -> acc + val, -- 累加逻辑
    acc -> acc / size(split(regexp_replace(FFTData, '[\\[\\] ]', ''), ',')) -- 最终求平均
  ) AS fft_avg
FROM dataset

方法2：打散数组后分组求平均（逻辑更直观，兼容低版本引擎）

如果你的表有每行唯一标识字段row_id，可以用这种写法：

WITH split_fft AS (
  SELECT 
    row_id,
    explode(split(regexp_replace(FFTData, '[\\[\\] ]', ''), ',')) AS str_val
  FROM dataset
)
SELECT 
  row_id,
  AVG(cast(str_val AS DOUBLE)) AS fft_avg
FROM split_fft
GROUP BY row_id

PostgreSQL

SELECT 
  row_id,
  AVG(elem::FLOAT) AS fft_avg
FROM dataset,
     -- 清理字符串后拆分并展开为行
     unnest(string_to_array(trim(both '[] ' FROM FFTData), ',')) AS elem
GROUP BY row_id