JoverZhang opened a new pull request, #55940:
URL: https://github.com/apache/doris/pull/55940
### What problem does this PR solve?
Issue Number: #38975
Problem Summary:
This PR reimplements `REGR_SLOPE` and `REGR_INTERCEPT` using the
Youngs–Cramer algorithm to align with PostgreSQL.
It also extends `AggregateFunctionRegrData<T>` so it can be reused by all
`REGR_*` functions (`SXX`, `SYY`, `SXY`, `R2`, etc.).
```sql
-- dataset (PostgreSQL)
drop table if exists d_table;
create table d_table (
k1 int,
k2 int not null,
k3 bigint,
col_tinyint smallint,
col_smallint smallint,
col_int int,
col_bigint bigint,
col_largeint numeric(38,0),
col_float real,
col_double double precision
);
insert into d_table values
(1, 1, 1, 100, 10000, 1000000, 10000000000, 100000000000000000000, 3.14,
2.718281828),
(2, 2, 2, 101, 10001, 1000001, 10000000001, 100000000000000000001, 6.28,
3.141592653),
(3, 3, 3, 102, 10002, 1000002, 10000000002, 100000000000000000002, 9.42,
1.618033988);
select regr_slope(col_tinyint, col_smallint) from d_table;
-- 1.0
select regr_slope(col_smallint, col_int) from d_table;
-- 1.0
select regr_slope(col_int, col_bigint) from d_table;
-- 1.0
select regr_slope(col_bigint, col_largeint) from d_table;
-- <null>
select regr_slope(col_largeint, col_float) from d_table;
-- 0.0
select regr_slope(col_float, col_double) from d_table;
-- -2.7928921351549283
select regr_slope(col_double, col_tinyint) from d_table;
-- -0.5501239200000003
select regr_intercept(col_tinyint, col_smallint) from d_table;
-- -9900.0
select regr_intercept(col_smallint, col_int) from d_table;
-- -990000.0
select regr_intercept(col_int, col_bigint) from d_table;
-- -9999000000.0
select regr_intercept(col_bigint, col_largeint) from d_table;
-- <null>
select regr_intercept(col_largeint, col_float) from d_table;
-- 1e+20
select regr_intercept(col_float, col_double) from d_table;
-- 13.241664047161668
select regr_intercept(col_double, col_tinyint) from d_table;
-- 58.055152076333364
```
### Next step:
Refactor `REGR_SXX/REGR_SYY/REGR_SXY/REGR_R2` to reuse the same
`AggregateFunctionRegrData<T>` state `(n, sx, sy, sxx, syy, sxy)` and
Youngs–Cramer merge. This ensures consistent numerics and associative merges
across all `REGR_*` functions, and aligns results with PostgreSQL.
- `regr_sxx = sxx`
- `regr_syy = syy`
- `regr_sxy = sxy`
- `regr_r2 = (sxy * sxy) / (sxx * syy)`
### Seeking feedback
Should we use the `if constexpr` to skip unused updates?
Now, all `REGR_*` functions share the same state `(n, sx, sy, sxx, syy,
sxy)` for Youngs–Cramer. For consumers that don’t need certain terms (e.g.,
`REGR_SLOPE` doesn’t need `syy`, `REGR_SXX` only needs `sxx`), we could add
compile-time flags to skip updates for unused accumulators:
```cpp
if constexpr (NeedSXX) sxx += tmp_x * tmp_x * scale;
if constexpr (NeedSYY) syy += tmp_y * tmp_y * scale;
if constexpr (NeedSXY) sxy += tmp_x * tmp_y * scale;
```
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [x] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [x] Yes. <!-- Explain the behavior change -->
- `REGR_SLOPE/REGR_INTERCEPT` now follow PostgreSQL / SQL:2003
semantics:
- Use `slope = sxy / sxx`, `intercept = (sy - sx * sxy/sxx) / n`.
- Does this need documentation?
- [x] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR should
merge into -->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
