AVG

Window function

Description

The AVG window function returns the average (arithmetic mean) of the input expression values.

(Redshift SQL Language Reference AVG function)

This function is fully supported in Snowflake.

For more information about quoted identifiers in functions, click here.

Grammar Syntax

AVG ( [ALL ] expression ) OVER
(
[ PARTITION BY expr_list ]
[ ORDER BY order_list 
                        frame_clause ]
)

Sample Source Patterns

Setup data

Redshift

CREATE TABLE example_table (
    my_smallint_column SMALLINT,
    my_integer_column INTEGER,
    my_bigint_column BIGINT,
    my_numeric_column NUMERIC,
    my_decimal_column DECIMAL,
    my_real_column REAL,
    my_double_precision_column DOUBLE PRECISION,
    my_super_column SUPER
);

INSERT INTO example_table (
    my_smallint_column,
    my_integer_column,
    my_bigint_column,
    my_numeric_column,
    my_decimal_column,
    my_real_column,
    my_double_precision_column,
    my_super_column
)
VALUES
    (1, 100, 10000000000, 123.45, 678.90, 3.14, 2.71828, 123),
    (2, 200, 20000000000, 234.56, 789.01, 2.71, 3.14159, 456),
    (3, 300, 30000000000, 345.67, 890.12, 1.41, 1.61803, 789),
    (4, 400, 40000000000, 456.78, 901.23, 1.61, 1.41421, 101112),
    (5, 500, 50000000000, 567.89, 123.45, 2.17, 3.14159, 131415);

Snowflake

CREATE TABLE example_table (
    my_smallint_column SMALLINT,
    my_integer_column INTEGER,
    my_bigint_column BIGINT,
    my_numeric_column NUMERIC,
    my_decimal_column DECIMAL,
    my_real_column REAL,
    my_double_precision_column DOUBLE PRECISION,
    my_super_column VARIANT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "11/27/2024",  "domain": "test" }}';

INSERT INTO example_table (
    my_smallint_column,
    my_integer_column,
    my_bigint_column,
    my_numeric_column,
    my_decimal_column,
    my_real_column,
    my_double_precision_column,
    my_super_column
)
VALUES
    (1, 100, 10000000000, 123.45, 678.90, 3.14, 2.71828, 123),
    (2, 200, 20000000000, 234.56, 789.01, 2.71, 3.14159, 456),
    (3, 300, 30000000000, 345.67, 890.12, 1.41, 1.61803, 789),
    (4, 400, 40000000000, 456.78, 901.23, 1.61, 1.41421, 101112),
    (5, 500, 50000000000, 567.89, 123.45, 2.17, 3.14159, 131415);

Input Code:

Redshift

SELECT
    my_smallint_column,
    AVG(my_integer_column) OVER (PARTITION BY my_smallint_column) AS avg_integer_column,
    AVG(my_numeric_column) OVER () AS avg_numeric_column_all,
    AVG(my_decimal_column) OVER (PARTITION BY my_smallint_column ORDER BY my_integer_column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg_decimal_column
FROM
    example_table
ORDER BY my_smallint_column
LIMIT 3;

Result

my_smallint_column	avg_integer_column	avg_numeric_column_all	cumulative_avg_decimal_column
1	100	345	679
2	200	345	789
3	300	345	890

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    my_smallint_column,
    AVG(my_integer_column) OVER (PARTITION BY my_smallint_column) AS avg_integer_column,
    AVG(my_numeric_column) OVER () AS avg_numeric_column_all,
    AVG(my_decimal_column) OVER (PARTITION BY my_smallint_column ORDER BY my_integer_column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg_decimal_column
FROM
    example_table
ORDER BY my_smallint_column
LIMIT 3;

Result

my_smallint_column	avg_integer_column	avg_numeric_column_all	cumulative_avg_decimal_column
1	100	345	679
2	200	345	789
3	300	345	890

Note

AVG, depending on the data type, can behave differently in terms of rounding and formatting, which may result in different precision or decimal places when comparing Redshift with Snowflake.

Related EWIs

There are no known issues.