Window statistics migration¶
A dedicated API being absent does not mean its mathematical result is impossible. Translate a well-defined profile first, then test all result values, NULLs and boundaries. Measure any performance claim separately.
Execute 17 composed statistical profiles¶
The complete window_rewrites.py example runs one MatrixOne SQL query over a ten-row fixture with two partitions. It uses an ordinal self-join to build a four-row lookback, then computes centered moments and deterministic extreme positions.
Install SciPy 1.17.0 alongside the basic dependencies, and run:
python window_rewrites.py
Expected output:
17 mathematical profiles: complete SQL/NumPy/SciPy comparison passed on 10 rows.
Every returned value is compared with an independent NumPy/SciPy calculation using rtol=atol=1e-7, including NULL positions. This tolerance applies to this small numerical fixture, not to source IDs, times or all workloads.
Defined profiles¶
Dedicated DolphinDB SQL name |
Composed profile in this example |
|---|---|
sum2 |
Sum of squares of non-NULL x values. |
imin / iminLast |
Zero-based frame-relative position of the earliest/latest tied minimum x. |
imax / imaxLast |
Zero-based frame-relative position of the earliest/latest tied maximum x. |
skew |
Biased standardized third centered moment. |
kurtosis |
Biased Pearson fourth standardized moment; not excess kurtosis. |
firstNot / lastNot |
First/last non-NULL x in the ordered frame. |
prod |
Non-NULL product, using log magnitude and sign parity; all-NULL returns NULL. |
wavg / wsum |
Weighted average/sum over valid x, with observed weights in this fixture. |
corr |
Pearson correlation over paired x/y observations. |
covar |
Sample covariance, denominator n-1. |
beta |
Regression slope of y on x, centered cross-product divided by centered x sum-of-squares. |
atImax / atImin |
x at the earliest row having maximum/minimum y; the selected x can itself be NULL. |
The fixture has finite y and positive weights, non-NULL x may be negative, and ordering ties have a unique row-key tie-breaker. Variance degeneracy is defined by a 1e-12 centered-sum-of-squares threshold in the script. Generalize only after defining paired NULL policy, weight rules, extreme ties and the numerical domain.
The log-product formulation is approximate and can overflow/underflow. Centered moment arithmetic and long self-join frames also require numerical and resource checks at real scale. This recipe proves the stated finite-input profiles; it does not reproduce every DolphinDB parameter, matrix/array shape or incremental kernel.
Engine-specific boundaries¶
ClickHouse lagInFrame/leadInFrame navigate within the selected frame; ordinary LAG/LEAD navigate the partition. nonNegativeDerivative also has a specific time-unit and counter-reset contract. Do not substitute a similar-looking function without matching those contracts.
DolphinDB moving/time/cumulative families add policies such as minPeriods, left-open/right-closed time boundaries, peer-time visibility, reset behavior, local rank and TopN tie selection. EWM requires explicit adjust/ignoreNA/decay conventions.
The tested MO revision rejected GROUPS, EXCLUDE, IGNORE NULLS, window DISTINCT and FILTER syntax. See the feature roadmap. Its native INTERVAL/SLIDING/FILL support is a separate capability.
The dedicated NULL-default correctness problem in LAG/LEAD is tracked in the existing issue. Typed NULL was not a correct workaround in the tested fixture. Retest the desired API signature and complete output on your release.
DolphinDB API comparison in the larger study used official documentation, not a live DolphinDB service. No cross-engine runtime-performance parity is established here.