示例
示例：在子查询中返回基数
以下示例为名为 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 存储用户在给定网站上访问的每个页面的预聚合草图。此表中的每一行都包含一个 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_CARDINALITY 函数获取集体的不同值，如下所示。
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 中。您可以运行以下查询在名为 spectrum 的 Amazon Redshift 外部架构中定义名为 sales 的外部表。
create external table spectrum.sales( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, dateid smallint, qtysold smallint, 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;
该输出值看上去类似于以下内容。
-- 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');