MariaDB Window Functions: Replacing Self-Joins for Running Totals and Top-N-Per-Group

The reporting query that won't die

Every team has that one report: monthly sales that self-joins the orders table three times, or "latest status per shipment" built on a correlated subquery that times out once the table passes a few million rows. These patterns work on paper but collapse under real data because the optimizer must materialize intermediates or run the subquery once per row.

MariaDB has supported window functions since 10.2. The takeaway is that for running totals, top-N-per-group and lag/lead comparisons, window functions express intent directly and usually run faster, provided you understand frame semantics and where the optimizer materializes the sort.

Top-N-per-group without a correlated subquery

For orders with id, customer_id, order_date and total, the classic "latest three orders per customer" used to be:

SELECT o1.*
FROM orders o1
WHERE 3 > (
  SELECT COUNT(*)
  FROM orders o2
  WHERE o2.customer_id = o1.customer_id
    AND o2.order_date >= o1.order_date
);

With an index on (customer_id, order_date) it is tolerable up to a few hundred thousand rows; beyond that the optimizer often scans.

The window equivalent:

SELECT id, customer_id, order_date, total
FROM (
  SELECT 
    id,
    customer_id,
    order_date,
    total,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
) AS ranked
WHERE rn <= 3;

ROW_NUMBER() assigns 1,2,3 within each customer_id partition ordered newest first. Window functions are evaluated after WHERE/GROUP BY/HAVING but before ORDER BY/LIMIT, so filtering on rn requires a derived table or CTE.

Running totals with explicit framing

A running balance per account:

SELECT 
  id,
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
    ROWS UNBOUNDED PRECEDING
  ) AS running_total
FROM transactions;

The self-join alternative sums all prior rows per row and is quadratic. Two details matter. First, explicit ROWS UNBOUNDED PRECEDING. The default for an ordered aggregate is RANGE UNBOUNDED PRECEDING, which groups peer rows with the same txn_date together. ROWS gives row-by-row accumulation, which finance usually expects. Second, PARTITION BY resets the sum per account.

Where cost hides

Window functions do not eliminate sorting. MariaDB must partition and order input before computing the window. Derived tables with windows are often materialized into an internal temporary table. If that exceeds tmp_table_size or max_heap_table_size it spills to disk and latency spikes.

Practical checks:

• Run EXPLAIN on the window query. Look for Using temporary and Using filesort in Extra.
• Ensure indexes exist on PARTITION BY and ORDER BY columns, e.g., INDEX (customer_id, order_date DESC).
• If the intermediate row count is huge before the WHERE rn <= 3 filter, push predicates into the inner query where possible.

Verify version first: run SELECT VERSION(); in the mysql client. Window syntax requires 10.2 or later.

Trade-off: readability vs materialization

Window functions win on clarity and auditability. The top-N query is shorter and easier to review. But they are not free. High partition cardinality or low-cardinality ordering columns can cause large sorts and temporary tables. If EXPLAIN shows a disk spill and latency spikes, a correlated subquery with a covering index may stop early per partition and be faster for that shape. Measure with your data.

Next steps

1. Inventory reporting queries that use correlated subqueries or self-joins for ranking, running totals or lag/lead.
2. Rewrite one low-risk dashboard query with ROW_NUMBER() or SUM() OVER.
3. Run EXPLAIN on both versions and compare rows examined, temporary table creation and execution time.
4. If the window version is equal or faster, adopt it and document the frame choice ROWS vs RANGE in a comment.