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

-- Migrated from DuckDB test: test/sql/aggregate/aggregates/test_approx_quantile.test
-- Test approx_percentile_cont function instead of approx_quantile
-- Test basic approximate quantile
CREATE TABLE approx_test(i INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO approx_test SELECT number, number * 1000 FROM numbers LIMIT 1000;

Affected Rows: 1000

-- Test approx_percentile_cont
-- median
SELECT approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY i) FROM approx_test;

+----------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.5)) WITHIN GROUP [approx_test.i ASC NULLS LAST] |
+----------------------------------------------------------------------------------+
| 499                                                                              |
+----------------------------------------------------------------------------------+

-- first quartile
SELECT approx_percentile_cont(0.25) WITHIN GROUP (ORDER BY i) FROM approx_test;

+-----------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.25)) WITHIN GROUP [approx_test.i ASC NULLS LAST] |
+-----------------------------------------------------------------------------------+
| 249                                                                               |
+-----------------------------------------------------------------------------------+

-- third quartile
SELECT approx_percentile_cont(0.75) WITHIN GROUP (ORDER BY i) FROM approx_test;

+-----------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.75)) WITHIN GROUP [approx_test.i ASC NULLS LAST] |
+-----------------------------------------------------------------------------------+
| 749                                                                               |
+-----------------------------------------------------------------------------------+

-- 95th percentile
SELECT approx_percentile_cont(0.95) WITHIN GROUP (ORDER BY i) FROM approx_test;

+-----------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.95)) WITHIN GROUP [approx_test.i ASC NULLS LAST] |
+-----------------------------------------------------------------------------------+
| 949                                                                               |
+-----------------------------------------------------------------------------------+

-- Test approx_percentile_cont DESC
-- median
SELECT approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY i DESC) FROM approx_test;

+------------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.5)) WITHIN GROUP [approx_test.i DESC NULLS FIRST] |
+------------------------------------------------------------------------------------+
| 499                                                                                |
+------------------------------------------------------------------------------------+

-- first quartile
SELECT approx_percentile_cont(0.25) WITHIN GROUP (ORDER BY i DESC) FROM approx_test;

+-------------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.25)) WITHIN GROUP [approx_test.i DESC NULLS FIRST] |
+-------------------------------------------------------------------------------------+
| 749                                                                                 |
+-------------------------------------------------------------------------------------+

-- third quartile
SELECT approx_percentile_cont(0.75) WITHIN GROUP (ORDER BY i DESC) FROM approx_test;

+-------------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.75)) WITHIN GROUP [approx_test.i DESC NULLS FIRST] |
+-------------------------------------------------------------------------------------+
| 249                                                                                 |
+-------------------------------------------------------------------------------------+

-- 95th percentile
SELECT approx_percentile_cont(0.95) WITHIN GROUP (ORDER BY i DESC) FROM approx_test;

+-------------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.95)) WITHIN GROUP [approx_test.i DESC NULLS FIRST] |
+-------------------------------------------------------------------------------------+
| 49                                                                                  |
+-------------------------------------------------------------------------------------+

-- Test with different data types
CREATE TABLE approx_double(d DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO approx_double SELECT number * 1.5, number * 1000 FROM numbers LIMIT 1000;

Affected Rows: 1000

SELECT approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY d) FROM approx_double;

+------------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.5)) WITHIN GROUP [approx_double.d ASC NULLS LAST] |
+------------------------------------------------------------------------------------+
| 748.875                                                                            |
+------------------------------------------------------------------------------------+

SELECT approx_percentile_cont(0.9) WITHIN GROUP (ORDER BY d) FROM approx_double;

+------------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.9)) WITHIN GROUP [approx_double.d ASC NULLS LAST] |
+------------------------------------------------------------------------------------+
| 1349.25                                                                            |
+------------------------------------------------------------------------------------+

-- Test with groups
CREATE TABLE approx_groups(grp INTEGER, val INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO approx_groups SELECT
    number % 3 as grp,
    number,
    number * 1000
FROM numbers LIMIT 300;

Affected Rows: 300

SELECT grp, approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY val)
FROM approx_groups GROUP BY grp ORDER BY grp;

+-----+--------------------------------------------------------------------------------------+
| grp | approx_percentile_cont(Float64(0.5)) WITHIN GROUP [approx_groups.val ASC NULLS LAST] |
+-----+--------------------------------------------------------------------------------------+
| 0   | 148                                                                                  |
| 1   | 149                                                                                  |
| 2   | 150                                                                                  |
+-----+--------------------------------------------------------------------------------------+

-- Test with NULL values
INSERT INTO approx_test VALUES (NULL, 1001000);

Affected Rows: 1

SELECT approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY i) FROM approx_test;

+----------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0.5)) WITHIN GROUP [approx_test.i ASC NULLS LAST] |
+----------------------------------------------------------------------------------+
| 499                                                                              |
+----------------------------------------------------------------------------------+

-- Test edge cases
-- should be close to min
SELECT approx_percentile_cont(0.0) WITHIN GROUP (ORDER BY i) FROM approx_test;

+--------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0)) WITHIN GROUP [approx_test.i ASC NULLS LAST] |
+--------------------------------------------------------------------------------+
| 0                                                                              |
+--------------------------------------------------------------------------------+

SELECT approx_percentile_cont(1.0) WITHIN GROUP (ORDER BY i DESC) FROM approx_test;

+----------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(1)) WITHIN GROUP [approx_test.i DESC NULLS FIRST] |
+----------------------------------------------------------------------------------+
| 0                                                                                |
+----------------------------------------------------------------------------------+

-- should be close to max
SELECT approx_percentile_cont(1.0) WITHIN GROUP (ORDER BY i) FROM approx_test;

+--------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(1)) WITHIN GROUP [approx_test.i ASC NULLS LAST] |
+--------------------------------------------------------------------------------+
| 999                                                                            |
+--------------------------------------------------------------------------------+

SELECT approx_percentile_cont(0.0) WITHIN GROUP (ORDER BY i DESC) FROM approx_test;

+----------------------------------------------------------------------------------+
| approx_percentile_cont(Float64(0)) WITHIN GROUP [approx_test.i DESC NULLS FIRST] |
+----------------------------------------------------------------------------------+
| 999                                                                              |
+----------------------------------------------------------------------------------+

DROP TABLE approx_test;

Affected Rows: 0

DROP TABLE approx_double;

Affected Rows: 0

DROP TABLE approx_groups;

Affected Rows: 0