Introduction
Spark SQL Aggregate functions are grouped as “agg_funcs” in Spark SQL. Below is a list of functions defined under this group. Each function can be used with its String column name version as well.
| 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.
# Ensure SparkSession is available
from pyspark.sql import SparkSession
from pyspark.sql.functions import approx_count_distinct, avg, collect_list, collect_set, count, countDistinct, first, last, kurtosis, max, min, mean, skewness, stddev, stddev_samp, stddev_pop, sum, sumDistinct, variance, var_samp, var_pop
spark = SparkSession.builder.appName("PySpark Aggregate Functions").getOrCreate()
# Create the DataFrame
simpleData = [
("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)
]
df = spark.createDataFrame(simpleData, ["employee_name", "department", "salary"])
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.
approx_count = df.select(approx_count_distinct("salary")).collect()[0][0]
print(f"approx_count_distinct: {approx_count}")
Output:
approx_count_distinct: 6
avg (average)
avg() function returns the average of values in the input column.
average_salary = df.select(avg("salary")).collect()[0][0]
print(f"avg: {average_salary}")
Output:
avg: 3400.0
collect_list()
collect_list() function returns all values from an input column with duplicates.
df.select(collect_list("salary")).show(truncate=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("salary")).show(truncate=False)
Output:
+------------------------------------+
|collect_set(salary) |
+------------------------------------+
|[4600, 3000, 3900, 4100, 3300, 2000]|
+------------------------------------+
countDistinct()
count_distinct_df = df.select(countDistinct("department", "salary"))
count_distinct_df.show(truncate=False)
distinct_count = count_distinct_df.collect()[0][0]
print(f"Distinct Count of Department & Salary: {distinct_count}")
Output:
+--------------------------------------+
|count(distinct department, salary) |
+--------------------------------------+
|8 |
+--------------------------------------+
Distinct Count of Department & Salary: 8
count()
count() function returns the number of elements in a column.
salary_count = df.select(count("salary")).collect()[0][0]
print(f"count: {salary_count}")
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("salary")).show(truncate=False)
Output:
+------------+
|first(salary)|
+------------+
|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("salary")).show(truncate=False)
Output:
+-----------+
|last(salary)|
+-----------+
|4100 |
+-----------+
kurtosis()
kurtosis() function returns the kurtosis of the values in a group.
df.select(kurtosis("salary")).show(truncate=False)
Output:
+-------------------+
|kurtosis(salary) |
+-------------------+
|-0.6467803030303032|
+-------------------+
max()
max() function returns the maximum value in a column.
df.select(max("salary")).show(truncate=False)
Output:
+-----------+
|max(salary)|
+-----------+
|4600 |
+-----------+
min()
min() function returns the minimum value in a column.
df.select(min("salary")).show(truncate=False)
Output:
+-----------+
|min(salary)|
+-----------+
|2000 |
+-----------+
mean()
mean() function returns the average of the values in a column. Alias for Avg
df.select(mean("salary")).show(truncate=False)
Output:
+-----------+
|avg(salary)|
+-----------+
|3400.0 |
+-----------+
skewness()
skewness() function returns the skewness of the values in a group.
df.select(skewness("salary")).show(truncate=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("salary"), stddev_samp("salary"), stddev_pop("salary")).show(truncate=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("salary")).show(truncate=False)
Output:
+-----------+
|sum(salary)|
+-----------+
|34000 |
+-----------+
sumDistinct()
sumDistinct() function returns the sum of all distinct values in a column.
df.select(sumDistinct("salary")).show(truncate=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("salary"), var_samp("salary"), var_pop("salary")).show(truncate=False)
Output:
+-----------------+-----------------+---------------+
|var_samp(salary) |var_samp(salary) |var_pop(salary)|
+-----------------+-----------------+---------------+
|586666.6666666666|586666.6666666666|528000.0 |
+-----------------+-----------------+---------------+