mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-07 22:02:56 +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>
120 lines
2.7 KiB
Plaintext
120 lines
2.7 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/aggregate/aggregates/test_median.test
|
|
-- Test MEDIAN aggregate
|
|
-- scalar median
|
|
SELECT median(NULL), median(1);
|
|
|
|
+--------------+------------------+
|
|
| median(NULL) | median(Int64(1)) |
|
|
+--------------+------------------+
|
|
| | 1 |
|
|
+--------------+------------------+
|
|
|
|
-- test with simple table
|
|
CREATE TABLE quantile(r INTEGER, v DOUBLE, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO quantile VALUES
|
|
(0, 0.1, 1000), (1, 0.2, 2000), (2, 0.3, 3000), (3, 0.4, 4000), (4, 0.5, 5000),
|
|
(5, 0.6, 6000), (6, 0.7, 7000), (7, 0.8, 8000), (8, 0.9, 9000), (9, 1.0, 10000),
|
|
(NULL, 0.1, 11000), (NULL, 0.5, 12000), (NULL, 0.9, 13000);
|
|
|
|
Affected Rows: 13
|
|
|
|
SELECT median(r)::VARCHAR FROM quantile;
|
|
|
|
+--------------------+
|
|
| median(quantile.r) |
|
|
+--------------------+
|
|
| 4 |
|
|
+--------------------+
|
|
|
|
SELECT median(r::FLOAT)::VARCHAR FROM quantile;
|
|
|
|
+--------------------+
|
|
| median(quantile.r) |
|
|
+--------------------+
|
|
| 4.5 |
|
|
+--------------------+
|
|
|
|
SELECT median(r::DOUBLE)::VARCHAR FROM quantile;
|
|
|
|
+--------------------+
|
|
| median(quantile.r) |
|
|
+--------------------+
|
|
| 4.5 |
|
|
+--------------------+
|
|
|
|
SELECT median(r::SMALLINT)::VARCHAR FROM quantile WHERE r < 100;
|
|
|
|
+--------------------+
|
|
| median(quantile.r) |
|
|
+--------------------+
|
|
| 4 |
|
|
+--------------------+
|
|
|
|
SELECT median(r::INTEGER)::VARCHAR FROM quantile;
|
|
|
|
+--------------------+
|
|
| median(quantile.r) |
|
|
+--------------------+
|
|
| 4 |
|
|
+--------------------+
|
|
|
|
SELECT median(r::BIGINT)::VARCHAR FROM quantile;
|
|
|
|
+--------------------+
|
|
| median(quantile.r) |
|
|
+--------------------+
|
|
| 4 |
|
|
+--------------------+
|
|
|
|
-- test with NULL values
|
|
SELECT median(NULL) FROM quantile;
|
|
|
|
+--------------+
|
|
| median(NULL) |
|
|
+--------------+
|
|
| |
|
|
+--------------+
|
|
|
|
SELECT median(42) FROM quantile;
|
|
|
|
+-------------------+
|
|
| median(Int64(42)) |
|
|
+-------------------+
|
|
| 42 |
|
|
+-------------------+
|
|
|
|
-- test with grouped data
|
|
CREATE TABLE median_groups(val INTEGER, grp INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO median_groups VALUES
|
|
(1, 1, 1000), (2, 1, 2000), (3, 1, 3000), (4, 1, 4000), (5, 1, 5000),
|
|
(10, 2, 6000), (20, 2, 7000), (30, 2, 8000), (40, 2, 9000), (50, 2, 10000),
|
|
(NULL, 3, 11000);
|
|
|
|
Affected Rows: 11
|
|
|
|
SELECT grp, median(val) FROM median_groups GROUP BY grp ORDER BY grp;
|
|
|
|
+-----+---------------------------+
|
|
| grp | median(median_groups.val) |
|
|
+-----+---------------------------+
|
|
| 1 | 3 |
|
|
| 2 | 30 |
|
|
| 3 | |
|
|
+-----+---------------------------+
|
|
|
|
-- cleanup
|
|
DROP TABLE quantile;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE median_groups;
|
|
|
|
Affected Rows: 0
|
|
|