rollup / cube

What it does

A report often needs detail rows, subtotals and a grand total. Without rollup you would run three aggregations and union them. rollup produces all of them in one query.

rollup is hierarchical: it removes columns from the right, so the column order matters. cube produces all 2ⁿ combinations.

Step by step

Run the example

from pyspark.sql import functions as F
result = (sales
    .rollup("region", "city")
    .agg(F.sum("amount").alias("sales"),
         F.grouping("city").alias("is_region_total"),
         F.grouping("region").alias("is_grand_total"))
    .orderBy(F.col("region").asc_nulls_last(), F.col("city").asc_nulls_last()))

SELECT region, city, SUM(amount) AS sales,
       GROUPING(city)   AS is_region_total,
       GROUPING(region) AS is_grand_total
FROM sales
GROUP BY ROLLUP(region, city)
ORDER BY region NULLS LAST, city NULLS LAST

Real nulls vs total rows

If the data itself has a null city, a detail row and a subtotal row both show city = null. grouping("city") is 0 for the detail row and 1 for the subtotal. Use it to label rows: F.when(F.grouping("city") == 1, "All cities").otherwise(F.col("city")). grouping_id() returns all the flags as one bit vector, handy for filtering a specific level.

GROUPING SETS in SQL

rollup and cube are shorthands for GROUPING SETS, which lets you list exactly the combinations you want:

SELECT region, city, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS ((region, city), (region), ())

Older PySpark versions have no grouping-sets method on DataFrames (recent ones add groupingSets), so for arbitrary combinations use SQL or union several groupBys.

Under the hood

Spark implements rollup and cube with an Expand operator: each input row is copied once per grouping set, with the rolled-up columns set to null and a hidden grouping id added, and then a single aggregation runs. For rollup over n columns that means n + 1 copies; cube over n columns means 2ⁿ copies. cube over 6 columns copies every row 64 times before the shuffle, so keep cube to a few columns.

Common mistakes