PySpark groupBy and Window Functions: Aggregations, Ranks and Running Totals (Part 2 of 3)

groupBy collapses rows into buckets. Window functions keep every row and add a rank or a running total beside it. This post follows Part 2 of the video series on querying data with PySpark in Databricks Free Edition: aggregate eight fictional orders by region and category, roll up by region, then rank and accumulate with windows. Like Part 1, this part is read-only.

<iframe width="560" height="315" src="https://www.youtube.com/embed/H3Et0gWCnek" title="PySpark groupBy Tutorial: Aggregations, count, sum &amp; Window Functions (Part 2 of 3)" frameborder="0" allowfullscreen></iframe>

Watch on YouTube

The notebook, source file and cheat sheet are in the Part 2 folder on GitHub. The notebook rebuilds the same eight orders as Part 1, so it stands on its own. Use a Python notebook on Serverless compute.

Aggregation changes the grain

Before grouping, one row means one order. After grouping by region and category, one row means one reporting bucket. Window functions are different: they keep the detail rows.

A useful habit from the lesson: say the sentence out loud. "One row means one order," or "one row means one region and category." If you cannot finish that sentence, do not aggregate yet.

Start from a total you can reconcile

Only rows with both quantity and price count toward revenue:

valid = orders.filter(F.col("quantity").isNotNull() & F.col("unit_price").isNotNull())

That gives six valid rows totaling 142.50. Every later total should reconcile back to that number.

Group by region and category

region_category = (valid.groupBy("region", "category")
    .agg(F.count("*").alias("order_count"),
         F.sum("order_total").cast("decimal(18,2)").alias("revenue"))
    .orderBy("region", "category"))

count("*") counts every row in the group. Counting a nullable column instead would skip nulls, which answers a different question. The orderBy only changes how the report is shown.

| region | category | order_count | revenue | |---|---|---|---| | EAST | Books | 1 | 12.00 | | NORTH | Books | 1 | 20.00 | | NORTH | Toys | 2 | 60.00 | | SOUTH | Toys | 1 | 25.00 | | WEST | Books | 1 | 25.50 |

Five buckets, six orders and 142.50 in revenue. It matches the valid input total.

Roll up by region

Group by fewer columns for a coarser report:

by_region = (valid.groupBy("region")
    .agg(F.count("*").alias("order_count"),
         F.sum("order_total").cast("decimal(18,2)").alias("revenue"))
    .orderBy("region"))

The result is EAST 12.00, NORTH 80.00 across three orders, SOUTH 25.00 and WEST 25.50. NORTH's 80.00 is the 20.00 plus 60.00 from its two category buckets. Add the regions and confirm you still get 142.50. Reconciliation is part of the analysis, not an optional extra.

Rank and accumulate with window functions

from pyspark.sql import Window

wrank = Window.partitionBy("category").orderBy( F.col("ordertotal").desc(), F.col("orderid").desc() ) wrun = Window.orderBy("orderdate", "orderid").rowsBetween( Window.unboundedPreceding, Window.currentRow )

ranked = (valid .withColumn("categoryrank", F.rownumber().over(wrank)) .withColumn("runningrevenue", F.sum("ordertotal").over(wrun).cast("decimal(18,2)"))) ```

• rownumber() over wrank restarts the rank for each category. The secondary sort on order_id gives ties a stable rank.
• w_run has no partition, so the running total spans every valid order by date, from the first row up to the current row.

Partitioning a window is not the same as grouping. The ranked and running-total columns appear next to the original order fields.

In the actual run, Toys ranked 108 first, 104 second and 102 third, and the running revenue ended at 142.50 on order 108.

Do not ignore the single-partition warning

Spark printed a non-fatal warning on the running total: no partition is defined for the window operation, so all data moves to a single partition, which can seriously hurt performance at scale.

For an eight-row teaching sample, a global running total is intentional. On large data, partition the window when the total should restart per key, or accept the shuffle cost on purpose and measure it. A right answer with an expensive plan is still a production risk.

Practice challenge

From the valid rows, keep only Toys, compute row_number() ordered by total descending, and predict the ranks before you run. You should see 108, 104, then 102. Do not write any tables in this part.

Key takeaways

• Know the grain before you aggregate: one order, or one bucket.
• Use count("*") for row counts and reconcile every rollup to a known total.
• Windows keep the detail rows. partitionBy restarts the calculation per key.
• An unpartitioned window should be a deliberate choice, not an accident.

Next, Part 3 answers the same questions in Spark SQL and saves one managed Delta summary safely.