SQL - How can I sum elements of an array?

Question

I am using SQL with pyspark and hive, and I'm new to all of it. I have a hive table with a column of type string, like this:

id | values
1  | '2;4;4'
2  |  '5;1'
3  |  '8;0;4'

I want to create a query to obtain this:

id | values | sum
1  | '2.2;4;4'  | 10.2
2  |  '5;1.2' |  6.2
3  |  '8;0;4' | 12

By using split(values, ';') I can get arrays like ['2.2','4','4'], but I still need to convert them into decimal numbers and sum them. Is there a not too complicated way to do this?

Thank you so so much in advance! And happy coding to you all :)

Multi-valued columns are almost always an indication of a poor database design. Normalizing tables with such columns to first normal form is almost always the way to go. — Honeyboy Wilson, Mar 27 '20 at 21:17

notNull · Answer 1 · 2020-03-27T23:17:18.380

From Spark-2.4+

We don't have to use explode on arrays but directly work on array's using higher order functions.

Example:

from pyspark.sql.functions import *

df=spark.createDataFrame([("1","2;4;4"),("2","5;1"),("3","8;0;4")],["id","values"])

#split and creating array<int> column
df1=df.withColumn("arr",split(col("values"),";").cast("array<int>"))

df1.createOrReplaceTempView("tmp")

spark.sql("select *,aggregate(arr,0,(x,y) -> x + y) as sum from tmp").drop("arr").show()
#+---+------+---+
#| id|values|sum|
#+---+------+---+
#|  1| 2;4;4| 10|
#|  2|   5;1|  6|
#|  3| 8;0;4| 12|
#+---+------+---+

#in dataframe API

df1.selectExpr("*","aggregate(arr,0,(x,y) -> x + y) as sum").drop("arr").show()
#+---+------+---+
#| id|values|sum|
#+---+------+---+
#|  1| 2;4;4| 10|
#|  2|   5;1|  6|
#|  3| 8;0;4| 12|
#+---+------+---+

score 1 · Accepted Answer · answered Mar 27 '20 at 22:08

PySpark solution

from pyspark.sql.functions import udf,col,split
from pyspark.sql.types import FloatType 
#UDF to sum the split values returning none when non numeric values exist in the string
#Change the implementation of the function as needed
def values_sum(split_list):
    total = 0
    for num in split_list:
        try:
            total += float(num)
        except ValueError:
            return None
    return total

values_summed = udf(values_sum,FloatType())
res = df.withColumn('summed',values_summed(split(col('values'),';')))
res.show()

The solution could've been a one-liner if it were known the array values are of a given data type. However, it is better to go with a safer implementation that covers all cases.

Hive solution

Use explode with split and group by to sum the values.

select id,sum(cast(split_value as float)) as summed
from tbl
lateral view explode(split(values,';')) t as split_value
group by id

score -1 · Answer 3 · answered Mar 27 '20 at 21:49

-1

write a stored procedure which does the job:

CREATE FUNCTION SPLIT_AND_SUM ( s VARCHAR(1024) ) RETURNS INT
BEGIN
   ...
END

answered Mar 27 '20 at 21:49

draz

793
6
10

SQL - How can I sum elements of an array?

3 Answers3