Categories: Aggregate, Window

AVG

Computes the average of a set of values.

Syntax

AVG(numeric_expression NUMERIC) → DOUBLE

• numeric_expression: The values for which to compute the average. Values can be type DOUBLE, NUMERIC, INTEGER, INTERVAL_DATE, or INTERVAL_YEAR.

Examples

AVG example

SELECT AVG(3)
-- 3.0

AVG example: val_col contains 0.6348, -1.301466

SELECT AVG("val_col");
-- -0.333333

AVG example: Window function with cumulative window frame

SELECT city, state, pop, AVG(pop)
  OVER (PARTITION BY state ORDER BY city RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 2806.341836734694
-- AKHIOK, AK, 13309, 2793.3230769230768
-- AKIACHAK, AK, 481, 2739.118556701031
-- ...

AVG example: Window function with sliding window frame

SELECT city, state, pop, AVG(pop)
  OVER (PARTITION BY state ORDER BY city ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 6378.333333333333
-- AKHIOK, AK, 13309, 4855.0
-- AKIACHAK, AK, 481, 3666.0
-- ...

Usage Notes

The AVG function supports optional PARTITION BY, ORDER_BY, and cumulative and sliding window frame subclauses. See Window Functions for more information and syntax.