-- Migrated from DuckDB test: test/sql/aggregate/aggregates/test_regression.test
-- Test REGRESSION functions
-- Test REGR_SLOPE, REGR_INTERCEPT, REGR_R2
CREATE TABLE regr_test(x DOUBLE, y DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

-- Linear relationship: y = 2x + 1
INSERT INTO regr_test VALUES
    (1.0, 3.0, 1000), (2.0, 5.0, 2000), (3.0, 7.0, 3000),
    (4.0, 9.0, 4000), (5.0, 11.0, 5000);

Affected Rows: 5

-- Test regression slope (should be close to 2)
SELECT REGR_SLOPE(y, x) FROM regr_test;

+-------------------------------------+
| regr_slope(regr_test.y,regr_test.x) |
+-------------------------------------+
| 2.0                                 |
+-------------------------------------+

-- Test regression intercept (should be close to 1)
SELECT REGR_INTERCEPT(y, x) FROM regr_test;

+-----------------------------------------+
| regr_intercept(regr_test.y,regr_test.x) |
+-----------------------------------------+
| 1.0                                     |
+-----------------------------------------+

-- Test R-squared (should be close to 1 for perfect fit)
SELECT REGR_R2(y, x) FROM regr_test;

+----------------------------------+
| regr_r2(regr_test.y,regr_test.x) |
+----------------------------------+
| 1.0                              |
+----------------------------------+

-- Test REGR_COUNT (number of non-null pairs)
SELECT REGR_COUNT(y, x) FROM regr_test;

+-------------------------------------+
| regr_count(regr_test.y,regr_test.x) |
+-------------------------------------+
| 5                                   |
+-------------------------------------+

-- Test REGR_SXX, REGR_SYY, REGR_SXY
SELECT REGR_SXX(y, x), REGR_SYY(y, x), REGR_SXY(y, x) FROM regr_test;

+-----------------------------------+-----------------------------------+-----------------------------------+
| regr_sxx(regr_test.y,regr_test.x) | regr_syy(regr_test.y,regr_test.x) | regr_sxy(regr_test.y,regr_test.x) |
+-----------------------------------+-----------------------------------+-----------------------------------+
| 10.0                              | 40.0                              | 20.0                              |
+-----------------------------------+-----------------------------------+-----------------------------------+

-- Test REGR_AVGX, REGR_AVGY
SELECT REGR_AVGX(y, x), REGR_AVGY(y, x) FROM regr_test;

+------------------------------------+------------------------------------+
| regr_avgx(regr_test.y,regr_test.x) | regr_avgy(regr_test.y,regr_test.x) |
+------------------------------------+------------------------------------+
| 3.0                                | 7.0                                |
+------------------------------------+------------------------------------+

-- Test with noisy data
CREATE TABLE regr_noisy(x DOUBLE, y DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO regr_noisy VALUES
    (1.0, 3.1, 1000), (2.0, 4.9, 2000), (3.0, 7.2, 3000),
    (4.0, 8.8, 4000), (5.0, 11.1, 5000);

Affected Rows: 5

SELECT REGR_SLOPE(y, x), REGR_INTERCEPT(y, x), REGR_R2(y, x) FROM regr_noisy;

+---------------------------------------+-------------------------------------------+------------------------------------+
| regr_slope(regr_noisy.y,regr_noisy.x) | regr_intercept(regr_noisy.y,regr_noisy.x) | regr_r2(regr_noisy.y,regr_noisy.x) |
+---------------------------------------+-------------------------------------------+------------------------------------+
| 1.9900000000000002                    | 1.049999999999998                         | 0.9973053289009772                 |
+---------------------------------------+-------------------------------------------+------------------------------------+

-- Test with groups
CREATE TABLE regr_groups(grp INTEGER, x DOUBLE, y DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO regr_groups VALUES
    (1, 1.0, 3.0, 1000), (1, 2.0, 5.0, 2000), (1, 3.0, 7.0, 3000),
    (2, 1.0, 2.0, 4000), (2, 2.0, 4.0, 5000), (2, 3.0, 6.0, 6000);

Affected Rows: 6

SELECT grp, REGR_SLOPE(y, x), REGR_INTERCEPT(y, x), REGR_R2(y, x)
FROM regr_groups GROUP BY grp ORDER BY grp;

+-----+-----------------------------------------+---------------------------------------------+--------------------------------------+
| grp | regr_slope(regr_groups.y,regr_groups.x) | regr_intercept(regr_groups.y,regr_groups.x) | regr_r2(regr_groups.y,regr_groups.x) |
+-----+-----------------------------------------+---------------------------------------------+--------------------------------------+
| 1   | 2.0                                     | 1.0                                         | 1.0                                  |
| 2   | 2.0                                     | 0.0                                         | 1.0                                  |
+-----+-----------------------------------------+---------------------------------------------+--------------------------------------+

DROP TABLE regr_test;

Affected Rows: 0

DROP TABLE regr_noisy;

Affected Rows: 0

DROP TABLE regr_groups;

Affected Rows: 0