How to Split a String of numbers in Spark SQL and take its average?

user_5708725 0
Viewed 330

I've a column with a string of numerical values in my dataset. I would like to split it and calculate the mean of it using Sqark SQL

"[0.0895298943, 0.0794019, 0.07463462, 0.0538841, 0.0266057681, 0.03875145, 0.0308785159]"

I know how to split it, but I would like to change the values to numbers (float), and calculate the average of this sequence.

SELECT split(FFTData,',') as VarN from dataset

Any help will be appreciated.

apache-spark-sql apache-spark
1 Answers

After you split, you can cast the resulting array of strings into an array of floats and then use aggregate function to calculate the average :

spark.sql("""
    SELECT  VarN,
            AGGREGATE(VarN, 0D, (acc, x) -> acc + x, acc -> acc / SIZE(VarN)) AS VarN_Avg
    FROM    (
        SELECT  CAST(SPLIT(FFTData, ',') AS ARRAY<FLOAT>) AS VarN
        FROM    dataset
    )
""").show(false)

//+-------------------------------------------------------------------------------------+--------------------+
//|VarN                                                                                 |VarN_Avg            |
//+-------------------------------------------------------------------------------------+--------------------+
//|[0.089529894, 0.0794019, 0.07463462, 0.0538841, 0.026605768, 0.03875145, 0.030878516]|0.056240892836025784|
//+-------------------------------------------------------------------------------------+--------------------+
user_13865510
Related