Categories:

System functions (System Information)

SYSTEM$CLUSTERING_RATIO — Deprecated

Calculates the clustering ratio for a table, based on one or more columns in the table. The ratio is a number from 0 to 100. The higher the ratio, the better clustered the table is.

The clustering ratio for a table can be calculated using any columns in the table or columns that have been explicitly defined as a clustering key for the table. A clustering key can be defined for a table using either CREATE TABLE or ALTER TABLE.

For more information about clustering ratio and clustering keys, see Understanding Snowflake Table Structures.

语法

SYSTEM$CLUSTERING_RATIO( '<table_name>' , '( <col1> [ , <col2> ... ] )' [ , '<predicate>' ] )

实参

table_name

要计算群集比率的表。

col1 [ , col2 ... ]

表中用于计算群集比率的列：

  • 对于没有群集密钥的表，必须使用此实参。如果省略此实参，将返回错误信息。
  • 对于具有群集密钥的表，此实参是可选的；如果省略此实参，Snowflake 将使用定义的群集密钥计算比率。

Note

无论是否为表定义了群集密钥，都可以使用此实参为表中的任何列计算比率。

predicate

Clause that filters the range of values in the columns on which to calculate the clustering ratio. Note that predicate does not utilize a WHERE keyword at the beginning of the clause.

使用说明

  • 所有实参都是字符串（即它们必须放入单引号内）。
  • If predicate contains a string, the string must be enclosed in single quotes, which then must be escaped using single quotes. For example: SYSTEM$CLUSTERING_RATIO( ... , 'col1 = 100 and col2 = ''A''' )

示例

使用表中的两列计算表的群集比率：

SELECT SYSTEM$CLUSTERING_RATIO('t2', '(col1, col3)');

+-------------------------------+
| SYSTEM$CLUSTERING_RATIO('T2') |
%-------------------------------%
|                          77.1 |
+-------------------------------+

使用表中的两列和其中一列上的谓词计算表的群集比率：

SELECT SYSTEM$CLUSTERING_RATIO('t2', '(col1, col2)', 'col1 = ''A''');

+-------------------------------+
| SYSTEM$CLUSTERING_RATIO('T2') |
%-------------------------------%
|                          87.7 |
+-------------------------------+

使用为表定义的群集密钥计算表的群集比率：

SELECT SYSTEM$CLUSTERING_RATIO('t1');

+-------------------------------+
| SYSTEM$CLUSTERING_RATIO('T1') |
%-------------------------------%
|                         100.0 |
+-------------------------------+