Catégories :

Fonctions d’agrégation , Fonctions de fenêtre

HASH_AGG

Renvoie une valeur de hachage globale signée de 64 bits sur l’ensemble (non ordonné) des lignes d’entrée. HASH_AGG ne renvoie jamais NULL, même si aucune entrée n’est fournie. Vider l’entrée « hachage » et régler sur 0.

Une utilisation des fonctions de hachage d’agrégation consiste à détecter les modifications apportées à un ensemble de valeurs sans comparer les anciennes et les nouvelles valeurs. HASH_AGG peut calculer une seule valeur de hachage basée sur plusieurs entrées ; tout changement apporté à l’une des entrées devrait entraîner un changement à la sortie de la fonction HASH_AGG. La comparaison de deux listes de valeurs nécessite généralement le tri des deux listes, mais HASH_AGG produit la même valeur quel que soit l’ordre des entrées. Étant donné que les valeurs n’ont pas besoin d’être triées pour HASH_AGG, les performances sont généralement beaucoup plus rapides.

Note

HASH_AGG n’est pas une fonction de hachage cryptographique et ne doit pas être utilisée en tant que telle.

Pour des raisons cryptographiques, utilisez les familles de fonctions SHA (dans Fonctions de chaîne et fonctions binaires).

Voir aussi:

HASH

Syntaxe

Fonction d’agrégation

HASH_AGG( [ DISTINCT ] <expr> [ , <expr2> ... ] )

HASH_AGG(*)
Copy

Fonction de fenêtre

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

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

Arguments

exprN

L’expression peut être une expression générale de tout type de données Snowflake.

expr2

Vous pouvez inclure des expressions supplémentaires.

expr3

La colonne sur laquelle partitionner, si vous voulez que le résultat soit divisé en plusieurs fenêtres.

*

Renvoie une valeur de hachage agrégée sur toutes les colonnes pour tous les enregistrements, y compris les enregistrements avec des valeurs NULL. Vous pouvez spécifier le caractère générique pour la fonction d’agrégation et la fonction de fenêtre.

Lorsque vous transmettez un caractère générique à la fonction, vous pouvez qualifier le caractère générique avec le nom ou l’alias de la table. Par exemple, pour transmettre toutes les colonnes de la table nommée mytable, précisez les éléments suivants :

(mytable.*)
Copy

Vous pouvez également utiliser les mots-clés ILIKE et EXCLUDE pour le filtrage :

  • ILIKE filtre les noms de colonnes qui correspondent au motif spécifié. Un seul motif est autorisé. Par exemple :

    (* ILIKE 'col1%')
    Copy

  • EXCLUDE filtre les noms de colonnes qui ne correspondent pas à la / aux colonnes spécifiées. Par exemple :

    (* EXCLUDE col1)

(* EXCLUDE (col1, col2))
    Copy

Les qualificatifs sont valides lorsque vous utilisez ces mots-clés. L’exemple suivant utilise le mot-clé ILIKE pour filtrer toutes les colonnes qui correspondent au motif col1% dans la table mytable :

(mytable.* ILIKE 'col1%')
Copy

Les mots-clés ILIKE et EXCLUDE ne peuvent pas être combinés dans un seul appel de fonction.

Pour cette fonction, les mots-clés ILIKE et EXCLUDE ne sont valables que dans une liste SELECT ou une clause GROUP BY.

Pour plus d’informations sur les mots-clés ILIKE et EXCLUDE, voir la section « Paramètres » dans SELECT.

Renvoie

Renvoie une valeur signée de 64 bits sous la forme NUMBER(19,0).

HASH_AGG ne renvoie jamais NULL, même pour les entrées NULL.

Notes sur l’utilisation

  • HASH_AGG calcule une « empreinte » sur l’ensemble d’une table ou d’un résultat de requête ou d’une fenêtre. Tout changement à l’entrée influencera le résultat de HASH_AGG avec une très grande probabilité. Ceci peut être utilisé pour détecter rapidement les modifications apportées au contenu des tables ou aux résultats des requêtes.

    Notez qu’il est possible, quoique très improbable, que deux tables d’entrée différentes produisent le même résultat pour HASH_AGG. Si vous devez vous assurer que deux tables ou résultats de requête qui produisent le même résultat HASH_AGG contiennent réellement les mêmes données, vous devez quand même comparer l’égalité des données (par exemple, en utilisant l’opérateur MINUS). Pour plus de détails, voir Définir les opérateurs.

  • HASH_AGG n’est pas sensible à l’ordre (c’est-à-dire que l’ordre des lignes dans une table d’entrée ou le résultat d’une requête n’influence pas le résultat de HASH_AGG). Cependant, la modification de l’ordre des colonnes d’entrée modifie le résultat.

  • HASH_AGG hache des lignes d’entrée individuelles à l’aide de la fonction HASH. Les principales caractéristiques de cette fonction sont reportées sur HASH_AGG. En particulier, HASH_AGG est stable en ce sens que deux lignes qui sont comparables et qui ont des types compatibles seront hachées à la même valeur de façon garantie (c’est-à-dire qu’elles influencent le résultat de HASH_AGG de la même manière).

    Par exemple, changer l’échelle et la précision d’une colonne qui fait partie d’une table ne change pas le résultat de HASH_AGG sur cette table. Voir HASH pour plus de détails.

  • Contrairement à la plupart des autres fonctions agrégées, HASH_AGG n’ignore pas les entrées NULL (autrement dit, les entrées NULL influencent le résultat HASH_AGG).

  • Les lignes dupliquées, y compris toutes les lignes dupliquées NULL, influencent le résultat pour les fonctions d’agrégation et de fenêtre. Le mot clé DISTINCT peut être utilisé pour supprimer l’effet des lignes dupliquées.

  • Lorsque cette fonction est appelée en tant que fonction de fenêtre, elle ne prend pas en charge :

    • Une clause ORDER BY dans la clause OVER.

    • Cadres de fenêtre explicites.

