mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-11 07:42:54 +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>
50 lines
1.8 KiB
SQL
50 lines
1.8 KiB
SQL
-- Migrated from DuckDB test: test/sql/aggregate/aggregates/test_stddev.test
|
|
-- Test STDDEV aggregations
|
|
|
|
CREATE TABLE stddev_test(val INTEGER, grp INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
INSERT INTO stddev_test VALUES (42, 1, 1000), (43, 1, 2000), (42, 2, 3000), (1000, 2, 4000), (NULL, 1, 5000), (NULL, 3, 6000);
|
|
|
|
SELECT stddev_samp(1);
|
|
|
|
SELECT var_samp(1);
|
|
|
|
-- stddev_samp
|
|
SELECT round(stddev_samp(val), 1) FROM stddev_test;
|
|
|
|
SELECT round(stddev_samp(val), 1) FROM stddev_test WHERE val IS NOT NULL;
|
|
|
|
SELECT grp, sum(val), round(stddev_samp(val), 1), min(val) FROM stddev_test GROUP BY grp ORDER BY grp;
|
|
|
|
SELECT grp, sum(val), round(stddev_samp(val), 1), min(val) FROM stddev_test WHERE val IS NOT NULL GROUP BY grp ORDER BY grp;
|
|
|
|
-- stddev_pop
|
|
SELECT round(stddev_pop(val), 1) FROM stddev_test;
|
|
|
|
SELECT round(stddev_pop(val), 1) FROM stddev_test WHERE val IS NOT NULL;
|
|
|
|
SELECT grp, sum(val), round(stddev_pop(val), 1), min(val) FROM stddev_test GROUP BY grp ORDER BY grp;
|
|
|
|
SELECT grp, sum(val), round(stddev_pop(val), 1), min(val) FROM stddev_test WHERE val IS NOT NULL GROUP BY grp ORDER BY grp;
|
|
|
|
-- var_samp
|
|
SELECT round(var_samp(val), 1) FROM stddev_test;
|
|
|
|
SELECT round(var_samp(val), 1) FROM stddev_test WHERE val IS NOT NULL;
|
|
|
|
SELECT grp, sum(val), round(var_samp(val), 1), min(val) FROM stddev_test GROUP BY grp ORDER BY grp;
|
|
|
|
SELECT grp, sum(val), round(var_samp(val), 1), min(val) FROM stddev_test WHERE val IS NOT NULL GROUP BY grp ORDER BY grp;
|
|
|
|
-- var_pop
|
|
SELECT round(var_pop(val), 1) FROM stddev_test;
|
|
|
|
SELECT round(var_pop(val), 1) FROM stddev_test WHERE val IS NOT NULL;
|
|
|
|
SELECT grp, sum(val), round(var_pop(val), 2), min(val) FROM stddev_test GROUP BY grp ORDER BY grp;
|
|
|
|
SELECT grp, sum(val), round(var_pop(val), 2), min(val) FROM stddev_test WHERE val IS NOT NULL GROUP BY grp ORDER BY grp;
|
|
|
|
-- cleanup
|
|
DROP TABLE stddev_test;
|