HASH_AGG

Reference material for HASH_AGG

Calculates a hash value over all rows on a list of arguments. When * is specified as an argument - calculates a hash aggregation over all columns in the input. Performing a hash aggregation operation is useful for warming up table data or to check if the same values exist in two different tables.

Alias: CHECKSUM

Syntax

HASH_AGG( <expression1> [, <expression2>] [, <expression3>] [, ...<expressionN>] ) [FILTER ([WHERE] <condition>)]
HASH_AGG(*) [FILTER ([WHERE] <condition>)]

Parameters

Parameter	Description	Supported input types
<expression>	A column name for specific results for the HASH_AGG function to be applied to	Any <column> name
<condition>	An optional boolean expression to filter rows used in aggregation	BOOL

Return Type

BIGINT

Examples

The following examples use a tournament_information table:

SELECT name, prizedollars, tournamentid
FROM (VALUES
    ('The Snow Park Grand Prix', 20903.0, 1),
    ('The Acceleration Championship', 19274.0, 2),
    ('The Acceleration Trials', 13877.0, 3)
) AS tournament_information(name, prizedollars, tournamentid);

name text	prizedollars double	tournamentid int
The Snow Park Grand Prix	20903	1
The Acceleration Championship	19274	2
The Acceleration Trials	13877	3

Rows: 3Execution time: 43.36ms

The following example calculates a hash aggregation over all columns of a tournament table:

SELECT HASH_AGG(*) FROM (VALUES
    ('The Snow Park Grand Prix', 20903.0, 1),
    ('The Acceleration Championship', 19274.0, 2),
    ('The Acceleration Trials', 13877.0, 3)
) AS tournament_information(name, prizedollars, tournamentid);

hash_agg long
7652934541320494802

Rows: 1Execution time: 6.94ms

The following example calculates a hash aggregation over prizedollars and tournamentid only:

SELECT HASH_AGG(prizedollars, tournamentid) FROM (VALUES
    ('The Snow Park Grand Prix', 20903.0, 1),
    ('The Acceleration Championship', 19274.0, 2),
    ('The Acceleration Trials', 13877.0, 3)
) AS tournament_information(name, prizedollars, tournamentid);

hash_agg long
3058600455882068351

Rows: 1Execution time: 6.64ms