Docs

Torch Log Service

SQL function

Approximate function

This topic introduces the syntax of estimation functions supported by TLS and provides common usage examples.

Function list

Notice

In TLS query and analysis statements (SQL statements), use single quotes ('') to enclose strings. Characters without quotes or with double quotes ("") indicate field names or column names. For example, 'time' represents a string, time or "time" represents a field name or column name.

Function name	Function syntax	Description
APPROX_PERCENTILE function	APPROX_PERCENTILE(KEY, percentage)	Sort the values in ascending order and return the KEY value at approximately the specified percentile.
		APPROX_PERCENTILE(KEY, array[percentage01, percentage02...])	Sort the values in ascending order and return the KEY values at approximately percentile 1 and percentile 2.
		APPROX_PERCENTILE(KEY, weight, percentage)	Sort the products of the values and weights in ascending order and return the KEY value at approximately the specified percentile.
		APPROX_PERCENTILE(KEY, weight, array[percentage01, percentage02...])	Sort the products of the values and weights in ascending order and return the KEY values corresponding to the 1st and 2nd percentiles.
		APPROX_PERCENTILE(KEY, weight, percentage, accuracy)	Sort the products of a set of values and weights in ascending order, and return the value that is approximately at the specified percentile. Supports setting the accuracy.
approx_most_frequent function	approx_most_frequent(KEY, x)	Counts the occurrences of each value in column x and sort the values in descending order. Then returns the top values with the highest frequency based on the key and their respective counts.

APPROX_PERCENTILE function

The APPROX_PERCENTILE function sorts a column of values in ascending order and returns the approximate key value at a specified percentile.

Function syntax

Syntax format

Sort a column of values in ascending order and return the key value at the specified percentile.

APPROX_PERCENTILE(KEY, percentage)

Sort a column of values in ascending order and return the key values at percentile 1 and percentile 2. The result is returned as an array.

APPROX_PERCENTILE(KEY, array[percentage01, percentage02...])

Arrange the products of a column's values and weights in ascending order and return the KEY value approximately at the specified percentile.

APPROX_PERCENTILE(KEY, weight, percentage)

Arrange the products of a column's values and weights in ascending order and return KEY values at percentile 1 and percentile 2. The result is returned as array format.

APPROX_PERCENTILE(KEY, weight, array[percentage01, percentage02...])

Arrange the products of a column's values and weights in ascending order and return the KEY value approximately at a specified percentile. The result is of type Double. Supports setting the accuracy.

APPROX_PERCENTILE(KEY, weight, percentage, accuracy)

Parameters description

Parameters	Description
KEY	A field or expression. Its values are Double type.
percentage	Percentage value. Range: [0,1].
accuracy	Accuracy. Range: (0,1).
weight	Weight, must be an integer greater than 1. After setting the weight, the system sorts based on the product of the KEY value and weight.

Return values

The return value is of type Double or Array(double).

Function examples

Example 1

Scenarios

After sorting the test_double field values, return the test_double field value approximately at the 50% position.

Log example

Query and analysis statements

*| SELECT APPROX_PERCENTILE(test_double,0.5)

Query and analysis results

Example 2

Scenarios

After sorting the test_double field values, return the test_double field values at the 10%, 20%, and 70% positions.

Log example

Query and analysis statements

*| SELECT APPROX_PERCENTILE(test_double,array[0.1,0.2,0.7])

Query and analysis results

Example 3

Scenarios

Arrange test_double field values based on the product of test_double field values and their respective weights, and then return the test_double field value that is approximately at the 50% position. When the test_double field value is less than 1.5, the weight is 100; otherwise, the weight is 10.

Query and analysis statements

* |
SELECT
APPROX_PERCENTILE(
test_double,case when test_double < 1.5 then 100 else 10 end,
0.5
)

Log example

Query and analysis results

Example 4

Scenarios

Arrange test_double field values based on the product of test_double field values and their respective weights, and then return the test_double field values that are approximately at the 50% and 90% positions. When the test_double field value is less than 1.5, the weight is 100; otherwise, the weight is 10.

Log example

Query and analysis statements

* |
SELECT
APPROX_PERCENTILE(
test_double,case when test_double < 1.5 then 100 else 10 end,
array [0.5,0.9]
)

Query and analysis results

Example 5

Scenarios

After sorting the test_double field values by multiplying them with their corresponding weights based on the test_double field values, the value of the test_double field approximately at the 50% position is returned, with an accuracy of 0.2. When the test_double field value is less than 1.5, the weight is 100; otherwise, the weight is 10.

Log example

Query and analysis statements

* |
SELECT
APPROX_PERCENTILE(
test_double,case when test_double < 1.5 then 100 else 10 end,
0.5,
0.2
)

Query and analysis results

approx_most_frequent function

The approx_most_frequent function counts the occurrences of each value in column x and sorts them in descending order, returning the top key values with the highest frequency along with their counts.

Function syntax

Syntax format

approx_most_frequent(KEY, x)

Parameters description

Parameters
Description
KEY
Returns the top KEY values based on frequency; 3 means the top 3 values and their respective counts are returned.
x
The parameter value is of varchar type.

Return values

The return value is of type map(varchar, bigint).

Function examples

Scenarios

Estimate the top 3 most frequent values in the content field.

Log example

content:
'B'
'A'
'A'
'A'
'A'
'B'
'B'
'C'
'C'
'D'

Query and analysis statements

* | select approx_most_frequent(3, content)

Query and analysis results

The query returns one row, the column name is _col0, and the value is {"A":4,"B":3,"C":2}.

Last updated: 2026.08.04 14:54:52

Parameters	Description
KEY	Returns the top KEY values based on frequency; 3 means the top 3 values and their respective counts are returned.
x	The parameter value is of varchar type.