greptimedb/tests/cases/standalone/common/aggregate/approx_median.result

-- Migrated from DuckDB test style: test approximate median
-- Test APPROX_MEDIAN function
-- Test with odd number of values
CREATE TABLE odd_test(i INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO odd_test VALUES (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000);

Affected Rows: 5

-- Should return 3 (middle value)
SELECT approx_median(i) FROM odd_test;

+---------------------------+
| approx_median(odd_test.i) |
+---------------------------+
| 3                         |
+---------------------------+

-- Test with even number of values
CREATE TABLE even_test(i INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO even_test VALUES (1, 1000), (2, 2000), (4, 3000), (5, 4000);

Affected Rows: 4

-- Should return approximately 3 (average of 2 and 4)
SELECT approx_median(i) FROM even_test;

+----------------------------+
| approx_median(even_test.i) |
+----------------------------+
| 3                          |
+----------------------------+

-- Test with larger dataset
CREATE TABLE large_test(val INTEGER, grp INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO large_test SELECT number, number % 3, number * 1000 FROM numbers LIMIT 1000;

Affected Rows: 1000

SELECT approx_median(val) FROM large_test;

+-------------------------------+
| approx_median(large_test.val) |
+-------------------------------+
| 499                           |
+-------------------------------+

-- Test with groups
SELECT grp, approx_median(val) FROM large_test GROUP BY grp ORDER BY grp;

+-----+-------------------------------+
| grp | approx_median(large_test.val) |
+-----+-------------------------------+
| 0   | 498                           |
| 1   | 499                           |
| 2   | 500                           |
+-----+-------------------------------+

-- Test with doubles
CREATE TABLE double_test(d DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO double_test VALUES
    (1.1, 1000), (2.2, 2000), (3.3, 3000), (4.4, 4000), (5.5, 5000);

Affected Rows: 5

SELECT approx_median(d) FROM double_test;

+------------------------------+
| approx_median(double_test.d) |
+------------------------------+
| 3.3                          |
+------------------------------+

-- Test with NULL values
INSERT INTO double_test VALUES (NULL, 6000);

Affected Rows: 1

SELECT approx_median(d) FROM double_test;

+------------------------------+
| approx_median(double_test.d) |
+------------------------------+
| 3.3                          |
+------------------------------+

-- Test with duplicate values
CREATE TABLE dup_test(val INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO dup_test VALUES
    (1, 1000), (1, 2000), (2, 3000), (2, 4000),
    (3, 5000), (3, 6000), (4, 7000), (4, 8000);

Affected Rows: 8

SELECT approx_median(val) FROM dup_test;

+-----------------------------+
| approx_median(dup_test.val) |
+-----------------------------+
| 2                           |
+-----------------------------+

-- Compare with exact median
SELECT median(val), approx_median(val) FROM dup_test;

+----------------------+-----------------------------+
| median(dup_test.val) | approx_median(dup_test.val) |
+----------------------+-----------------------------+
| 2                    | 2                           |
+----------------------+-----------------------------+

-- Test edge cases
-- empty result
SELECT approx_median(i) FROM odd_test WHERE i > 100;

+---------------------------+
| approx_median(odd_test.i) |
+---------------------------+
|                           |
+---------------------------+

-- Test single value
SELECT approx_median(i) FROM odd_test WHERE i = 3;

+---------------------------+
| approx_median(odd_test.i) |
+---------------------------+
| 3                         |
+---------------------------+

-- Test with negative values
CREATE TABLE neg_test(val INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO neg_test VALUES (-5, 1000), (-2, 2000), (0, 3000), (3, 4000), (7, 5000);

Affected Rows: 5

SELECT approx_median(val) FROM neg_test;

+-----------------------------+
| approx_median(neg_test.val) |
+-----------------------------+
| 0                           |
+-----------------------------+

-- cleanup
DROP TABLE odd_test;

Affected Rows: 0

DROP TABLE even_test;

Affected Rows: 0

DROP TABLE large_test;

Affected Rows: 0

DROP TABLE double_test;

Affected Rows: 0

DROP TABLE dup_test;

Affected Rows: 0

DROP TABLE neg_test;

Affected Rows: 0