示例

此部分包含在 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 存储用户在给定网站上访问的每个页面的预聚合草图。此表中的每一行都包含一个 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 的外部表。此示例的 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 聚合函数。此函数使您能够避免重复计算在选定范围内超过一天购买商品的买家。

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表，请在每天结束时运行以下查询。这样做会根据今天购买商品的买家的 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;