範例 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

範例

範例:返回子查詢中的基數

以下示例返回名為銷售

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_CONING 聚合函數進行組合。

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存儲用户在給定網站上訪問的每個頁面的預聚合草圖。此表中的每一行都包含一個 HyperLogLog 草圖,該草圖表示顯示訪問頁面的所有用户 ID。

page_users -- +----------------+-------------+--------------+ -- | _PARTITIONTIME | page | sketch | -- +----------------+-------------+--------------+ -- | 2019-07-28 | homepage | CHAQkAQYA... | -- | 2019-07-28 | Product A | CHAQxPnYB... | -- +----------------+-------------+--------------+

以下示例將預聚合的多個草繪聯合起來並生成單個草繪。此草圖封裝了每個草圖封裝的集體基數。

SELECT hll_combine(sketch) as sketch FROM page_users

輸出結果類似如下。

-- +-----------------------------------------+ -- | sketch | -- +-----------------------------------------+ -- | CHAQ3sGoCxgCIAuCB4iAIBgTIBgqgIAgAwY.... | -- +-----------------------------------------+

創建新草繪時,可以使用 HLL_CARDINITY 函數獲取集體不同值,如下所示。

SELECT hll_cardinality(sketch) FROM ( SELECT hll_combine(sketch) as sketch FROM page_users ) AS hll_subquery

輸出結果類似如下。

-- +-------+ -- | count | -- +-------+ -- | 54356 | -- +-------+

範例:使用外部表在 S3 數據上生成 HyperLogLog 草圖

以下示例緩存 HyperLogLog 草圖,以避免直接訪問 Amazon S3 進行基數估計。

您可以在定義為保存 Amazon S3 數據的外部表中預聚合和緩存 HyperLogLog 草圖。通過這樣做,您可以提取基數估計值,而無需訪問基礎數據。

例如,假設您已將一組製表符分隔的文字檔案卸載至 Amazon S3。運行以下查詢來定義名為salesAmazon Redshift 名為spectrum

create external table spectrum.sales( sellerid integer, buyerid integer, dateid smallint, qtysold smallint, product_type integer, pricepaid decimal(8,2), commission decimal(8,2), saletime date) row format delimited fields terminated by '\t'stored as textfile location 's3://awssampledbuswest2/tickit/spectrum/sales/';

假設您想要計算在任意日期購買商品的獨特買家。為此,以下示例為一年中每一天的買家編號生成草圖,並將結果存儲在 Amazon Redshift 表中hll_sales

CREATE TABLE hll_sales AS SELECT saletime, hll_create_sketch(buyerid) AS sketch FROM spectrum.sales GROUP BY saletime;

輸出結果類似如下。

-- hll_sales -- | saletime | sketch | -- +----------------+---------------+ -- | 2018-11-23 | "CHAQkAQYA..." -- | 2018-11-24 | "TNLMLMLKK..." -- | 2018-11-25 | "KMNKLLOKM..." -- | 2018-11-26 | "MMKNKLLMO..." -- | 2018-11-27 | "MMLSKNLPM..." -- +----------------+---------------+

以下查詢提取感恩節後週五期間購買商品的不同買家的估計數量。

SELECT hll_cardinality(sketch) as distinct_buyers FROM hll_sales WHERE saletime = '2018-11-23';

輸出結果類似如下。

distinct_buyers --------------- 1771

假設您需要在特定日期範圍內購買項目的不同用户的數量。一個例子可能是從感恩節後的星期五到下一個星期一。為了得到這一點,以下查詢使用hll_combine彙整函數。此功能使您可以避免重複計算在選定範圍內一天以上購買商品的買家。

SELECT hll_cardinality(hll_combine(sketch)) as distinct_buyers FROM hll_sales WHERE saletime BETWEEN '2018-11-23' AND '2018-11-26';

輸出結果類似如下。

distinct_buyers --------------- 232152

若要保留hll_sales表最新時,請在每天結束時運行以下查詢。這樣做會根據今天購買商品的買家的 ID 生成一個 HyperLogLog 草圖,並將其添加到hll_sales資料表。

INSERT INTO hll_sales SELECT saletime, hll_create_sketch(buyerid) FROM spectrum.sales WHERE saletime = to_char(now(), 'YYYY-MM-DD');