GROUP BY in a Window Function's Clothes

If you read the CalculationFormula column in KC Star V2's MeasureTypeLookup, you would conclude the aggregates are window functions. SaleTypeTotal is documented as SUM(TotalSale) OVER (PARTITION BY SaleType). OverallTotal as SUM(TotalSale) OVER (). Clean, declarative, modern. And then you read the actual function that computes them, CreateAggregatedFacts, and there is not an OVER clause in sight — just GROUP BY. This post is about that gap: how the aggregates are really built, why I described them as window functions anyway, and what the difference costs.

What CreateAggregatedFacts actually does

CreateSalesFacts writes the four base measures for a sale, then calls CreateAggregatedFacts() to (re)build measures 5 through 8 across all sales. The function is a two-CTE affair:

CREATE OR REPLACE FUNCTION CreateAggregatedFacts()
RETURNS VOID AS $$
BEGIN
    WITH BaseFacts AS (
        SELECT sf.FactId, sf.FactValue AS TotalSale, sf.SaleType
        FROM SalesFact sf
        WHERE sf.MeasureTypeId = 1        -- TotalSale
          AND sf.IsCurrent = TRUE
          AND sf.SalesReferenceId IS NOT NULL
    ),
    Aggregated AS (
        -- per-SaleType rollups (measures 5, 6)
        SELECT SaleType,
               SUM(TotalSale) AS SaleTypeTotal,
               AVG(TotalSale) AS SaleTypeAverage,
               MIN(FactId)    AS BaseFactId
        FROM BaseFacts
        GROUP BY SaleType
    )
    INSERT INTO SalesFact (SalesReferenceId, SaleType, MeasureTypeId,
                           FactValue, BaseFactId, ValidFrom, IsCurrent, VersionHash)
    SELECT NULL, SaleType, 5, SaleTypeTotal, BaseFactId,
           CURRENT_TIMESTAMP, TRUE,
           digest(...)   -- hash over the contributing FactIds
    FROM Aggregated;
    -- ...measures 6, 7 (overall), 8 in the same shape...
END;
$$ LANGUAGE plpgsql;

BaseFacts gathers every current TotalSale fact with its SaleType — available directly because V2 promoted SaleType to a column. Aggregated rolls those up with GROUP BY SaleType. The result inserts back into SalesFact as new rows with SalesReferenceId = NULL (an aggregate belongs to no single sale), a SaleType partition, and a BaseFactId set to MIN(FactId) — a pointer at one of the contributing base facts. The overall aggregates (measures 7 and 8) do the same without the GROUP BY, producing one row each with a NULL SaleType.

That is the machinery. Plain relational aggregation, inserted as fact rows. No windows.

Why the formulas say OVER

So why does the lookup describe these as SUM(...) OVER (PARTITION BY SaleType) when the code says GROUP BY SaleType? Two reasons, one honest and one that became a lesson.

The honest reason is that the window-function phrasing describes the intent more clearly than the implementation does. SUM(TotalSale) OVER (PARTITION BY SaleType) reads as “for each row, the total for its sale type” — which is exactly the semantic an analyst wants. GROUP BY SaleType collapses rows and forces a re-join to attach the aggregate back to individual sales. The formula column is documentation for a human, and the window form documents the meaning better.

The reason that became a lesson is performance, and the V2 changelog calls it out directly. A window function computes the partitioned aggregate and keeps every input row — you get one output row per input, each carrying its partition's total. Materialising that for every sale, on every fact rebuild, when what I actually needed to store was one aggregate row per partition, is wasteful. GROUP BY produces exactly the rows I store: one per SaleType, plus the overalls. I chose the shape that matched the storage, and left the formula describing the shape that matched the meaning.

I do not think that was wrong, but I would document it louder now. A formula string that does not match the executed SQL is a small trap for the next reader — they will grep for OVER, find nothing, and wonder which is authoritative. The answer is: the code is authoritative, the formula is a specification. Keeping those roles explicit is the fix.

The variance query, and what it needs

The whole point of storing per-SaleType aggregates is questions like this one, straight from the usage examples:

SELECT Id, SaleType, TotalSale, SaleTypeAverage,
       (TotalSale - SaleTypeAverage) AS VarianceFromAverage
FROM CurrentSalesState
ORDER BY SaleType, Id;

Each sale, its sale type's average, and its deviation. For the view to serve this, it has to stitch the base fact for each sale to the aggregate fact for that sale's SaleType — which it does by matching on the SaleType column. This is where the GROUP BY choice pays back: because aggregates are stored as their own rows keyed by SaleType, the view joins on a column rather than recomputing a window over the whole fact table on every read.

You can also inspect the aggregate rows directly, and their NULL SalesReferenceId is the tell:

SELECT SalesReferenceId, ValidFrom, ValidTo, MeasureName, FactValue, CalculationContext
FROM SalesFact sf
JOIN MeasureTypeLookup mtl ON sf.MeasureTypeId = mtl.MeasureTypeId
WHERE sf.MeasureTypeId IN (5, 6, 7, 8)   -- aggregated measures
ORDER BY ValidFrom, SalesReferenceId;

Every row here has SalesReferenceId = NULL, because these facts summarise many sales rather than describing one. That NULL is elegant — it cleanly distinguishes a base fact from an aggregate fact by a property of the data itself.

Or so it seems. That same NULL is the seam the next post picks apart: when it comes time to expire old aggregates and rebuild them, telling base rows from aggregate rows by their reference key turns out to be exactly one distinction short of what the schema needs — and per-SaleType aggregates quietly stop expiring.