mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-08 22:32:55 +00:00
c6e5552f05
* test: migrate aggregation tests from duckdb, part4 Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: tests Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: rename tests Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: comments Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: ignore zero weights test Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: remove duplicated sql Signed-off-by: Dennis Zhuang <killme2008@gmail.com> --------- Signed-off-by: Dennis Zhuang <killme2008@gmail.com>
121 lines
4.9 KiB
Plaintext
121 lines
4.9 KiB
Plaintext
-- 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
|
|
|