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

GROUPING

HLL_COUNT_BUILD

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Examples

​Syntax

​Parameters

​Return Type

​Examples

Syntax

Parameters

Return Type

Examples