类别：

聚合函数 (Similarity Estimation) , 窗口函数的语法和用法

APPROXIMATE_SIMILARITY

根据输入的 MinHash 状态返回其相似度（Jaccard 指数）的估计值。有关 MinHash 状态的更多信息，请参阅 估计两个或多个集合的相似度。

别名：

APPROXIMATE_JACCARD_INDEX

另请参阅：

MINHASH、 MINHASH_COMBINE

语法

APPROXIMATE_SIMILARITY( [ DISTINCT ] <expr> [ , ... ] )

APPROXIMATE_SIMILARITY(*)

实参

expr

表达式应该是 MINHASH 函数调用返回的一个或多个 MinHash 状态。换言之，表达式必须是 MinHash 状态信息，而不是您需要其近似相似度的列或表达式。（下面的示例有助于说明这一点。）

有关 MinHash 状态的更多信息，请参阅 估计两个或多个集合的相似度。

返回

介于 0.0 和 1.0（含）之间的浮点数，其中 1.0 表示集相同，0.0 表示集没有重叠。

使用说明

  • DISTINCT 可以作为实参包含在内，但不起作用。

  • 输入 MinHash 状态必须具有长度相等的 MinHash 数组。

  • 输入 MinHash 状态的数组长度是近似值的质量指标。

    函数 MINHASH 中使用的 k 的值越大，近似值就越准确。但是，此值对相似度估算的计算时间具有线性影响。

示例

USE SCHEMA snowflake_sample_data.tpch_sf1;

SELECT APPROXIMATE_SIMILARITY(mh) FROM
    (
      (SELECT MINHASH(100, C5) mh FROM orders WHERE c2 <= 50000)
         UNION
      (SELECT MINHASH(100, C5) mh FROM orders WHERE C2 > 50000)
    );

+----------------------------+
| APPROXIMATE_SIMILARITY(MH) |
|----------------------------|
|                       0.97 |
+----------------------------+

Here is a more extensive example, showing the three related functions MINHASH, MINHASH_COMBINE and APPROXIMATE_SIMILARITY. This example creates 3 tables (ta, tb, and tc), two of which (ta and tb) are similar, and two of which (ta and tc) are completely dissimilar.

创建并使用以下值填充表：

CREATE TABLE ta (i INTEGER);
CREATE TABLE tb (i INTEGER);
CREATE TABLE tc (i INTEGER);

INSERT INTO ta (i) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO tb (i) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (11);
INSERT INTO tc (i) VALUES (-1), (-20), (-300), (-4000);

计算初始数据集的 minhash 信息：

CREATE TABLE minhash_a_1 (mh) AS SELECT MINHASH(100, i) FROM ta;
CREATE TABLE minhash_b (mh) AS SELECT MINHASH(100, i) FROM tb;
CREATE TABLE minhash_c (mh) AS SELECT MINHASH(100, i) FROM tc;

向其中一个表添加更多数据：

INSERT INTO ta (i) VALUES (12);

Demonstrate the MINHASH_COMBINE function:

CREATE TABLE minhash_a_2 (mh) AS SELECT MINHASH(100, i) FROM ta WHERE i > 10;

CREATE TABLE minhash_a (mh) AS
  SELECT MINHASH_COMBINE(mh)
    FROM (
      (SELECT mh FROM minhash_a_1)
      UNION ALL
      (SELECT mh FROM minhash_a_2)
    );

This query shows the approximate similarity of the two similar tables (ta and tb):

SELECT APPROXIMATE_SIMILARITY(mh)
  FROM (
    (SELECT mh FROM minhash_a)
    UNION ALL
    (SELECT mh FROM minhash_b)
  );

+-----------------------------+
| APPROXIMATE_SIMILARITY (MH) |
|-----------------------------|
|                        0.75 |
+-----------------------------+

This query shows the approximate similarity of the two very different tables (ta and tc):

SELECT APPROXIMATE_SIMILARITY(mh)
  FROM (
    (SELECT mh FROM minhash_a)
    UNION ALL
    (SELECT mh FROM minhash_c)
  );

+-----------------------------+
| APPROXIMATE_SIMILARITY (MH) |
|-----------------------------|
|                           0 |
+-----------------------------+