Averages don't add: re-combining grouped reads

A revenue dashboard reads a gold table of orders. The table is too large to copy, so someone pre-aggregates it on the platform: one row per region and day, with the day's revenue and its average order value. The chart then shows average order value by region, and the chart tool does what it was asked: it takes the average of the avg_revenue column. The number looks plausible, passes review and is wrong. An average of averages weights every day the same, whether that day had two orders or two thousand.

This post walks through the arithmetic, the SQL that gets it right, and how Emolysis handles the same problem when a chart reads a gold table that is too large to copy whole.

A worked example

Here is a grouped read of an orders table: one row per region and day, every order counted.

SELECT
  region,
  order_date,
  SUM(revenue)   AS revenue_sum,
  COUNT(revenue) AS revenue_count,
  MIN(revenue)   AS revenue_min,
  MAX(revenue)   AS revenue_max
FROM sales.orders
GROUP BY region, order_date;

The chart wants one bar per region. The tempting query divides each row first and averages the results:

SELECT region, AVG(revenue_sum / revenue_count) AS avg_order
FROM daily_orders
GROUP BY region;

For eu that is (50 + 52) / 2 = 51. But eu had seven orders worth 360 in total, so the average order is 360 / 7 = 51.43. The error here is small because the two days are similar. Make one day a single large order and the gap grows as large as you like.

Partials that combine exactly

The fix is to carry, for each measure, the pieces that still combine correctly after grouping, and to combine those pieces again at the chart's grain:

• a sum combines as the sum of the sums;
• a count combines as the sum of the counts, never as a count of rows;
• a minimum combines as the least minimum, a maximum as the greatest maximum;
• an average is carried as a sum and a count, and combines as the sum of the sums divided by the sum of the counts.

SELECT
  region,
  SUM(revenue_sum)                                   AS revenue,
  SUM(revenue_count)                                 AS orders,
  MIN(revenue_min)                                   AS smallest_order,
  MAX(revenue_max)                                   AS largest_order,
  SUM(revenue_sum) / NULLIF(SUM(revenue_count), 0)   AS avg_order
FROM daily_orders
GROUP BY region;

Two details matter. The count beside an average is COUNT(revenue), the number of non-null values, because that is what AVG(revenue) divides by; COUNT(*) gives a different answer as soon as a revenue is missing. And NULLIF keeps a group with no values from dividing by zero; its average is empty, as AVG over no values would be.

On the platforms here, / divides exactly; on an engine where dividing integers truncates, such as PostgreSQL, cast one side to a decimal first.

How Emolysis does it

A dashboard chart in Emolysis moves from illustrative rows to a sample and then to live rows, read from the deployed gold table. When the gold table fits within a size limit, Emolysis copies it whole into the project's local lakehouse and the chart's query reads that copy. When it does not, Emolysis reads it grouped instead: one query on the platform's compute, over every row, grouped by the chart's dimensions (its x and series fields), with each measure as the partials above. Live still means every row of the gold table counted.

The chart's own query then combines the partials as the chart draws them, with the rules in the list. The chart spec also says that its rows are already combined, and names the column each measure reads; an average reads its sum and its count. A renderer that draws such a spec combines rows by the same rules and never applies the chart's aggregate a second time. The spec is checked before anything draws it, so an average without its count is refused as malformed rather than drawn from the wrong column. The chart spec carries that instruction with the rows, so how to read them travels with them.

Where it stops

Grouping works because every aggregate a chart can use (sum, count, minimum, maximum and average) has partials that combine exactly. Many useful aggregates do not. A distinct count cannot be rebuilt from per-day distinct counts: a customer who ordered on both days is counted twice. A median cannot be rebuilt from per-day medians at all. Emolysis does not approximate them. An aggregate without an exact partial is not read grouped, and Emolysis says why.

Some charts cannot be grouped either. A table or a scatter plot that shows its rows as they are needs the rows, so grouping would change what it shows; over the size limit, such a chart stays on its sample and Emolysis says why. So does a chart whose measure is also its dimension. In both cases you can narrow the gold table, change the chart, or keep the sample until the table is smaller.

Grouping also has a cost. The grouped read runs on your platform's compute over the whole gold table, each time the chart goes live again. A gold table that is already at the chart's grain is the cheaper design, and the Aggregation / gold shape builds exactly that. The grouped read is the safety net for the tables that are not.

The rule fits on one line: carry sums and counts, never averages, and combine them again where the chart draws. See the glossary entry on grain for why the grain of a table decides which numbers it can answer.

Emolysis is in private development. Nothing described here is released yet.