Question 34 - DSA-C02 discussion

What is a Window?

A window is a group of related rows. For example, a window might be defined based on timestamps, with all rows in the same month grouped in the same window. Or a window might be defined based on location, with all rows from a particular city grouped in the same window.

A window can consist of zero, one, or multiple rows. For simplicity, Snowflake documentation usually says that a window contains multiple rows.

What is a Window Function?

A window function is any function that operates over a window of rows.

A window function is generally passed two parameters:

A row. More precisely, a window function is passed 0 or more expressions. In almost all cases, at least one of those expressions references a column in that row. (Most window functions require at least one column or expression, but a few window functions, such as some rank-related functions, do not required an explicit column or expression.)

A window of related rows that includes that row. The window can be the entire table, or a subset of the rows in the table.

For non-window functions, all arguments are usually passed explicitly to the function, for example:

MY_FUNCTION(argument1, argument2, ...)

Window functions behave differently; although the current row is passed as an argument the normal way, the window is passed through a separate clause, called an OVER clause. The syntax of the OVER clause is documented later.

LISTAGG

Returns the concatenated input values, separated by the delimiter string.

Window function

1. LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )

2. [ WITHIN GROUP ( <orderby_clause> ) ]

3. OVER ( [ PARTITION BY <expr2> ] )

HASH_AGG

Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows. HASH_AGG never returns NULL, even if no input is provided. Empty input ''hashes'' to 0.

Window function

HASH_AGG( [ DISTINCT ] <expr> [ , <expr2> ... ] ) OVER ( [ PARTITION BY <expr3> ] )

HASH_AGG(*) OVER ( [ PARTITION BY <expr3> ] )