Window Functions, Frames, Ranking, Running Metrics, and Top-N per Group

Learning outcomes

ServiceHub reporting now needs “top two expensive work orders per region,” technician rank by cost, a running cost total over time, and each row’s percentage of regional cost. GROUP BY alone collapses many input rows into one output row per group. Window functions solve a different problem: calculate across a related set of rows while still returning each row. The subtlety is that every window has partition, ordering and frame semantics, and defaults may not match the mental picture a report author has in mind.

MariaDB supports window functions through the OVER clause, including PARTITION BY, window ORDER BY, and ROWS/RANGE frame types. Ranking functions and aggregate windows behave differently: ranking assigns positions; aggregate windows compute values over a frame. Duplicate sort keys create peers, which is exactly where default-frame assumptions become visible.

1. A window computes without collapsing rows

Consider regional work-order totals. GROUP BY would return one row per region. A window aggregate can return every work order plus the total for its region. The partition defines which rows are related for the calculation; it does not reduce the result to one row per partition.

SELECT work_order_id, region, labor_cost,       SUM(labor_cost) OVER (PARTITION BY region) AS regional_costFROM work_ordersORDER BY region, work_order_id;

This distinction is foundational. Use GROUP BY when the desired result grain is the group. Use a window when you need a group-relative calculation while preserving the underlying rows. Complex reports often use both: group in one stage, then window over the grouped result.

2. Window ORDER BY is not final result ordering

The ORDER BY inside OVER(...) defines sequence for the window calculation. The outer SELECT ORDER BY defines presentation order. They often use similar keys, but they are different clauses with different jobs. A report can rank by labor cost and still display rows alphabetically; conversely, a final ORDER BY does not retroactively define a window frame.

SELECT work_order_id, region, labor_cost,       ROW_NUMBER() OVER (         PARTITION BY region         ORDER BY labor_cost DESC, work_order_id       ) AS row_numFROM work_ordersORDER BY region, row_num, work_order_id;

The unique work_order_id inside the window ordering makes ROW_NUMBER deterministic among equal costs. Without it, tied rows can receive either relative row number because the window ordering did not distinguish them.

3. ROW_NUMBER, RANK and DENSE_RANK encode different tie rules

ROW_NUMBER() assigns a unique sequence position. RANK() gives peers the same rank and leaves gaps after ties. DENSE_RANK() also gives peers the same rank but does not leave gaps. The correct function follows the business meaning of “rank,” not a performance superstition.

SELECT work_order_id, region, labor_cost,       ROW_NUMBER() OVER (PARTITION BY region ORDER BY labor_cost DESC) AS rn,       RANK()       OVER (PARTITION BY region ORDER BY labor_cost DESC) AS rnk,       DENSE_RANK() OVER (PARTITION BY region ORDER BY labor_cost DESC) AS dense_rnkFROM work_ordersORDER BY region, labor_cost DESC, work_order_id;

4. Running totals require an explicit frame when peers matter

A window frame specifies which rows around the current row contribute to a window aggregate. MariaDB supports ROWS and RANGE. ROWS counts physical positions in the window order; RANGE groups peer values according to the ordering expression and frame boundary semantics. If an aggregate window has an ORDER BY and you rely on the default frame, duplicate sort keys can make the running result advance by peer groups rather than one row at a time.

SELECT work_order_id, opened_at, labor_cost,       SUM(labor_cost) OVER (         ORDER BY opened_at       ) AS implicit_frame_total,       SUM(labor_cost) OVER (         ORDER BY opened_at, work_order_id         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW       ) AS explicit_row_totalFROM work_ordersORDER BY opened_at, work_order_id;

The lab has duplicate opened_at timestamps. The explicit second window states the report requirement precisely: move row by row in a deterministic order and include all preceding rows plus the current row. This is safer than asking future maintainers to remember the server’s default frame behavior.

5. Top-N per group is a two-stage query

Window values are computed after the row-source filtering/grouping stages, so a common pattern calculates row numbers in a CTE or derived table and filters those computed values in the outer query. This makes “top two per region” both readable and deterministic.

WITH ranked AS (  SELECT work_order_id, region, labor_cost,         ROW_NUMBER() OVER (           PARTITION BY region           ORDER BY labor_cost DESC, work_order_id         ) AS rn  FROM work_orders)SELECT work_order_id, region, labor_cost, rnFROM rankedWHERE rn <= 2ORDER BY region, rn, work_order_id;

