如何在SQL查詢中使用多元索引虛擬列 - Tablestore

使用多元索引虛擬列功能時，您可以通過修改多元索引schema或者建立多元索引來實現新欄位新資料類型的查詢功能，而無需修改Table Store的儲存結構及資料。

說明

關於多元索引虛擬列的更多資訊，請參見虛擬列。

注意事項

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如下圖所示。
建立多元索引映射關係。具體操作，請參見通過控制台使用SQL查詢或者通過SDK使用SQL查詢。
多元索引映射關係名稱為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;
```
  返回結果如下圖所示。