Explanation
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 ] [, ] )
2.[ WITHIN GROUP ( ) ]
3.OVER ( [ PARTITION BY ] )
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 ] [ , ... ] ) OVER ( [ PARTITION BY ] )
HASH_AGG(*) OVER ( [ PARTITION BY ] )
