historian(sqlserver): time-weighted Part 13 aggregates #671

New Issue

2026-10-09T15:26:12-04:00

dohertj2 commented

2026-10-09 15:26:12 -04:00

The SQL Server backend pushes aggregates down to T-SQL using simple per-sample definitions. Average is the arithmetic mean of samples, and there is no time weighting or interpolated bounding values (see docs/Historian.md §"SQL Server backend", aggregate table). These are not full Part 13 definitions.

Scope: add TimeAverage, TimeAverage2 and Interpolative, plus Total time-weighted, with Part 13 bounding-value semantics, pushed down using window functions (LAG/LEAD at the bucket edges). Pin each one with hand-computed literals.

Claude-Session: https://claude.ai/code/session_01Ef2ozxS7QPanGT8uoATbTe

The SQL Server backend pushes aggregates down to T-SQL using simple per-sample definitions. Average is the arithmetic mean of samples, and there is no time weighting or interpolated bounding values (see `docs/Historian.md` §"SQL Server backend", aggregate table). These are not full Part 13 definitions. **Scope:** add `TimeAverage`, `TimeAverage2` and `Interpolative`, plus `Total` time-weighted, with Part 13 bounding-value semantics, pushed down using window functions (LAG/LEAD at the bucket edges). Pin each one with hand-computed literals. Claude-Session: https://claude.ai/code/session_01Ef2ozxS7QPanGT8uoATbTe

dohertj2 referenced this issue from a commit

2026-10-11 10:17:47 -04:00

feat(historian): Part 13 time-weighted aggregates on SqlServer + QuestDb (#671)

dohertj2 referenced this issue from a commit

2026-10-11 10:17:47 -04:00

fix(historian): #671 review — raw-row cap, reverse Interpolative, SDK status rule, Partial bit

dohertj2 referenced this issue from a commit

2026-10-11 10:17:47 -04:00

Merge feat/671-time-weighted-aggregates: Part 13 time-weighted aggregates (#671)

dohertj2 commented

2026-10-11 10:17:59 -04:00

Implemented in 3b739b98 (merged to master).

New shared evaluator Driver.Historian.Sql.Common/TimeWeightedAggregator.cs (both SQL backends, identical results) for the Part 13 time-weighted family: TimeAverage, TimeAverage2, Total, Total2, Interpolative (window + 4 bound rows streamed into C#). Sloped for numeric, stepped for Boolean; server-default AggregateConfiguration (client's ignored); status = the SDK AggregateCalculator time-based rule (Uncertain time counts as Good); Partial bit set; reverse Interpolative anchored at the later time.
⚠️ Client-visible: Total is now the time-weighted integral (matching the Gateway backend), no longer SUM.
New ServerHistorian:MaxTimeWeightedRawRows (default 1,000,000) — past it ⇒ BadTooManyOperations for that node.

Live gate (docker-dev, gate 2) — PASS on SqlServer and QuestDB: sparse tag interpolates Good (not BadNoData); Total ≈ TimeAverage × seconds and Total2 = TimeAverage2 × good seconds; across an outage TimeAverage2/Total2 exclude the Bad span while TimeAverage interpolates, all-Bad buckets come back Bad; backends match (bucket times + statuses exact, values within ~5e-8 from QuestDB µs); reverse Interpolative anchored at the later time; the raw-row cap returns BadTooManyOperations with a WRN log while small windows are served.

Follow-ups filed: reverse anchoring for the other aggregates, per-sample Partial bit, AVEVA integral unit, client AggregateConfiguration, CLI aggregate list.

Implemented in `3b739b98` (merged to master). - New shared evaluator `Driver.Historian.Sql.Common/TimeWeightedAggregator.cs` (both SQL backends, identical results) for the Part 13 time-weighted family: **TimeAverage, TimeAverage2, Total, Total2, Interpolative** (window + 4 bound rows streamed into C#). Sloped for numeric, stepped for Boolean; server-default AggregateConfiguration (client's ignored); status = the SDK `AggregateCalculator` time-based rule (Uncertain time counts as Good); Partial bit set; reverse `Interpolative` anchored at the later time. - ⚠️ **Client-visible:** `Total` is now the time-weighted integral (matching the Gateway backend), no longer `SUM`. - New `ServerHistorian:MaxTimeWeightedRawRows` (default 1,000,000) — past it ⇒ `BadTooManyOperations` for that node. **Live gate (docker-dev, gate 2) — PASS on SqlServer and QuestDB:** sparse tag interpolates Good (not BadNoData); Total ≈ TimeAverage × seconds and Total2 = TimeAverage2 × good seconds; across an outage TimeAverage2/Total2 exclude the Bad span while TimeAverage interpolates, all-Bad buckets come back Bad; backends match (bucket times + statuses exact, values within ~5e-8 from QuestDB µs); reverse Interpolative anchored at the later time; the raw-row cap returns BadTooManyOperations with a WRN log while small windows are served. Follow-ups filed: reverse anchoring for the other aggregates, per-sample Partial bit, AVEVA integral unit, client AggregateConfiguration, CLI aggregate list.

dohertj2 closed this issue

2026-10-11 10:18:00 -04:00

dohertj2 referenced this issue

2026-10-11 10:18:14 -04:00

Time-weighted aggregates (#671) follow-ups: reverse anchoring, Partial bit, integral unit, AggregateConfiguration, CLI list #677

dohertj2 referenced this issue

2026-10-11 11:26:41 -04:00

Time-weighted aggregates: forward differences from the SDK AggregateCalculator (TimeAverage/Total status, Total at start of data, Partial on truncated) #680

dohertj2 referenced this issue

2026-10-12 08:38:37 -04:00

Time-weighted aggregates: forward differences from the SDK AggregateCalculator (TimeAverage/Total status, Total at start of data, Partial on truncated) #680

Sign in to join this conversation.