Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/median below:

Website Navigation

• Reference
• Function and stored procedure reference
• Aggregate

MEDIAN | Snowflake Documentation

Categories:

Aggregate functions (General) , Window functions

Determines the median of a set of values.

Aggregate function

Window function

MEDIAN( <expr> ) OVER ( [ PARTITION BY <expr2> ] )

Copy

expr

The expression must evaluate to a numeric data type (INTEGER, FLOAT, DECIMAL, or equivalent).

Returns a FLOAT or DECIMAL (fixed-point) number, depending upon the input.

• If the number of non-NULL values is an odd number greater than or equal to 1, this returns the median (“center”) value of the non-NULL values.

• If the number of non-NULL values is an even number, this returns a value equal to the average of the two center values. For example, if the values are 1, 3, 5, and 20, then this returns 4 (the average of 3 and 5).

• If all values are NULL, this returns NULL.

• If the number of non-NULL values is 0, this returns NULL.

• DISTINCT is not supported for this function.

• When this function is called as a window function, it does not support:

  • An ORDER BY clause within the OVER clause.

  • Explicit window frames.

This shows how to use the function.

Create an empty table.

CREATE OR REPLACE TABLE aggr(k int, v decimal(10,2));

Copy

Get the MEDIAN value for column v. The function returns NULL because there are no rows.

SELECT MEDIAN (v) FROM aggr;
+------------+
| MEDIAN (V) |
|------------|
|       NULL |
+------------+

Copy

Insert some rows:

INSERT INTO aggr VALUES(1, 10), (1,20), (1, 21);
INSERT INTO aggr VALUES(2, 10), (2, 20), (2, 25), (2, 30);
INSERT INTO aggr VALUES(3, NULL);

Copy

Get the MEDIAN value for each group. Note that because the number of values in group k = 2 is an even number, the returned value for that group is the mid-point between the two middle numbers.

SELECT k, MEDIAN(v) FROM aggr GROUP BY k ORDER BY k;
+---+-----------+
| K | MEDIAN(V) |
|---+-----------|
| 1 |  20.00000 |
| 2 |  22.50000 |
| 3 |      NULL |
+---+-----------+

Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4