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

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

Website Navigation

• Reference
• Function and stored procedure reference
• Aggregate

ANY_VALUE | Snowflake Documentation

Returns some value of the expression from the group. The result is non-deterministic.

Aggregate function

ANY_VALUE( [ DISTINCT ] <expr1> )

Copy

Window function

ANY_VALUE( [ DISTINCT ] <expr1> ) OVER ( [ PARTITION BY <expr2> ] )

Copy

expr1

The input expression.

expr2

The column to partition on, if you want the result to be split into multiple partitions.

This function can return a value of any data type.

If the input expression is NULL, the function returns NULL.

• The DISTINCT keyword can be specified for this function, but it does not have any effect.

• The function doesn’t exclude NULL values. If the expression contains NULL values, the function can return a NULL value.

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

  • An ORDER BY clause within the OVER clause.

  • Explicit window frames.

ANY_VALUE can simplify and optimize the performance of GROUP BY statements. A common problem for many queries is that the result of a query with a GROUP BY clause can only contain expressions used in the GROUP BY clause itself, or results of aggregate functions. For example:

SELECT customer.id , customer.name , SUM(orders.value)
  FROM customer
  JOIN orders ON customer.id = orders.customer_id
  GROUP BY customer.id , customer.name;

Copy

In this query, the customer.name attribute needs to be in the GROUP BY to be included in the result. This is unnecessary (for example, when customer.id is known to be unique) and makes the computation possibly more complex and slower. Another option is to use an aggregate function. For example:

SELECT customer.id , MIN(customer.name) , SUM(orders.value)
  FROM customer
  JOIN orders ON customer.id = orders.customer_id
  GROUP BY customer.id;

Copy

This simplifies the GROUP BY clause, but still requires computing the MIN function, which incurs an extra cost.

With ANY_VALUE, you can execute the following query:

SELECT customer.id , ANY_VALUE(customer.name) , SUM(orders.value)
  FROM customer
  JOIN orders ON customer.id = orders.customer_id
  GROUP BY customer.id;

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