Introduction
Spark SQL Aggregate functions are grouped as “agg_funcs” in Spark SQL. Below is a list of functions defined under this group.
Note that every function below has another signature that takes a String as a column name instead of Column.
| AGGREGATE FUNCTION SYNTAX | AGGREGATE FUNCTION DESCRIPTION |
| ----------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------- |
| approx_count_distinct(e: Column) | Returns the count of distinct items in a group. |
| approx_count_distinct(e: Column, rsd: Double) | Returns the count of distinct items in a group. |
| avg(e: Column) | Returns the average of values in the input column. |
| collect_list(e: Column) | Returns all values from an input column with duplicates. |
| collect_set(e: Column) | Returns all values from an input column with duplicate values eliminated. |
| corr(column1: Column, column2: Column) | Returns the Pearson Correlation Coefficient for two columns. |
| count(e: Column) | Returns the number of elements in a column. |
| countDistinct(expr: Column, exprs: Column*) | Returns the number of distinct elements in the columns. |
| first(e: Column, ignoreNulls: Boolean) | Returns the first element in a column when ignoreNulls is set to true, it returns first non null element. |
| first(e: Column): Column | Returns the first element in a column. |
| grouping(e: Column) | Indicates whether a specified column in a GROUP BY list is aggregated or not, returning 1 for aggregated or 0 for not aggregated in the result set. |
| last(e: Column, ignoreNulls: Boolean) | Returns the last element in a column when ignoreNulls is set to true, it returns the last non null element. |
| last(e: Column) | Returns the last element in a column. |
| max(e: Column) | Returns the maximum value in a column. |
| mean(e: Column) | Alias for Avg. Returns the average of the values in a column. |
| min(e: Column) | Returns the minimum value in a column. |
| skewness(e: Column) | Returns the skewness of the values in a group. |
| stddev(e: Column) | alias for stddev_samp. |
| sum(e: Column) | Returns the sum of all values in a column. |
| sumDistinct(e: Column) | Returns the sum of all distinct values in a column. |
| variance(e: Column) | alias for var_samp. |
| var_samp(e: Column) | Returns the unbiased variance of the values in a column. |
| var_pop(e: Column) | Returns the population variance of the values in a column. |
Aggregate Functions Examples
First, let’s create a DataFrame to work with aggregate functions.
// Make sure you have spark session object
// Import spark sql functions
import static org.apache.spark.sql.functions.*;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import java.util.Arrays;
import java.util.List;
SparkSession spark = SparkSession.builder()
.appName("Java Spark SQL")
.config("spark.master", "local")
.getOrCreate();
List<Row> data = Arrays.asList(
RowFactory.create("James", "Sales", 3000),
RowFactory.create("Michael", "Sales", 4600),
RowFactory.create("Robert", "Sales", 4100),
RowFactory.create("Maria", "Finance", 3000),
RowFactory.create("James", "Sales", 3000),
RowFactory.create("Scott", "Finance", 3300),
RowFactory.create("Jen", "Finance", 3900),
RowFactory.create("Jeff", "Marketing", 3000),
RowFactory.create("Kumar", "Marketing", 2000),
RowFactory.create("Saif", "Sales", 4100)
);
StructType schema = new StructType()
.add("employee_name", "string")
.add("department", "string")
.add("salary", "int");
Dataset<Row> df = spark.createDataFrame(data, schema);
df.show();
Output:
+-------------+----------+------+
|employee_name|department|salary|
+-------------+----------+------+
| James| Sales| 3000|
| Michael| Sales| 4600|
| Robert| Sales| 4100|
| Maria| Finance| 3000|
| James| Sales| 3000|
| Scott| Finance| 3300|
| Jen| Finance| 3900|
| Jeff| Marketing| 3000|
| Kumar| Marketing| 2000|
| Saif| Sales| 4100|
+-------------+----------+------+
approx_count_distinct()
approx_count_distinct() function returns the count of distinct items in a group.
System.out.println("approx_count_distinct: " + df.select(approx_count_distinct(col("salary"))).collectAsList().get(0).getLong(0));
Output:
approx_count_distinct: 6
avg (average)
avg() function returns the average of values in the input column.
System.out.println("avg: " + df.select(avg(col("salary"))).collectAsList().get(0).getDouble(0));
Output:
avg: 3400.0
collect_list()
collect_list() function returns all values from an input column with duplicates.
df.select(collect_list(col("salary"))).show(false);
Output:
+------------------------------------------------------------+
|collect_list(salary) |
+------------------------------------------------------------+
|[3000, 4600, 4100, 3000, 3000, 3300, 3900, 3000, 2000, 4100]|
+------------------------------------------------------------+
collect_set()
collect_set() function returns all values from an input column with duplicate values eliminated.
df.select(collect_set(col("salary"))).show(false);
Output:
+------------------------------------+
|collect_set(salary) |
+------------------------------------+
|[4600, 3000, 3900, 4100, 3300, 2000]|
+------------------------------------+
countDistinct()
Dataset<Row> df2 = df.select(countDistinct(col("department"), col("salary")));
df2.show(false);
System.out.println("Distinct Count of Department & Salary: " + df2.collectAsList().get(0).getLong(0));
Output:
+--------------------------------------+
|count(distinct department, salary) |
+--------------------------------------+
|8 |
+--------------------------------------+
Distinct Count of Department & Salary: 8
count()
count() function returns the number of elements in a column.
System.out.println("count: " + df.select(count(col("salary"))).collectAsList().get(0).getLong(0));
Output:
count: 10
first()
first() function returns the first element in a column when ignoreNulls is set to true; it returns the first non-null element.
df.select(first(col("salary"))).show(false);
Output:
+--------------------+
|first(salary, false)|
+--------------------+
|3000 |
+--------------------+
last()
last() function returns the last element in a column. when ignoreNulls is set to true, it returns the last non-null element.
df.select(last(col("salary"))).show(false);
Output:
+-------------------+
|last(salary, false)|
+-------------------+
|4100 |
+-------------------+
kurtosis()
kurtosis() function returns the kurtosis of the values in a group.
df.select(kurtosis(col("salary"))).show(false);
Output:
+-------------------+
|kurtosis(salary) |
+-------------------+
|-0.6467803030303032|
+-------------------+
max()
max() function returns the maximum value in a column.
df.select(max(col("salary"))).show(false);
Output:
+-----------+
|max(salary)|
+-----------+
|4600 |
+-----------+
min()
min() function returns the minimum value in a column.
df.select(min(col("salary"))).show(false);
Output:
+-----------+
|min(salary)|
+-----------+
|2000 |
+-----------+
mean()
mean() function returns the average of the values in a column. Alias for Avg
df.select(mean(col("salary"))).show(false);
Output:
+-----------+
|avg(salary)|
+-----------+
|3400.0 |
+-----------+
skewness()
skewness() function returns the skewness of the values in a group.
df.select(skewness(col("salary"))).show(false);
Output:
+--------------------+
|skewness(salary) |
+--------------------+
|-0.12041791181069571|
+--------------------+
stddev(), stddev_samp() and stddev_pop()
stddev() alias for stddev_samp.
stddev_samp() function returns the sample standard deviation of values in a column.
stddev_pop() function returns the population standard deviation of the values in a column.
df.select(stddev(col("salary")), stddev_samp(col("salary")), stddev_pop(col("salary"))).show(false);
Output:
+-------------------+-------------------+------------------+
|stddev_samp(salary)|stddev_samp(salary)|stddev_pop(salary)|
+-------------------+-------------------+------------------+
|765.9416862050705 |765.9416862050705 |726.636084983398 |
+-------------------+-------------------+------------------+
sum()
sum() function Returns the sum of all values in a column.
df.select(sum(col("salary"))).show(false);
Output:
+-----------+
|sum(salary)|
+-----------+
|34000 |
+-----------+
sumDistinct()
sumDistinct() function returns the sum of all distinct values in a column.
df.select(sumDistinct(col("salary"))).show(false);
Output:
+--------------------+
|sum(DISTINCT salary)|
+--------------------+
|20900 |
+--------------------+
variance(), var_samp(), var_pop()
variance() alias for var_samp
var_samp() function returns the unbiased variance of the values in a column.
var_pop() function returns the population variance of the values in a column.
df.select(variance(col("salary")), var_samp(col("salary")), var_pop(col("salary"))).show(false);
Output:
+-----------------+-----------------+---------------+
|var_samp(salary) |var_samp(salary) |var_pop(salary)|
+-----------------+-----------------+---------------+
|586666.6666666666|586666.6666666666|528000.0 |
+-----------------+-----------------+---------------+