mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-16 21:10:38 +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>
85 lines
4.3 KiB
Plaintext
85 lines
4.3 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/aggregate/aggregates/test_covar.test
|
|
-- Test COVAR operators (covariance)
|
|
-- Test population covariance on scalar values
|
|
SELECT COVAR_POP(3,3), COVAR_POP(NULL,3), COVAR_POP(3,NULL), COVAR_POP(NULL,NULL);
|
|
|
|
+------------------------------+--------------------------+--------------------------+----------------------+
|
|
| covar_pop(Int64(3),Int64(3)) | covar_pop(NULL,Int64(3)) | covar_pop(Int64(3),NULL) | covar_pop(NULL,NULL) |
|
|
+------------------------------+--------------------------+--------------------------+----------------------+
|
|
| 0.0 | | | |
|
|
+------------------------------+--------------------------+--------------------------+----------------------+
|
|
|
|
-- Test sample covariance on scalar values
|
|
SELECT COVAR_SAMP(3,3), COVAR_SAMP(NULL,3), COVAR_SAMP(3,NULL), COVAR_SAMP(NULL,NULL);
|
|
|
|
+-------------------------------+---------------------------+---------------------------+-----------------------+
|
|
| covar_samp(Int64(3),Int64(3)) | covar_samp(NULL,Int64(3)) | covar_samp(Int64(3),NULL) | covar_samp(NULL,NULL) |
|
|
+-------------------------------+---------------------------+---------------------------+-----------------------+
|
|
| | | | |
|
|
+-------------------------------+---------------------------+---------------------------+-----------------------+
|
|
|
|
-- Test population covariance on a set of values
|
|
CREATE TABLE integers(x INTEGER, y INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO integers VALUES
|
|
(10, NULL, 1000),
|
|
(10, 11, 2000),
|
|
(20, 22, 3000),
|
|
(25, NULL, 4000),
|
|
(30, 35, 5000);
|
|
|
|
Affected Rows: 5
|
|
|
|
SELECT COVAR_POP(x,y), COVAR_POP(x,1), COVAR_POP(1,y), COVAR_POP(x,NULL), COVAR_POP(NULL,y) FROM integers;
|
|
|
|
+----------------------------------+--------------------------------+--------------------------------+----------------------------+----------------------------+
|
|
| covar_pop(integers.x,integers.y) | covar_pop(integers.x,Int64(1)) | covar_pop(Int64(1),integers.y) | covar_pop(integers.x,NULL) | covar_pop(NULL,integers.y) |
|
|
+----------------------------------+--------------------------------+--------------------------------+----------------------------+----------------------------+
|
|
| 79.99999999999999 | 0.0 | 0.0 | | |
|
|
+----------------------------------+--------------------------------+--------------------------------+----------------------------+----------------------------+
|
|
|
|
-- Test sample covariance
|
|
SELECT COVAR_SAMP(x,y), COVAR_SAMP(x,1), COVAR_SAMP(1,y) FROM integers;
|
|
|
|
+-----------------------------------+---------------------------------+---------------------------------+
|
|
| covar_samp(integers.x,integers.y) | covar_samp(integers.x,Int64(1)) | covar_samp(Int64(1),integers.y) |
|
|
+-----------------------------------+---------------------------------+---------------------------------+
|
|
| 119.99999999999999 | 0.0 | 0.0 |
|
|
+-----------------------------------+---------------------------------+---------------------------------+
|
|
|
|
-- Test grouped covariance
|
|
CREATE TABLE covar_data(grp INTEGER, x DOUBLE, y DOUBLE, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO covar_data VALUES
|
|
(1, 1.0, 2.0, 1000),
|
|
(1, 2.0, 4.0, 2000),
|
|
(1, 3.0, 6.0, 3000),
|
|
(2, 10.0, 5.0, 4000),
|
|
(2, 20.0, 10.0, 5000),
|
|
(2, 30.0, 15.0, 6000);
|
|
|
|
Affected Rows: 6
|
|
|
|
SELECT grp, COVAR_POP(x, y), COVAR_SAMP(x, y) FROM covar_data GROUP BY grp ORDER BY grp;
|
|
|
|
+-----+--------------------------------------+---------------------------------------+
|
|
| grp | covar_pop(covar_data.x,covar_data.y) | covar_samp(covar_data.x,covar_data.y) |
|
|
+-----+--------------------------------------+---------------------------------------+
|
|
| 1 | 1.3333333333333333 | 2.0 |
|
|
| 2 | 33.333333333333336 | 50.0 |
|
|
+-----+--------------------------------------+---------------------------------------+
|
|
|
|
-- cleanup
|
|
DROP TABLE integers;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE covar_data;
|
|
|
|
Affected Rows: 0
|
|
|