类别：: 聚合函数 (Frequency Estimation) , 窗口函数的语法和用法

APPROX_TOP_K_COMBINE¶

将多个输入状态组合（合并）为单个输出状态。

这允许 APPROX_TOP_K_ACCUMULATE 在同一表的水平分区上运行，从而为每个表分区生成算法状态。这些状态稍后可以使用 APPROX_TOP_K_COMBINE 进行组合，生成与整个表的 APPROX_TOP_K_ACCUMULATE 单次运行相同的输出状态。

另请参阅：: APPROX_TOP_K_ACCUMULATE、APPROX_TOP_K_ESTIMATE

语法¶

APPROX_TOP_K_COMBINE( <state> [ , <counters> ] )

实参¶

state

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

counters

这是在估算过程中一次可以跟踪的非重复值数量上限。例如，如果 counters 设置为 100000，则算法将跟踪 100000 个不同值，尝试保留 100000 个比较频繁的值。

counters 的数量上限是 100000 (100000)。

返回¶

此操作将返回有关前 K 计算的“状态”的信息。

This state information is not usually useful by itself, but can be passed to the function APPROX_TOP_K_ESTIMATE.

使用说明¶

如果已定义 counters，则输出状态使用指定数量的计数器。
如果未定义 counters，则所有输入状态必须具有相同数量的计数器。

不支持小数浮点数 (DECFLOAT) 值。

示例¶

This example shows how to use the three related functions APPROX_TOP_K_ACCUMULATE, APPROX_TOP_K_ESTIMATE, and 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);

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 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                        |
|   ]                          |
| ]                            |
+------------------------------+