Détails du classement

No impact.

  • Deux chaînes identiques mais ayant des spécifications de classement différentes ont la même valeur de hachage. En d’autres termes, seule la chaîne, et non la spécification de classement, affecte la valeur de hachage.

  • Deux chaînes différentes, mais se égales selon un classement, peuvent avoir une valeur de hachage différente. Par exemple, deux chaînes identiques utilisant un classement insensible à la ponctuation auront normalement des valeurs de hachage différentes, car seule la chaîne, et non la spécification du classement, affecte la valeur de hachage.

Exemples

Cet exemple montre que les valeurs NULLs ne sont pas ignorées :

SELECT HASH_AGG(NULL), HASH_AGG(NULL, NULL), HASH_AGG(NULL, NULL, NULL);
Copy
+----------------------+----------------------+----------------------------+
|       HASH_AGG(NULL) | HASH_AGG(NULL, NULL) | HASH_AGG(NULL, NULL, NULL) |
|----------------------+----------------------+----------------------------|
| -5089618745711334219 |  2405106413361157177 |       -5970411136727777524 |
+----------------------+----------------------+----------------------------+

Cet exemple montre que l’entrée vide est hachée sur 0 :

SELECT HASH_AGG(NULL) WHERE 0 = 1;
Copy
+----------------+
| HASH_AGG(NULL) |
|----------------|
|              0 |
+----------------+

Utilisez HASH_AGG(*) pour agréger toutes les colonnes d’entrée :

SELECT HASH_AGG(*) FROM orders;
Copy
+---------------------+
|     HASH_AGG(*)     |
|---------------------|
| 1830986524994392080 |
+---------------------+

Cet exemple montre que l’agrégation groupée est prise en charge :

SELECT YEAR(o_orderdate), HASH_AGG(*)
  FROM ORDERS GROUP BY 1 ORDER BY 1;
Copy
+-------------------+----------------------+
| YEAR(O_ORDERDATE) |     HASH_AGG(*)      |
|-------------------+----------------------|
| 1992              | 4367993187952496263  |
| 1993              | 7016955727568565995  |
| 1994              | -2863786208045652463 |
| 1995              | 1815619282444629659  |
| 1996              | -4747088155740927035 |
| 1997              | 7576942849071284554  |
| 1998              | 4299551551435117762  |
+-------------------+----------------------+

Cet exemple supprime les lignes en double à l’aide de DISTINCT (les lignes en double influencent les résultats de HASH_AGG) :

SELECT YEAR(o_orderdate), HASH_AGG(o_custkey, o_orderdate)
  FROM orders GROUP BY 1 ORDER BY 1;
Copy
+-------------------+----------------------------------+
| YEAR(O_ORDERDATE) | HASH_AGG(O_CUSTKEY, O_ORDERDATE) |
|-------------------+----------------------------------|
| 1992              | 5686635209456450692              |
| 1993              | -6250299655507324093             |
| 1994              | 6630860688638434134              |
| 1995              | 6010861038251393829              |
| 1996              | -767358262659738284              |
| 1997              | 6531729365592695532              |
| 1998              | 2105989674377706522              |
+-------------------+----------------------------------+

SELECT YEAR(o_orderdate), HASH_AGG(DISTINCT o_custkey, o_orderdate)
  FROM orders GROUP BY 1 ORDER BY 1;
Copy
+-------------------+-------------------------------------------+
| YEAR(O_ORDERDATE) | HASH_AGG(DISTINCT O_CUSTKEY, O_ORDERDATE) |
|-------------------+-------------------------------------------|
| 1992              | -8416988862307613925                      |
| 1993              | 3646533426281691479                       |
| 1994              | -7562910554240209297                      |
| 1995              | 6413920023502140932                       |
| 1996              | -3176203653000722750                      |
| 1997              | 4811642075915950332                       |
| 1998              | 1919999828838507836                       |
+-------------------+-------------------------------------------+

Cet exemple calcule le nombre de jours pendant lesquels les ensembles de clients correspondants ayant des commandes dont le statut n’est pas égal à 'F' et dont le statut n’est pas égal à 'P', respectivement, sont identiques :

SELECT COUNT(DISTINCT o_orderdate) FROM orders;
Copy
+-----------------------------+
| COUNT(DISTINCT O_ORDERDATE) |
|-----------------------------|
| 2406                        |
+-----------------------------+

SELECT COUNT(o_orderdate)
  FROM (SELECT o_orderdate, HASH_AGG(DISTINCT o_custkey)
    FROM orders
    WHERE o_orderstatus <> 'F'
    GROUP BY 1
    INTERSECT
      SELECT o_orderdate, HASH_AGG(DISTINCT o_custkey)
        FROM orders
        WHERE o_orderstatus <> 'P'
        GROUP BY 1);
Copy
+--------------------+
| COUNT(O_ORDERDATE) |
|--------------------|
| 1143               |
+--------------------+

La requête ne tient pas compte de la possibilité de collisions de hachage, de sorte que le nombre réel de jours peut être légèrement inférieur.

Langage: Français