例
例: サブクエリで基数を返す
次の例では、Sales という名前のテーブルでサブクエリ内の各スケッチの基数を返します。
CREATE TABLE Sales (customer VARCHAR, country VARCHAR, amount BIGINT); INSERT INTO Sales VALUES ('David Joe', 'Greece', 14.5), ('David Joe', 'Greece', 19.95), ('John Doe', 'USA', 29.95), ('John Doe', 'USA', 19.95), ('George Spanos', 'Greece', 9.95), ('George Spanos', 'Greece', 2.95);
次のクエリは、各国の顧客の HLL スケッチを生成し、基数を抽出します。これは、各国のユニークな顧客を示しています。
SELECT hll_cardinality(sketch), country FROM (SELECT hll_create_sketch(customer) AS sketch, country FROM Sales GROUP BY country) AS hll_subquery; hll_cardinality | country ----------------+--------- 1 | USA 2 | Greece ...
例: サブクエリで結合されたスケッチから HLLSKETCH タイプを返す
次の例では、サブクエリから個々のスケッチの組み合わせを表す単一の HLLSKETCH 型を返します。スケッチは、HLL_COMBINE 集計関数を使用して結合されます。
SELECT hll_combine(sketch) FROM (SELECT hll_create_sketch(customers) AS sketch FROM Sales GROUP BY country) AS hll_subquery hll_combine -------------------------------------------------------------------------------------------- {"version":1,"logm":15,"sparse":{"indices":[29808639,35021072,47612452],"values":[1,1,1]}} (1 row)
例: 複数のスケッチを組み合わせて HyperLogLog スケッチを返す
次の例では、テーブル page-users に、ユーザーが特定のウェブサイトでアクセスした各ページで、事前に集計されたスケッチが保存されているとします。このテーブルの各行には、訪問したページを表示するすべてのユーザー ID を表す HyperLogLog スケッチが含まれています。
page_users -- +----------------+-------------+--------------+ -- | _PARTITIONTIME | page | sketch | -- +----------------+-------------+--------------+ -- | 2019-07-28 | homepage | CHAQkAQYA... | -- | 2019-07-28 | Product A | CHAQxPnYB... | -- +----------------+-------------+--------------+
次の例では、事前に集計された複数のスケッチを結合し、1 つのスケッチを生成します。このスケッチは、各スケッチがカプセル化する集合基数をカプセル化します。
SELECT hll_combine(sketch) as sketch FROM page_users
出力は次の例のようになります。
-- +-----------------------------------------+ -- | sketch | -- +-----------------------------------------+ -- | CHAQ3sGoCxgCIAuCB4iAIBgTIBgqgIAgAwY.... | -- +-----------------------------------------+
新しいスケッチを作成するときに、次に示すように、HLL_CARDINALITY 関数を使用して、集合的な個別値を取得できます。
SELECT hll_cardinality(sketch) FROM ( SELECT hll_combine(sketch) as sketch FROM page_users ) AS hll_subquery
出力は次の例のようになります。
-- +-------+ -- | count | -- +-------+ -- | 54356 | -- +-------+
例: 外部テーブルを使用して S3 データに対する HyperLogLog スケッチを生成する
以下の例では、基数推定のために Amazon S3 に直接アクセスしないように、HyperLogLog スケッチをキャッシュします。
HyperLogLog スケッチを事前に集計して、Amazon S3 データを保持するために定義された外部テーブルにキャッシュできます。これにより、基になる基本データにアクセスせずに、基数の推定値を抽出できます。
たとえば、タブ区切りテキストファイルのセットを Amazon S3 にアンロードしたとします。次のクエリを実行して、sales という名前の Amazon Redshift 外部スキーマに spectrum という名前の外部テーブルを定義します。この例における Amazon S3 バケットは 米国東部 (バージニア北部) AWS リージョンにあります。
create external table spectrum.sales( salesid integer, listid integer, sellerid smallint, buyerid smallint, eventid integer, dateid integer, qtysold integer, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp) row format delimited fields terminated by '\t' stored as textfile location 's3://redshift-downloads/tickit/spectrum/sales/';
任意の日付に商品を購入した個別の購入者を計算するとします。そのために、以下の例では、その年の各日の購入者 ID のスケッチを生成し、その結果を Amazon Redshift テーブル hll_sales に保存します。
CREATE TABLE hll_sales AS SELECT saletime, hll_create_sketch(buyerid) AS sketch FROM spectrum.sales GROUP BY saletime; SELECT TOP 5 * FROM hll_sales;
出力は次の例のようになります。
-- hll_sales -- | saletime | sketch | -- +-----------------+---------------------------------------------------------------------+ -- | 7/22/2008 8:30 | {"version":1,"logm":15,"sparse":{"indices":[9281416],"values":[1]}} -- | 2/19/2008 0:38 | {"version":1,"logm":15,"sparse":{"indices":[48735497],"values":[3]}} -- | 11/5/2008 4:49 | {"version":1,"logm":15,"sparse":{"indices":[27858661],"values":[1]}} -- | 10/27/2008 4:08 | {"version":1,"logm":15,"sparse":{"indices":[65295430],"values":[2]}} -- | 2/16/2008 9:37 | {"version":1,"logm":15,"sparse":{"indices":[56869618],"values":[2]}} -- +---------------- +---------------------------------------------------------------------+
次のクエリは、2008 年の感謝祭後の金曜日に商品を購入した個別購入者の推定数を示します。
SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers FROM hll_sales WHERE trunc(saletime) = '2008-11-28';
出力は次の例のようになります。
distinct_buyers --------------- 386
特定の日付範囲で商品を購入した個別ユーザーの数が必要であるとします。例としては、感謝祭の後の金曜日から次の月曜日までにすることができます。これを取得するために、次のクエリは hll_combine 集計関数を使用します。この機能を使用すると、選択した範囲より 1 日を超えて商品を購入した購入者が二重カウントされないようにすることができます。
SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers FROM hll_sales WHERE saletime BETWEEN '2008-11-28' AND '2008-12-01';
出力は次の例のようになります。
distinct_buyers --------------- 1166
hll_sales テーブルを最新の状態に保つには、1 日の終わりに次のクエリを実行します。これを行うと、本日商品を購入した購入者の ID に基づいて HyperLogLog スケッチが生成され、hll_sales テーブルに追加されます。
INSERT INTO hll_sales SELECT saletime, hll_create_sketch(buyerid) FROM spectrum.sales WHERE TRUNC(saletime) = to_char(GETDATE(), 'YYYY-MM-DD') GROUP BY saletime;
