This topic describes table-valued functions (TVFs).

rankTvf

rankTvf is similar to the rank window function of SQL statements. This function is used to filter data after data is dispersed. The following sample code provides the prototype of the function:

rankTvf("group_key", "sort_key", "reserved_count", (sql))

group_key: the field that is used to disperse data. You can specify multiple fields or leave the parameter empty. Separate multiple fields with commas (,).

sort_key: the field that you want to use to sort data. You can specify multiple fields. The plus sign (+) specifies that the system sorts data in ascending order. The minus sign (-) specifies that the system sorts data in descending order. By default, the system sorts data in ascending order. You cannot leave this field empty.

reserved_count: the number of returned records that you want to reserve in each group. A negative number specifies that all returned records are reserved in each group.

sql: the SQL statement whose query result needs to be dispersed.

After rankTvf is used to disperse and filter the results of SQL statements, the output results are still sorted in the order in which the original results of SQL statements are sorted. The filtered rows are deleted from the output results.

Example:

select * from table (
  rankTvf('brand','-size','1', (SELECT brand, size FROM phone))
) 
order by brand 
limit 100

sortTvf

sortTvf is used to provide the local top K feature. For example, you can use the sortTvf function to sort the top K results returned by the searcher and perform join operations on the results. If you use an ORDER BY clause, the clause is pushed to the query record searcher (QRS) for join operations. ORDER BY clauses are used for global sort operations.

The following sample code provides the prototype of the function:

sortTvf("sort_key", "reserved_count", (sql))

reserved_count: the number of fields that are reserved in each group.

sql: the SQL statement whose query result needs to be sorted.

Difference between sortTvf and rankTvf: sortTvf changes the order of rows in the original result of SQL statements in tables.

Example:

select * from table (
  sortTvf('-size','3', (SELECT brand, size FROM phone))
)

topKTvf

topKTvf is used to provide the local top K feature. For example, you can use the topKTvf function to sort the top K results returned by the searcher and perform join operations on the results. If you use an ORDER BY clause, the clause is pushed to the query record searcher (QRS) for join operations. ORDER BY clauses are used for global sort operations.

The following sample code provides the prototype of the function:

topKTvf("sort_key", "reserved_count", (sql))

reserved_count: the number of fields that are reserved in each group.

sql: the SQL statement whose query result needs to be sorted.

Differences between topKTvf and sortTvf: The final result returned by topKTvf is unordered.

Example:

select * from table (
  topKTvf('-size','3', (SELECT brand, size FROM phone))
)

enableShuffleTvf

The enableShuffleTvf function is used to switch SQL statements from a TVF to the QRS. For example, SQL statements that need to be processed by the rankTvf function can be pushed down to a searcher by default. If an SQL statement in the rankTvf function contains enableShuffleTvf, the rankTvf function only runs on the QRS. The following sample code provides the prototype of the function:

enableShuffleTvf((sql))

The following sample code provides an example on how to use the enableShuffleTvf function:

select * from table (
 enableShuffleTvf((SELECT brand, size FROM phone))
)