类别：

:doc:`/sql-reference/functions-aggregation`（基数估算）、:doc:`窗口函数 </sql-reference/functions-window-syntax>`（基数估算）

HLL_ESTIMATE

返回给定 HyperLogLog 状态的基数估计值。

由 HLL_ACCUMULATE 和 HLL_COMBINE 生成的 HyperLogLog 状态可用于使用 HLL_ESTIMATE 函数计算基数估计值。

因此，HLL_ESTIMATE(HLL_ACCUMULATE(...)) 等效于 HLL(...)。

另请参阅：

HLL、HLL_ACCUMULATE、HLL_COMBINE

语法

聚合函数

HLL_ESTIMATE( <state> )

窗口函数

HLL_ESTIMATE( <state> ) OVER ( [ PARTITION BY <expr> ] )

有关 OVER 子句的详细信息，请参阅 窗口函数的语法和用法。

实参

state

一个表达式，其中包含通过调用 HLL_ACCUMULATE 或 HLL_COMBINE 生成的状态信息。

使用说明

示例

This example shows how to use the three related functions HLL_ACCUMULATE, HLL_ESTIMATE, and HLL_COMBINE.

创建简单的表和数据：

CREATE OR REPLACE SEQUENCE seq92;
CREATE OR REPLACE TABLE sequence_demo (c1 INTEGER DEFAULT seq92.nextval, dummy SMALLINT);
INSERT INTO sequence_demo (dummy) VALUES (0);

INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;

Create a table that contains the "state" that represents the current approximate cardinality information for the table named sequence_demo:

CREATE OR REPLACE TABLE resultstate1 AS (
  SELECT HLL_ACCUMULATE(c1) AS rs1
    FROM sequence_demo);

Now create a second table and add data. (In a more realistic situation, the user could have loaded more data into the first table and divided the data into non-overlapping sets based on the time that the data was loaded.)

CREATE OR REPLACE TABLE test_table2 (c1 INTEGER);
INSERT INTO test_table2 (c1) SELECT c1 + 4 FROM sequence_demo;

仅获取新数据的“状态”信息。

CREATE OR REPLACE TABLE resultstate2 AS
  (SELECT HLL_ACCUMULATE(c1) AS rs1
     FROM test_table2);

合并两批行的“状态”信息：

CREATE OR REPLACE TABLE combined_resultstate (c1) AS
  SELECT HLL_COMBINE(rs1) AS apc1
    FROM (
      SELECT rs1 FROM resultstate1
      UNION ALL
      SELECT rs1 FROM resultstate2
    );

获取行组合集的近似基数：

SELECT HLL_ESTIMATE(c1)
  FROM combined_resultstate;

+------------------+
| HLL_ESTIMATE(C1) |
|------------------|
|               12 |
+------------------+