Categories:

Aggregate functions (Frequency Estimation) , Window function syntax and usage

APPROX_TOP_K_ESTIMATE¶

Returns the approximate most frequent values and their estimated frequency for the given Space-Saving state. (For more information about the Space-Saving summary, see Estimating Frequent Values.)

A Space-Saving state produced by APPROX_TOP_K_ACCUMULATE and APPROX_TOP_K_COMBINE can be used to compute a cardinality estimate using the APPROX_TOP_K_ESTIMATE function.

因此，APPROX_TOP_K_ESTIMATE(APPROX_TOP_K_ACCUMULATE(…)) 等效于 APPROX_TOP_K(…)。

See also:

APPROX_TOP_K , APPROX_TOP_K_ACCUMULATE , APPROX_TOP_K_COMBINE

语法

APPROX_TOP_K_ESTIMATE( <state> [ , <k> ] )

实参

state

An expression that contains state information generated by a call to APPROX_TOP_K_ACCUMULATE or APPROX_TOP_K_COMBINE.

k

The number of values whose counts you want approximated. For example, if you want to see the top 10 most common values, then set k to 10.

If k is omitted, the default is 1.

The maximum value is 100000 (100,000), and is automatically reduced if items cannot fit in the output.

返回

返回类型 ARRAY 的值。

使用说明

示例

此示例说明如何使用三个相关函数 APPROX_TOP_K_ACCUMULATE、APPROX_TOP_K_ESTIMATE 和 APPROX_TOP_K_COMBINE。

此示例生成一个表，其中包含值为 1-8 的 8 行，以及另一个包含 8 行的表，其值为 5-12。因此，两个表的并集中比较常见的值是值 5-8，每个值的计数为 2。

创建简单的表和数据：

CREATE OR REPLACE SEQUENCE seq91;
CREATE OR REPLACE TABLE sequence_demo (c1 INTEGER DEFAULT seq91.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 Top K information for the table named sequence_demo:

CREATE OR REPLACE TABLE resultstate1 AS (
  SELECT APPROX_TOP_K_ACCUMULATE(c1, 50) AS rs1
    FROM sequence_demo);

现在创建另一个表并添加数据。（在更实际的情况下，用户可以将更多数据加载到第一个表中，并根据加载数据的时间将数据划分为非重叠集）。

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 APPROX_TOP_K_ACCUMULATE(c1, 50) AS rs1
     FROM test_table2);

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

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

获取组合行集的近似 Top K 值：

SELECT APPROX_TOP_K_ESTIMATE(c1, 4)
  FROM combined_resultstate;

+------------------------------+
| APPROX_TOP_K_ESTIMATE(C1, 4) |
%------------------------------%
| [                            |
|   [                          |
|     5,                       |
|     2                        |
|   ],                         |
|   [                          |
|     6,                       |
|     2                        |
|   ],                         |
|   [                          |
|     7,                       |
|     2                        |
|   ],                         |
|   [                          |
|     8,                       |
|     2                        |
|   ]                          |
| ]                            |
+------------------------------+