invest-insights: rate_v caps at 1/14400 — part_rate is session-cumulative but wrapped in tumble(1s)

[gangtao]

Background

Follow-up to #46, which fixed rate_v returning all zeros (correlated rand() columns in the random streams). After that fix, rate_v emits nonzero rates — but the values can never be meaningful in the current design.

Problem

The part_rate JS UDAF computes:

rate: function() {
  ...
  return this.T / (4 * 60 * 60);
}

T counts the number of seconds in which the order-book spread condition held, and dividing by 4*60*60 means the intended reading is "fraction of a 4-hour trading session in violation" — a cumulative, session-long counter. The UDF even preserves this.T across finalize() calls (its initialize() reset deliberately skips T), which only makes sense when the same aggregate instance lives across many emits, i.e. a global aggregation with periodic emits.

But 017_rate_v.sql wraps it in a 1-second tumble window:

FROM tumble(cte, 1s)
GROUP BY SecurityId, StrategyId, window_start;

Tumble aggregate state is per-window, so each window gets a fresh UDF instance: T can only ever be 0 or 1, and rate caps at 1/14400 ≈ 0.0000694. Verified on a fresh 1.0.1 install (Timeplus 3.3.1): over 3,000 group-windows, every nonzero rate was exactly 0.0000694.

Suggested fix

Rewrite rate_v as a global aggregation so T accumulates across the session, e.g.:

SELECT SecurityId, StrategyId,
       part_rate(OrderId, market, Side, Quantity, CumQuantity, Price, OrdStatus, minReportBalance, minSpread) AS rate,
       ...
FROM cte
GROUP BY SecurityId, StrategyId
EMIT PERIODIC 1s;

This changes the view's output shape (no window_start), so the dashboard panel and the app version need to be updated with it. Deciding whether T should also decay/reset daily (the 4h constant assumes one trading session) is part of the design work.

Related observation

While testing, a streaming query on rate_v also died with UDF_MEMORY_THRESHOLD_EXCEEDED — the JS UDAF's V8 heap exceeded the default javascript_max_memory_bytes (200 MB) with ~1,600 groups (200 securities × 8 strategies) each holding order Maps. A global-aggregation rewrite keeps 1,600 long-lived instances, so it may need the limit raised or the per-group state bounded (e.g. evict filled/cancelled orders more aggressively in process).

🤖 Generated with Claude Code

https://claude.ai/code/session_01NoHgtTNijUH6HLkYSEHXPg