如何在SQL查询中使用多元索引虚拟列_表格存储(Tablestore)-阿里云帮助中心

使用多元索引虚拟列功能时，您可以通过修改多元索引schema或者新建多元索引来实现新字段新数据类型的查询功能，而无需修改表格存储的存储结构及数据。

说明

关于多元索引虚拟列的更多信息，请参见虚拟列。

注意事项

SQL查询目前不支持多元索引Geo-point类型。
SQL查询虚拟列目前不支持数组类型的虚拟列。

数据类型映射

表中数据类型	多元索引虚拟列数据类型	SQL数据类型
多元索引虚拟列在表中无对应的列，只有虚拟列对应的原始列在表中有对应的列。多元索引数据类型和数据表数据类型映射请参见数据类型映射。	Keyword	MEDIUMTEXT
	Text	MEDIUMTEXT
	Long	BIGINT
	Double	Double

创建方法

要在SQL查询中使用多元索引虚拟列，则必须创建多元索引映射关系。关于创建多元索引映射关系的具体操作，请参见创建多元索引的映射关系。

在CREATE TABLE语句中，只需要正确书写虚拟列名称和对应的SQL数据类型即可。

创建包含虚拟列的多元索引映射关系，SQL示例如下：

CREATE TABLE `test_table__test_table_index`(
    `id` MEDIUMTEXT,
    `col_keyword` MEDIUMTEXT,
    `col_keyword_virtual_long` BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"test_table_index","table_name":"test_table"}';

通过SQL查询数据

使用SQL查询数据时，虚拟列支持如下使用方法：

虚拟列支持用在SELECT语句中的WHERE子句来返回满足查询条件的数据。
在查询条件中使用虚拟列时，虚拟列的数据类型和相应查询条件中的参数数据类型必须一致。
虚拟列支持用在统计聚合中进行数据分析，但虚拟列在多元索引中的原始数据类型必须满足多元索引统计聚合的相关要求。例如多元索引支持求和的数据类型为Long和Double，如果虚拟列在多元索引中的原始数据为Keyword，则不能对虚拟列进行求和。
支持按照虚拟列分组，但虚拟列在多元索引中的原始数据类型必须满足多元索引分组的相关要求。例如多元索引不支持Text数据类型的分组，则Text数据类型的虚拟列不支持进行分组。
虚拟列支持使用SQL进行TopN查询。
支持按照虚拟列排序，但是必须配合LIMIT使用。不支持在虚拟列上进行没有LIMIT的排序。

在SQL查询中使用虚拟列时有如下限制：

虚拟列仅支持在创建的多元索引映射关系中使用。
虚拟列仅支持用在查询语句中，不能用于SELECT返回列值。如果需要返回列值，请指定返回该虚拟列的原始列。如果多元索引映射关系中包含虚拟列column_virtual，使用SELECT *不受影响，返回结果中会自动忽略该虚拟列，但不能使用SELECT column_virtual。
虚拟列上不能进行列之间的比较、运算和join。
虚拟列不能进行数据类型转换后的计算，也不能对无法下推到多元索引的函数进行计算。目前下推函数中SQL查询仅支持下推聚合函数计算。

使用示例

假设数据表名称为test_table，该表中包括id（String类型）和col_keyword（String类型）两列。

如果要在SQL查询中使用多元索引虚拟列，则需要为数据表创建多元索引并创建多元索引的映射关系，然后使用SQL语句通过虚拟列查询数据。具体步骤如下：

创建多元索引，并在多元索引时完成虚拟列配置。具体操作，请参见通过控制台操作虚拟列或者通过SDK操作虚拟列。
说明
如果要使用的多元索引中未配置虚拟列，您也可以通过修改多元索引的schema来增加虚拟列。具体操作，请参见动态修改schema。
多元索引名称为test_table_index，该多元索引包括id（String类型）和col_keyword（Keyword类型）的列以及虚拟列col_keyword_virtual_long（Long类型）。其中虚拟列的原始列为col_keyword。
使用控制台创建多元索引的配置如下图所示。
多元索引Schema如下图所示。
创建多元索引映射关系。具体操作，请参见使用控制台或者使用SDK。
多元索引映射关系名称为test_table__test_table_index，该映射关系中虚拟列col_keyword_virtual_long（Long类型）对应的SQL数据类型为BIGINT。更多信息，请参见创建多元索引的映射关系。
SQL示例如下：
```
CREATE TABLE `test_table__test_table_index`(
    `id` MEDIUMTEXT,
    `col_keyword` MEDIUMTEXT,
    `col_keyword_virtual_long` BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"test_table_index","table_name":"test_table"}';
```
创建多元索引映射关系后，请执行如下语句获取表中数据。
```
SELECT * FROM `test_table__test_table_index`;
```
返回结果如下图所示。假设多元索引映射关系test_table__test_table_index有10条数据。
使用SELECT语句查询数据。更多信息，请参见查询数据。
- 查询col_keyword_virtual_long虚拟列小于50的平均值。
```
SELECT AVG(col_keyword_virtual_long) FROM `test_table__test_table_index` WHERE col_keyword_virtual_long < 50; 
```
  col_keyword_virtual_long虚拟列小于50的有四条数据，分别为10、20、30和40，计算得到平均值为25.0000。返回结果如下图所示。
- 对其他列按照虚拟列col_keyword_virtual_long进行降序排序，获取前5条数据。
```
SELECT * FROM `test_table__test_table_index` ORDER BY col_keyword_virtual_long DESC LIMIT 5;
```
  返回结果如下图所示。