A moving average over one table holding many devices' readings looks wrong at each device's first rows; what happened?

## What the window is actually walking A moving window walks the **rows of one object in one ordering**. It has no notion of a device, an account or an instrument unless something tells it about one. A table that stacks many devices' readings is, to the window, a single sequence of rows, and it will place its left edge in one device's records and its right edge in another's without hesitation. Nothing raises. The row count is unchanged, the types are unchanged, and every value is a plausible number in the right range. This is the ordinary shape of failure here: not an exception, but a column that is quietly wrong in a bounded, structured way. ## Two layouts, two failure shapes | table layout | where contamination appears | how much of the output is wrong | |---|---|---| | ordered by device, then by stamp | at the first positions of each device after the first | about length − 1 positions per device, for a count-defined window | | ordered by stamp only, devices interleaved | everywhere | potentially every answer, each mixing several devices | The second layout is the dangerous one, and it is common — it is what a raw export sorted by arrival gives you. There is no boundary to be *near*, because every position is a boundary. A seven-record moving average over an interleaved table of five devices is averaging roughly one and a half readings from each of them. For a **span-defined** window the arithmetic differs again: contamination is not bounded by a record count but by how many of the neighbouring entity's records fall inside the span, which may be none, or thousands after a burst. ## Why the obvious defences do not work - **Sorting by device and then by stamp does not restrict the window.** It concentrates the damage at the boundaries instead of spreading it through the output, which is an improvement in diagnosis and no improvement at all in correctness. - **Checking the row count cannot find it**, because a window computation returns one answer per input row whether or not it crossed anything. - **Eyeballing the head of the output finds the wrong rows.** The first device's leading positions are the ones that look correct under a proper restriction; it is every *subsequent* device's head that is wrong. - **A device identifier column in the table does nothing on its own.** The window does not read it unless the restriction is stated in terms of it. ## Stating the restriction Every design in this space provides some way to say that a window may not reach across into another entity's records. The forms differ — some take the entity column as an argument to the window itself, some require the records to be split by entity, the window run over each part, and the parts recombined — but the requirement is identical and it is not optional. A window that *can* reach across an entity boundary eventually will. There is also a precondition worth naming: within each entity the records must be in stamp order for the window to mean anything, and in most designs nothing verifies that for you. A second, easily missed consequence: once the restriction is in place, **each entity gets its own leading edge**. With a minimum observation count equal to the window length, every device now begins with that many positions holding no value. That is correct, and it is also the cheapest check available. ## Checks that catch it 1. Count, per entity, how many leading positions hold no value. Under a correct restriction this is the same for every entity, not just the first. 2. Take the earliest row of the second entity and recompute its answer by hand from that entity's records alone. A disagreement with the pipeline means the window crossed. 3. Shuffle the order of the entities in the input and recompute. A correctly restricted result is unchanged up to row order; an unrestricted one changes, because the contamination now arrives from a different neighbour. 4. Run the computation over a single entity's records in isolation and compare against that entity's slice of the full result. Check 3 is the strongest of the four, because it needs no knowledge of what the correct answer is — only that the answer must not depend on who happened to be sorted next to whom. ## The anchored case is worse, not better A window anchored at the start has no left edge to bound the damage. Run unrestricted over a stacked table, its running total at the first row of the fourth device already includes every reading from the first three. There is no "first few positions" to inspect: every answer after the first entity is wrong, and the error accumulates rather than washing out as the sequence advances. The restriction matters more in this case, not less — which is the opposite of the intuition that a window with no length needs fewer settings.