Dataset Functions
The return results of these functions are datasets that contain fields and data. The examples provided in the descriptions of these functions are all based on the movie table in the database, which can be accessed via test. Below is the data for the movie table. In the HENGSHI SENSE system, the newly created data connection has an id of 1, the newly created app has an id of 1, and the newly created dataset has an id of 1.
| id | type | name | votes |
|---|---|---|---|
| 1 | Animation | The Lion King | 8.5 |
| 2 | Comedy | Crazy Alien | 8.2 |
| 3 | Animation | Frozen | 8.1 |
| 4 | Comedy | Detective Chinatown | 8.3 |
app_dataset
This function returns the data of a specific dataset under a specific app.
- Function Syntax
text
app_dataset({appId}, {datasetId})Parameter Description
- appId: Required. The ID of the app. Optional type: NUMBER
- datasetId: Required. The ID of the dataset. Optional type: NUMBER
Example
text
app_dataset(1, 1)dataset
This function returns the data of a specific dataset. This function can only be used within the context of an app.
- Function Syntax
text
dataset({id})Parameter Description
- id: Required. The ID of the dataset. Optional type: NUMBER
Example
text
dataset(1)db_source
This function returns the contents of a specific table under a given connection.
- Function Syntax
text
db_source({connectionId}, {path}, {tableName})Parameter Description
- connectionId: Required. The ID of the data connection configured in the current system. Optional type: NUMBER
- path: Required. The access path of the table. To use the database default, you can fill in []. Optional type: STRING array
- tableName: Required. The name of the table to query. Optional type: STRING
Example
text
db_source(1, ['test'], 'movie')The returned result is as follows:
json
{
"data": {
"data": [
[
1,
"Animation",
"The Lion King",
8.5
],
[
2,
"Comedy",
"Crazy Alien",
8.2
],
[
3,
"Animation",
"Frozen",
8.1
],
[
4,
"Comedy",
"Detective Chinatown",
8.3
]
],
"schema": [
{
"fieldName": "id",
"visible": true,
"nativeType": "int4"
},
{
"fieldName": "type",
"visible": true,
"nativeType": "text"
},
{
"fieldName": "name",
"visible": true,
"nativeType": "text"
},
{
"fieldName": "votes",
"visible": true,
"nativeType": "numeric"
}
]
}
}filter
This function returns the filtered dataset data.
- Function Syntax
text
filter({dataset expression}, {filter expression})Parameter Description
- dataset expression: Required. An expression that returns a dataset.
- filter expression: Required. An expression that returns a boolean value. Optional type: BOOL
Example
text
filter(app_dataset(1, 1), {votes} > 8.2)json
{
"data": {
"data": [
[
1,
"Animation",
"The Lion King",
8.5
],
[
4,
"Comedy",
"Detective Chinatown",
8.3
]
],
"schema": [
{
"fieldName": "id",
"visible": true,
"nativeType": "int4"
},
{
"fieldName": "type",
"visible": true,
"nativeType": "text"
},
{
"fieldName": "name",
"visible": true,
"nativeType": "text"
},
{
"fieldName": "votes",
"visible": true,
"nativeType": "numeric"
}
]
}
}select_fields
This function returns the data of the selected columns in the dataset. Each column operation is independent and does not affect each other.
- Function Syntax
text
select_fields({dataset expression}, {field1}, {field2} ...)Parameter Description
- dataset expression: Required. An expression that returns a dataset. The available options are the dataset function expressions introduced in this document.
- field1, field2 ...: Required. The number of parameters is variable, with at least one required. Describes the column expressions. Parameters cannot use newly added metrics in the dataset.
Example 1
text
select_fields(filter(app_dataset(1, 1), {votes} < 8.3), {id}, {type} as {Type}, {votes} - 8 as {Score Fraction})The result is as follows:
json
{
"data": {
"data": [
[
1,
"Animation",
0.5
],
[
4,
"Comedy",
0.3
]
],
"schema": [
{
"fieldName": "id",
"visible": true,
"nativeType": "int4"
},
{
"fieldName": "Type",
"visible": true,
"nativeType": "text"
},
{
"fieldName": "Score Fraction",
"visible": true,
"nativeType": "numeric"
}
]
}
}- Example 2
text
select_fields(app_dataset(1, 1), {type}, sum({votes}) as {Total Votes})The result is as follows:
json
{
"data": {
"data": [
[
"Animation",
33.1
],
[
"Comedy",
33.1
],
[
"Animation",
33.1
],
[
"Comedy",
33.1
]
],
"schema": [
{
"fieldName": "type",
"visible": true,
"nativeType": "text"
},
{
"fieldName": "Total Votes",
"visible": true,
"nativeType": "numeric"
}
]
}
}select_fields_complete
This function returns the data of the selected columns in the dataset. Each column operation is independent and does not affect each other. Unlike SELECT_FIELDS, it allows you to add filter conditions, sorting, and pagination information.
- Function Syntax
text
select_fields_complete({dataset expression},
[{field1},{field2},{field3} ...],
[filter_expr_1,filter_expr_2 ...],
[sort_expr_1,sort_expr_2...],
offset, limit)Parameter Description
- dataset expression: Required. An expression that returns a dataset as the result.
- [{field1},{field2},{field3} ...]: Required. The array length is unlimited. If the array is empty, it means retrieving information for all fields.
- [filter_expr_1,filter_expr_2 ...]: Required. Filter expressions. The relationship between different expressions is AND. If empty, it means there are no filter conditions.
- [sort_expr_1,sort_expr_2...]: Required. Sorting fields. When writing expressions, specifying order is not supported here; all are sorted in ascending order. However, when writing HE structures, the order can be specified.
- offset: Required. The offset for retrieving data.
- limit: Required. The limit on the number of records to retrieve.
Example 1: Writing an Expression
text
select_fields_complete(dataset(2),[{id},{month}],[{id}>1,{id}<50],[{id}],6,3)The result is as follows:
json
{
"data": {
"data": [
[
8,
9
],
[
9,
10
],
[
10,
10
]
],
"schema": [
{
"fieldName": "id",
"visible": true,
"type": "number"
},
{
"fieldName": "month",
"visible": true,
"type": "number"
}
]
}
}- Example 2: Writing an HE Structure
json
{
"kind": "function",
"op": "select_fields_complete",
"args": [
{
"kind": "function",
"op": "dataset",
"args": [
{
"kind": "constant",
"op": 2
}
]
},
[
{
"kind": "field",
"op": "id"
},
{
"kind": "field",
"op": "month"
}
],
[
{
"kind": "function",
"op": ">",
"args": [
{
"kind": "field",
"op": "id"
},
{
"kind": "constant",
"op": 1
}
]
},
{
"kind": "function",
"op": "<",
"args": [
{
"kind": "field",
"op": "id"
},
{
"kind": "constant",
"op": 50
}
]
}
],
[
{
"kind": "field",
"op": "id",
"direction": "desc"
}
],
{
"kind": "constant",
"op": 6
},
{
"kind": "constant",
"op": 3
}
]
}The result is as follows:
json
{
"data": {
"data": [
[
43,
1
],
[
42,
5
],
[
41,
10
]
],
"schema": [
{
"fieldName": "id",
"visible": true,
"type": "number"
},
{
"fieldName": "month",
"visible": true,
"type": "number"
}
]
}
}summarize
This function returns the data of the selected columns in the dataset. Operations on different columns are interdependent. After grouping by non-aggregated columns, the aggregated columns are then calculated.
- Function Syntax
text
summarize({dataset expression}, {field1}, {field2} ...)Parameter Description
- dataset expression: Required. An expression that returns a dataset as the result.
- field1, field2 ...: Required. The number of parameters is variable, with at least one required. Describes the column expressions.
Example 1
text
summarize(app_dataset(1, 1), {type})The result is as follows:
json
{
"data": {
"data": [
[
"Comedy"
],
[
"Animation"
]
],
"schema": [
{
"fieldName": "type",
"visible": true,
"nativeType": "text"
}
]
}
}- Example 2
text
summarize(app_dataset(1, 1), {type}, sum({votes}) as {sum1}, max({votes}) as {max1})The result is as follows:
json
{
"data": {
"data": [
[
"Comedy",
16.5,
8.3
],
[
"Animation",
16.6,
8.5
]
],
"schema": [
{
"fieldName": "type",
"visible": true,
"nativeType": "text"
},
{
"fieldName": "sum1",
"visible": true,
"nativeType": "numeric"
},
{
"fieldName": "max1",
"visible": true,
"nativeType": "numeric"
}
]
}
}summarize_complete
This function returns the data of the selected columns in the dataset. Calculations on different columns affect each other. After grouping by non-aggregated columns, the aggregated columns are then calculated. Unlike SUMMARIZE, it allows you to add filter conditions, sorting, and pagination information.
- Function Syntax
text
summarize_complete({dataset expression},
[expr_1,expr_2 ...],
[filter_expr_1,filter_expr_2 ...],
[having_expr_1,having_expr_2 ...],
[sort_expr_1,sort_expr_2...],
offset, limit)Parameter Description
- dataset expression: Required. An expression that returns a dataset as the result.
- [expr_1,expr_2 ...]: Required. No limit on the array length. If the array is empty, it means retrieving information for all fields. All non-aggregated fields in the expression will participate in the group calculation.
- [filter_expr_1,filter_expr_2 ...]: Required. Filter expressions. The relationship between different expressions is AND. If empty, it means there are no filter conditions.
- [having_expr_1,having_expr_2 ...]: Required. Filter expressions. The relationship between different expressions is AND. If empty, it means there are no filter conditions. This is the filter for aggregate expressions, corresponding to HAVING in SQL.
- [sort_expr_1,sort_expr_2...]: Required. Sort fields. When writing expressions, specifying the order is not supported here; all are sorted in ascending order. However, when writing the HE struct, the order can be specified.
- offset: Required. The offset for retrieving data.
- limit: Required. The limit on the number of records to retrieve.
Example 1: Writing an Expression
text
summarize_complete(dataset(2),[{month},count({id})],[{id}>1,{id}<50],[avg({day})>1],[{month}],6,3)The result is as follows:
json
{
"data": {
"data": [
[
8,
2
],
[
9,
2
],
[
10,
4
]
],
"schema": [
{
"fieldName": "month",
"visible": true,
"type": "number"
},
{
"fieldName": "_hs_uid_0",
"visible": true,
"type": "number"
}
]
}
}- Example 2: Writing an HE Struct
json
{
"kind": "function",
"op": "summarize_complete",
"args": [
{
"kind": "function",
"op": "dataset",
"args": [
{
"kind": "constant",
"op": 2
}
]
},
[
{
"kind": "field",
"op": "month",
"uid": "month-t1"
},
{
"kind": "function",
"op": "count",
"args": [
{
"kind": "field",
"op": "id"
}
]
}
],
[
{
"kind": "function",
"op": ">",
"args": [
{
"kind": "field",
"op": "id"
},
{
"kind": "constant",
"op": 1
}
]
},
{
"kind": "function",
"op": "<",
"args": [
{
"kind": "field",
"op": "id"
},
{
"kind": "constant",
"op": 50
}
]
}
],
[
{
"kind": "function",
"op": "!=",
"args": [
{
"kind": "reference",
"op": "month-t1"
},
{
"kind": "constant",
"op": 6
}
]
}
],
[
{
"kind": "field",
"op": "month",
"direction": "desc"
}
],
{
"kind": "constant",
"op": 6
},
{
"kind": "constant",
"op": 3
}
]
}The result is as follows:
json
{
"data": {
"data": [
[
5,
3
],
[
4,
5
],
[
3,
5
]
],
"schema": [
{
"fieldName": "month-t1",
"visible": true,
"type": "number"
},
{
"fieldName": "_hs_uid_0",
"visible": true,
"type": "number"
}
]
}
}