Spark DataFrame单agg语句实现分组后多列去重计数及记录数统计

Got it, let's break this down for you. You can absolutely get all three stats you need in a single agg call after grouping by col1—here's the straightforward approach:

核心实现思路 #

The key is leveraging Spark's built-in aggregate functions to define all your required metrics within one agg() method call. Each metric maps to a specific function:

• 去重统计col2: Use countDistinct("col2")—this function automatically deduplicates values in col2 for each group before counting.
• 去重统计col3: Same logic applies with countDistinct("col3").
• 分组总记录数: Use count("*") (or count(lit(1)) for clarity) to count every row in the group, including rows with null values in any columns.

代码示例 #

Scala 版本

import org.apache.spark.sql.functions.{count, countDistinct}

// 假设df是你的目标DataFrame
df.groupBy("col1")
  .agg(
    countDistinct("col2").alias("distinct_col2_count"),
    countDistinct("col3").alias("distinct_col3_count"),
    count("*").alias("total_group_records")
  )

Python 版本

from pyspark.sql.functions import count, countDistinct

// 假设df是你的目标DataFrame
df.groupBy("col1")\
  .agg(
    countDistinct("col2").alias("distinct_col2_count"),
    countDistinct("col3").alias("distinct_col3_count"),
    count("*").alias("total_group_records")
  )

性能优化小技巧 #

If you're working with large datasets and strict performance requirements, consider using approx_count_distinct instead of countDistinct. It provides an approximate distinct count with configurable error tolerance (default 0.05), which is much faster because it avoids heavy shuffling for exact deduplication:

// Scala 示例，指定误差为0.01（1%误差）
approx_count_distinct("col2", 0.01).alias("approx_distinct_col2_count")

内容的提问来源于stack exchange，提问作者CaroV1x3n