Skip to content

Commit 9ebab62

Browse files
pchintarQnJ1c2kNCg
authored andcommitted
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. (cherry picked from commit 37768b8)
1 parent 25728b0 commit 9ebab62

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
@@ -6600,6 +6600,142 @@ ORDER BY i;
66006600
3 1
66016601
4 NULL
66026602

6603+
# Covariance/correlation sliding-window regression test. Verifies correct
6604+
# results across row removals and a NULL-gap empty-frame transition.
6605+
query IRRR
6606+
SELECT
6607+
column1,
6608+
covar_pop(column2, column3) OVER (
6609+
ORDER BY column1
6610+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6611+
),
6612+
covar_samp(column2, column3) OVER (
6613+
ORDER BY column1
6614+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6615+
),
6616+
corr(column2, column3) OVER (
6617+
ORDER BY column1
6618+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6619+
)
6620+
FROM (
6621+
VALUES
6622+
(1, 10.0, 5.0),
6623+
(2, NULL, NULL),
6624+
(3, NULL, NULL),
6625+
(4, 30.0, 10.0),
6626+
(5, 40.0, 20.0),
6627+
(6, 50.0, 10.0)
6628+
);
6629+
----
6630+
1 0 NULL NULL
6631+
2 0 NULL NULL
6632+
3 NULL NULL NULL
6633+
4 0 NULL NULL
6634+
5 25 50 1
6635+
6 -25 -50 -1
6636+
6637+
# Multi-row covariance/correlation sliding-window regression test. Verifies
6638+
# correct accumulation when valid rows enter the frame after a reset.
6639+
query IRRR
6640+
SELECT
6641+
column1,
6642+
covar_pop(column2, column3) OVER (
6643+
ORDER BY column1
6644+
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
6645+
),
6646+
covar_samp(column2, column3) OVER (
6647+
ORDER BY column1
6648+
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
6649+
),
6650+
corr(column2, column3) OVER (
6651+
ORDER BY column1
6652+
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
6653+
)
6654+
FROM (
6655+
VALUES
6656+
(1, 10.0, 5.0),
6657+
(2, NULL, NULL),
6658+
(3, NULL, NULL),
6659+
(4, 30.0, 10.0),
6660+
(5, 40.0, 20.0),
6661+
(6, 50.0, 10.0)
6662+
);
6663+
----
6664+
1 0 NULL NULL
6665+
2 0 NULL NULL
6666+
3 0 NULL NULL
6667+
4 0 NULL NULL
6668+
5 25 50 1
6669+
6 0 0 0
6670+
6671+
# Covariance/correlation sliding-window regression test. Rows with NULL in
6672+
# either input column must not contribute to the aggregate state.
6673+
query IRRR
6674+
SELECT
6675+
column1,
6676+
covar_pop(column2, column3) OVER (
6677+
ORDER BY column1
6678+
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
6679+
),
6680+
covar_samp(column2, column3) OVER (
6681+
ORDER BY column1
6682+
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
6683+
),
6684+
corr(column2, column3) OVER (
6685+
ORDER BY column1
6686+
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
6687+
)
6688+
FROM (
6689+
VALUES
6690+
(1, 10.0, 5.0),
6691+
(2, 20.0, NULL),
6692+
(3, NULL, 15.0),
6693+
(4, 30.0, 10.0),
6694+
(5, 40.0, 20.0)
6695+
);
6696+
----
6697+
1 0 NULL NULL
6698+
2 0 NULL NULL
6699+
3 0 NULL NULL
6700+
4 25 50 1
6701+
5 25 50 1
6702+
6703+
# Variance/stddev sliding-window regression test. Verifies that retracting
6704+
# the last valid row resets the aggregate state.
6705+
query IRRRR
6706+
SELECT
6707+
column1,
6708+
var_pop(column2) OVER (
6709+
ORDER BY column1
6710+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6711+
),
6712+
var_samp(column2) OVER (
6713+
ORDER BY column1
6714+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6715+
),
6716+
stddev_pop(column2) OVER (
6717+
ORDER BY column1
6718+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6719+
),
6720+
stddev_samp(column2) OVER (
6721+
ORDER BY column1
6722+
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
6723+
)
6724+
FROM (
6725+
VALUES
6726+
(1, 10.0),
6727+
(2, NULL),
6728+
(3, NULL),
6729+
(4, 30.0),
6730+
(5, 40.0)
6731+
);
6732+
----
6733+
1 0 NULL 0 NULL
6734+
2 0 NULL 0 NULL
6735+
3 NULL NULL NULL NULL
6736+
4 0 NULL 0 NULL
6737+
5 25 50 5 7.071067811865
6738+
66036739
# Decimal variant — the integer-division path would otherwise panic on an
66046740
# empty frame.
66056741
query IR

0 commit comments

Comments
 (0)