Skip to content

Commit 37768b8

Browse files
authored
fix: Enable sliding window execution for covar_pop, covar_samp, and corr (apache#22764)
## Which issue does this PR close? - Closes apache#22763 . ## Rationale for this change Bounded sliding window queries using `covar_pop`, `covar_samp`, and `corr` currently fail with a `retract_batch is not implemented` error, preventing these aggregates from being used with sliding window frames. ## What changes are included in this PR? * Included `supports_retract_batch()` for the covariance and correlation accumulators. * Added SQL logic tests covering bounded sliding window execution for covariance and correlation aggregates. ## Are these changes tested? Yes. Added SQL logic tests covering: * Single-row bounded sliding frames * Multi-row bounded sliding frames for `covar_pop`, `covar_samp`, and `corr`. ## Are there any user-facing changes? Yes. `covar_pop`, `covar_samp`, and `corr` can now be used with bounded sliding window frames that previously failed. Also, no changes were made to any public APIs.
1 parent b8998c7 commit 37768b8

4 files changed

Lines changed: 159 additions & 0 deletions

File tree

‎datafusion/functions-aggregate/src/correlation.rs‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -281,6 +281,10 @@ impl Accumulator for CorrelationAccumulator {
281281
self.stddev2.retract_batch(&values[1..2])?;
282282
Ok(())
283283
}
284+
285+
fn supports_retract_batch(&self) -> bool {
286+
true
287+
}
284288
}
285289

286290
#[derive(Default)]

‎datafusion/functions-aggregate/src/covariance.rs‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -305,6 +305,14 @@ impl Accumulator for CovarianceAccumulator {
305305
_ => continue,
306306
};
307307

308+
if self.count <= 1 {
309+
self.count = 0;
310+
self.mean1 = 0.0;
311+
self.mean2 = 0.0;
312+
self.algo_const = 0.0;
313+
continue;
314+
}
315+
308316
let new_count = self.count - 1;
309317
let delta1 = self.mean1 - value1;
310318
let new_mean1 = delta1 / new_count as f64 + self.mean1;
@@ -373,4 +381,8 @@ impl Accumulator for CovarianceAccumulator {
373381
fn size(&self) -> usize {
374382
size_of_val(self)
375383
}
384+
385+
fn supports_retract_batch(&self) -> bool {
386+
true
387+
}
376388
}

‎datafusion/functions-aggregate/src/variance.rs‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -348,6 +348,13 @@ impl Accumulator for VarianceAccumulator {
348348
fn retract_batch(&mut self, values: &[ArrayRef]) -> Result<()> {
349349
let arr = as_float64_array(&values[0])?;
350350
for value in arr.iter().flatten() {
351+
if self.count <= 1 {
352+
self.count = 0;
353+
self.mean = 0.0;
354+
self.m2 = 0.0;
355+
continue;
356+
}
357+
351358
let new_count = self.count - 1;
352359
let delta1 = self.mean - value;
353360
let new_mean = delta1 / new_count as f64 + self.mean;

‎datafusion/sqllogictest/test_files/window.slt‎

Lines changed: 136 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6626,6 +6626,142 @@ ORDER BY i;
66266626
3 1
66276627
4 NULL
66286628

6629+
# Covariance/correlation sliding-window regression test. Verifies correct
6630+
# results across row removals and a NULL-gap empty-frame transition.
6631+
query IRRR
6632+
SELECT
6633+
column1,
6634+
covar_pop(column2, column3) OVER (
6635+
ORDER BY column1
6636+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6637+
),
6638+
covar_samp(column2, column3) OVER (
6639+
ORDER BY column1
6640+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6641+
),
6642+
corr(column2, column3) OVER (
6643+
ORDER BY column1
6644+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6645+
)
6646+
FROM (
6647+
VALUES
6648+
(1, 10.0, 5.0),
6649+
(2, NULL, NULL),
6650+
(3, NULL, NULL),
6651+
(4, 30.0, 10.0),
6652+
(5, 40.0, 20.0),
6653+
(6, 50.0, 10.0)
6654+
);
6655+
----
6656+
1 0 NULL NULL
6657+
2 0 NULL NULL
6658+
3 NULL NULL NULL
6659+
4 0 NULL NULL
6660+
5 25 50 1
6661+
6 -25 -50 -1
6662+
6663+
# Multi-row covariance/correlation sliding-window regression test. Verifies
6664+
# correct accumulation when valid rows enter the frame after a reset.
6665+
query IRRR
6666+
SELECT
6667+
column1,
6668+
covar_pop(column2, column3) OVER (
6669+
ORDER BY column1
6670+
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
6671+
),
6672+
covar_samp(column2, column3) OVER (
6673+
ORDER BY column1
6674+
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
6675+
),
6676+
corr(column2, column3) OVER (
6677+
ORDER BY column1
6678+
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
6679+
)
6680+
FROM (
6681+
VALUES
6682+
(1, 10.0, 5.0),
6683+
(2, NULL, NULL),
6684+
(3, NULL, NULL),
6685+
(4, 30.0, 10.0),
6686+
(5, 40.0, 20.0),
6687+
(6, 50.0, 10.0)
6688+
);
6689+
----
6690+
1 0 NULL NULL
6691+
2 0 NULL NULL
6692+
3 0 NULL NULL
6693+
4 0 NULL NULL
6694+
5 25 50 1
6695+
6 0 0 0
6696+
6697+
# Covariance/correlation sliding-window regression test. Rows with NULL in
6698+
# either input column must not contribute to the aggregate state.
6699+
query IRRR
6700+
SELECT
6701+
column1,
6702+
covar_pop(column2, column3) OVER (
6703+
ORDER BY column1
6704+
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
6705+
),
6706+
covar_samp(column2, column3) OVER (
6707+
ORDER BY column1
6708+
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
6709+
),
6710+
corr(column2, column3) OVER (
6711+
ORDER BY column1
6712+
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
6713+
)
6714+
FROM (
6715+
VALUES
6716+
(1, 10.0, 5.0),
6717+
(2, 20.0, NULL),
6718+
(3, NULL, 15.0),
6719+
(4, 30.0, 10.0),
6720+
(5, 40.0, 20.0)
6721+
);
6722+
----
6723+
1 0 NULL NULL
6724+
2 0 NULL NULL
6725+
3 0 NULL NULL
6726+
4 25 50 1
6727+
5 25 50 1
6728+
6729+
# Variance/stddev sliding-window regression test. Verifies that retracting
6730+
# the last valid row resets the aggregate state.
6731+
query IRRRR
6732+
SELECT
6733+
column1,
6734+
var_pop(column2) OVER (
6735+
ORDER BY column1
6736+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6737+
),
6738+
var_samp(column2) OVER (
6739+
ORDER BY column1
6740+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6741+
),
6742+
stddev_pop(column2) OVER (
6743+
ORDER BY column1
6744+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6745+
),
6746+
stddev_samp(column2) OVER (
6747+
ORDER BY column1
6748+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6749+
)
6750+
FROM (
6751+
VALUES
6752+
(1, 10.0),
6753+
(2, NULL),
6754+
(3, NULL),
6755+
(4, 30.0),
6756+
(5, 40.0)
6757+
);
6758+
----
6759+
1 0 NULL 0 NULL
6760+
2 0 NULL 0 NULL
6761+
3 NULL NULL NULL NULL
6762+
4 0 NULL 0 NULL
6763+
5 25 50 5 7.071067811865
6764+
66296765
# Decimal variant — the integer-division path would otherwise panic on an
66306766
# empty frame.
66316767
query IR

0 commit comments

Comments
 (0)