How to get other columns when using Spark DataFrame groupby?

by

Long story short in general you have to join aggregated results with the original table. Spark SQL follows the same pre-SQL:1999 convention as most of the major databases (PostgreSQL, Oracle, MS SQL Server) which doesn’t allow additional columns in aggregation queries.

Since for aggregations like count results are not well defined and behavior tends to vary in systems which supports this type of queries you can just include additional columns using arbitrary aggregate like first or last.

In some cases you can replace agg using select with window functions and subsequent where but depending on the context it can be quite expensive.

More Related Contents:

  1. How to select the first row of each group?
  2. Querying Spark SQL DataFrame with complex types
  3. Concatenate columns in Apache Spark DataFrame
  4. Spark sql queries vs dataframe functions
  5. Difference between === null and isNull in Spark DataDrame
  6. Spark Window Functions – rangeBetween dates
  7. Spark SQL window function with complex condition
  8. Does SparkSQL support subquery?
  9. Including null values in an Apache Spark Join
  10. Explode (transpose?) multiple columns in Spark SQL table
  11. get TopN of all groups after group by using Spark DataFrame
  12. Why using a UDF in a SQL query leads to cartesian product?
  13. How to aggregate over rolling time window with groups in Spark
  14. What is the difference between cube, rollup and groupBy operators?
  15. How to add a constant column in a Spark DataFrame?
  16. DataFrame / Dataset groupBy behaviour/optimization
  17. How to define partitioning of DataFrame?
  18. How do I add a new column to a Spark DataFrame (using PySpark)?
  19. Dropping a nested column from Spark DataFrame
  20. Difference between DataFrame, Dataset, and RDD in Spark
  21. Filtering a Pyspark DataFrame with SQL-like IN clause
  22. Spark Dataframe :How to add a index Column : Aka Distributed Data Index
  23. Updating a dataframe column in spark
  24. Dividing complex rows of dataframe to simple rows in Pyspark
  25. Spark add new column to dataframe with value from previous row
  26. Why does Spark think this is a cross / Cartesian join
  27. Pyspark: Replacing value in a column by searching a dictionary
  28. Pivot String column on Pyspark Dataframe
  29. Derive multiple columns from a single column in a Spark DataFrame
  30. Spark replacement for EXISTS and IN

Leave a Comment