例

このセクションでは、Amazon Redshift で HyperLogLog を使用する例を示します。

例: サブクエリで基数を返す

次の例では、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(customer) 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;