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

-- Migrated from DuckDB test: test/sql/aggregate/aggregates/test_avg.test
-- Test AVG aggregate function
-- scalar average
SELECT AVG(3);

+---------------+
| avg(Int64(3)) |
+---------------+
| 3.0           |
+---------------+

-- FIXME(dennis): unsupported type
-- SELECT AVG(NULL);
SELECT AVG(3::SMALLINT), AVG(NULL::SMALLINT);

+---------------+-----------+
| avg(Int64(3)) | avg(NULL) |
+---------------+-----------+
| 3.0           |           |
+---------------+-----------+

SELECT AVG(3::DOUBLE), AVG(NULL::DOUBLE);

+---------------+-----------+
| avg(Int64(3)) | avg(NULL) |
+---------------+-----------+
| 3.0           |           |
+---------------+-----------+

-- test average with table
CREATE TABLE integers(i INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO integers VALUES (1, 1000), (2, 2000), (3, 3000);

Affected Rows: 3

SELECT AVG(i), AVG(1), AVG(DISTINCT i), AVG(NULL) FROM integers;

Error: 3001(EngineExecuteQuery), Execution error: AvgAccumulator for (Null --> Float64)

SELECT AVG(i) FROM integers WHERE i > 100;

+-----------------+
| avg(integers.i) |
+-----------------+
|                 |
+-----------------+

-- empty average
CREATE TABLE vals(i INTEGER, j DOUBLE, k BIGINT, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO vals VALUES (NULL, NULL, NULL, 1000);

Affected Rows: 1

SELECT AVG(i), AVG(j), AVG(k) FROM vals;

+-------------+-------------+-------------+
| avg(vals.i) | avg(vals.j) | avg(vals.k) |
+-------------+-------------+-------------+
|             |             |             |
+-------------+-------------+-------------+

-- test with mixed values
DROP TABLE vals;

Affected Rows: 0

CREATE TABLE vals(i INTEGER, j DOUBLE, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO vals VALUES (1, 1.5, 1000), (2, 2.5, 2000), (3, 3.5, 3000), (NULL, NULL, 4000);

Affected Rows: 4

SELECT AVG(i), AVG(j) FROM vals;

+-------------+-------------+
| avg(vals.i) | avg(vals.j) |
+-------------+-------------+
| 2.0         | 2.5         |
+-------------+-------------+

-- FIXME(dennis): AVG(DISTINCT) not supported
-- https://github.com/apache/datafusion/issues/2408
-- SELECT AVG(DISTINCT i), AVG(DISTINCT j) FROM vals;
-- AVG1 state scalar calculation
SELECT avg_calc(avg_state(CAST(i AS DOUBLE))) FROM integers;

+---------------------------------+
| avg_calc(avg_state(integers.i)) |
+---------------------------------+
| 2.0                             |
+---------------------------------+

-- Merged states retain their unequal group weights.
WITH states AS (
  SELECT avg_state(CAST(i AS DOUBLE)) AS state FROM integers WHERE i < 3
  UNION ALL
  SELECT avg_state(CAST(i AS DOUBLE)) AS state FROM integers WHERE i >= 3
)
SELECT avg_calc(avg_merge(state)) FROM states;

+-----------------------------------+
| avg_calc(avg_merge(states.state)) |
+-----------------------------------+
| 2.0                               |
+-----------------------------------+

-- Empty, null-only, and null binary states calculate to NULL.
SELECT avg_calc(avg_state(CAST(i AS DOUBLE))) FROM integers WHERE i > 100;

+---------------------------------+
| avg_calc(avg_state(integers.i)) |
+---------------------------------+
|                                 |
+---------------------------------+

SELECT avg_calc(avg_state(NULL::DOUBLE));

+---------------------------+
| avg_calc(avg_state(NULL)) |
+---------------------------+
|                           |
+---------------------------+

SELECT avg_calc(NULL::BYTEA);

+----------------+
| avg_calc(NULL) |
+----------------+
|                |
+----------------+

-- Empty window frames return the canonical AVG1 state, not SQL NULL.
SELECT
    i,
    avg_state(CAST(i AS DOUBLE)) OVER (
        ORDER BY i
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS state
FROM integers;

+---+------------------------------------------+
| i | state                                    |
+---+------------------------------------------+
| 1 | 4156473100000000000000000000000000000000 |
| 2 | 415647310100000000000000000000000000f03f |
| 3 | 4156473102000000000000000000000000000840 |
+---+------------------------------------------+

-- Invalid AVG1 state propagates an error.
SELECT avg_calc(X'00');

Error: 3001(EngineExecuteQuery), Execution error: Invalid AVG1 state

-- cleanup
DROP TABLE integers;

Affected Rows: 0

DROP TABLE vals;

Affected Rows: 0