カテゴリ：

集計関数 , ウィンドウ関数

HASH_AGG

入力行の（順序付けられていない）セットの符号付き64ビットハッシュ値の集約を返します。HASH_AGG は、入力が提供されない場合でも、 NULLを返しません。空の入力は 0 に「ハッシュ」されます。

集約ハッシュ関数の用途の1つは、個々の古い値と新しい値を比較せずに、一連の値の変更を検出することです。HASH_AGG は、多くの入力に基づいて単一のハッシュ値を計算できます。入力の1つを変更すると、HASH_AGG 関数の出力が変更される可能性があります。通常、2つの値のリストを比較するには両方のリストを並べ替える必要がありますが、 HASH_AGG は入力の順序に関係なく同じ値を生成します。HASH_AGGに対して値を並べ替える必要がないため、通常パフォーマンスは非常に高速です。

注釈

HASH_AGG は、暗号化ハッシュ関数では ないため 、そのまま使用しないでください。

暗号化目的には、 SHA ファミリーの関数（文字列とバイナリ関数）を使用します。

こちらもご覧ください:

HASH

構文

集計関数

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

ウィンドウ関数

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

引数

exprN

式は、 GEOGRAPHY および GEOMETRY を除く、任意のSnowflakeデータ型の一般式とすることができます。

expr2

追加の式を含めることもできます。

expr3

結果を複数のウィンドウに分割する場合は、パーティション化する列です。

*

NULL の値を持つ記録を含む、すべての記録のすべての列の集計ハッシュ値を返します。集計関数とウィンドウ関数の両方にワイルドカードを指定できます。

関数にワイルドカードを渡す場合、ワイルドカードをテーブルの名前またはエイリアスで修飾することができます。例えば、 mytable というテーブルからすべての列を渡すには、以下のように指定します。

(mytable.*)
Copy

ILIKE と EXCLUDE キーワードを使ってフィルタリングすることもできます。

  • ILIKE は、指定されたパターンに一致する列名でフィルターします。許されるパターンは1つのみです。例:

    (* ILIKE 'col1%')
    Copy

  • EXCLUDE は、指定された列に一致しない列名をフィルターします。例:

    (* EXCLUDE col1) (* EXCLUDE (col1, col2))
    Copy

修飾子は、これらのキーワードを使用する場合に有効です。以下の例では、 ILIKE キーワードを使用して、テーブル mytable 内のパターン col1% に一致するすべての列をフィルターします。

(mytable.* ILIKE 'col1%')
Copy

ILIKE と EXCLUDE キーワードは、1つの関数呼び出し内で組み合わせることはできません。

この関数では、 ILIKE と EXCLUDE キーワードは SELECT リストまたは GROUP BY 句でのみ有効です。

ILIKE と EXCLUDE キーワードの詳細については、 SELECT の「パラメータ」セクションをご参照ください。

戻り値

符号付き64ビット値を NUMBER（19、0）として返します。

HASH_AGG は、 NULL 入力に対しても NULLを返しません。

使用上の注意

  • HASH_AGG は、テーブル、クエリ結果、ウィンドウ全体の「フィンガープリント」を計算します。入力に対する変更は、圧倒的な確率で HASH_AGG の結果に影響します。これを使用して、テーブルの内容またはクエリ結果の変更をすばやく検出できます。

    非常にまれですが、2つの異なる入力テーブルが HASH_AGG に対して同じ結果を生成する可能性があります。同じ HASH_AGG 結果を生成する2つのテーブルまたはクエリ結果が実際に同じデータを含んでいることを確認する必要がある場合、（例えば MINUS 演算子を使用して）データが等しいかどうかを比較する必要があります。詳細については、 演算子のセット をご参照ください。

  • HASH_AGG は順序依存 ではありません （つまり、入力テーブルやクエリ結果の行の順序は HASH_AGG の結果に影響しません）。ただし、入力列の順序を変更すると、結果が 変化します 。

  • HASH_AGG は、 HASH 関数を使用して、個々の入力行をハッシュします。この関数の顕著な特徴は HASH_AGG に引き継がれます。特に、 HASH_AGG は、等しいとして比較され、互換性のあるタイプを持つ2つの行は、同じ値にハッシュすることが保証される（つまり、 HASH_AGG の結果に同じ影響を与える）という意味で「安定」しています。

    例えば、あるテーブルの一部である列のスケールや精度を変更しても、そのテーブルの HASH_AGG の結果は変更されません。詳細については HASH をご参照ください。

  • 他のほとんどの集計関数とは対照的に、 HASH_AGG は NULL の入力を無視しません（つまり、 NULL の入力は HASH_AGG の結果に影響します）。

  • 集計関数とウィンドウ関数の両方において、重複行（重複するすべての NULL 行を含む）が結果に影響します。DISTINCT キーワードを使用して、重複行の影響を抑制できます。

  • この関数がウィンドウ関数として呼び出される場合、以下はサポートされていません。

    • OVER 句内の ORDER BY 句。

    • 明示的なウィンドウフレーム。

照合順序の詳細

  • 同一だが照合仕様が異なる2つの文字列は、同じハッシュ値を持ちます。つまり、照合仕様ではなく文字列のみがハッシュ値に影響します。

  • 2つの文字列が異なっていても、照合順序に従って同じものとして比較すると、ハッシュ値が異なる場合があります。例えば、句読点を区別しない照合順序を使用した2つの文字列は、通常異なるハッシュ値を持ちます。照合順序の仕様ではなく文字列のみがハッシュ値に影響するからです。

例

この例は、 NULLs が無視されないことを示しています。

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 | +----------------------+----------------------+----------------------------+

この例は、空の入力が 0 にハッシュされることを示しています。

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

HASH_AGG（*）を使用して、すべての入力列を簡単に集約します。

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

この例は、グループ化された集計がサポートされていることを示しています。

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 | +-------------------+----------------------+

DISTINCT を使用して重複行を抑制します（重複行は 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 | +-------------------+-------------------------------------------+

この例では、ステータスが 'F' に等しくない注文と、ステータスが 'P' に等しくない注文に対応する顧客のセットがそれぞれ同一である日数を計算します。

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 | +--------------------+

クエリはハッシュ衝突の可能性を考慮していないため、実際の日数はわずかに短くなる可能性があります。

言語: 日本語