If product requirements change to include ties, replace the ranking rule deliberately rather than changing rn <= 2 blindly. The ranking function, tie-breaker and filter together define the business semantics.

6. Window calculations can build on grouped results

A useful analytical pattern first creates one row per technician with GROUP BY, then applies ranking or percentage windows over those aggregate rows. This avoids applying a window directly to a more detailed event grain than the report intends.

WITH tech_cost AS (  SELECT technician_id,         SUM(labor_cost) AS total_cost  FROM work_orders  WHERE technician_id IS NOT NULL  GROUP BY technician_id)SELECT technician_id, total_cost,       RANK() OVER (ORDER BY total_cost DESC) AS cost_rank,       total_cost / SUM(total_cost) OVER () AS share_of_totalFROM tech_costORDER BY cost_rank, technician_id;

This is a useful review technique: state the grain after every stage. tech_cost is one row per technician; the outer window operates over technicians, not raw work orders. That prevents accidental weighting from duplicate detail rows.

7. Observe execution, but do not optimize away the meaning

EXPLAIN FORMAT=JSONWITH ranked AS (  SELECT work_order_id, region, labor_cost,         ROW_NUMBER() OVER (           PARTITION BY region           ORDER BY labor_cost DESC, work_order_id         ) rn  FROM work_orders)SELECT * FROM ranked WHERE rn <= 2;ANALYZE FORMAT=JSONWITH ranked AS (  SELECT work_order_id, region, labor_cost,         ROW_NUMBER() OVER (           PARTITION BY region           ORDER BY labor_cost DESC, work_order_id         ) rn  FROM work_orders)SELECT * FROM ranked WHERE rn <= 2;

Window execution may require sorting or temporary structures. On this tiny lab the timings are meaningless as capacity data; use it only to learn the shape of evidence. In production, measure representative row counts, partitions, indexes, memory and concurrency. Do not replace a correct window query with an opaque user-variable trick based on an old tutorial unless you have proven semantics and version behavior.

8. Failure drill and acceptance checklist

1. Compute regional totals with a window and prove rows are not collapsed.
2. Compare ROW_NUMBER, RANK and DENSE_RANK on a dataset with at least one tie.
3. Run a running SUM using only window ORDER BY and compare it with an explicit ROWS frame.
4. Use the duplicate opened_at values in the seed to explain peer behavior.
5. Implement exact top-2-per-region with ROW_NUMBER and a unique tie-breaker.
6. Change the rule to include ties and document the expected row-count difference.
7. Aggregate technician cost first, then rank the aggregate rows.
8. Run EXPLAIN/ANALYZE and record whether sorts/temporary processing appear on your target version.

9. Verify peer and frame behavior with adversarial fixtures

Window bugs are easiest to expose with deliberately awkward data. Add two rows with the same ordering value, two rows with the same ranking value, and one NULL where the domain permits it. Then calculate the result manually for a few rows before looking at MariaDB output. This small exercise reveals whether the SQL author is thinking in row positions, peer groups, or value ranges.

For a running total, write down exactly which prior rows should contribute to each current row. If the answer is “every earlier row in this deterministic sequence,” use an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame and enough ordering keys to make the sequence unique. If the requirement is value-based and peers should move together, a RANGE frame may better state the intent. The goal is not to prefer ROWS universally; it is to make the business frame visible.

Performance testing should use representative partition sizes. A window over ten rows says nothing about a partition containing millions of rows. Record the target server version, indexes, row counts, memory settings and observed plan/runtime evidence when deciding whether the database window is the right boundary or whether pre-aggregation/materialization should occur elsewhere.

10. Summary and bridge

Window functions calculate across partitions without collapsing rows. PARTITION BY defines the related group, window ORDER BY defines sequence, and a frame defines which portion contributes to an aggregate. Ranking functions encode different tie policies. Explicit ROWS/RANGE choices and deterministic tie-breakers prevent “works on my sample” reports from changing when duplicate sort keys appear.

The final lesson combines grouping, HAVING, ROLLUP and set operations into production reporting workflows. It also addresses a MariaDB-specific reporting constraint: WITH ROLLUP emits NULL in super-aggregate columns and cannot be combined directly with ordinary ORDER BY, so subtotal labeling and presentation need deliberate design.