Aggregation instructions - Simple Log Service - Alibaba Cloud Documentation Center

stats

The stats instruction performs statistical analysis on logs, similar to SQL aggregate functions such as COUNT, SUM, and AVG. It groups and aggregates specific fields in log data.

Important

This instruction is used only for query and analysis in a LogStore. It does not apply to scenarios such as data transformation, Structured Process Language (SPL) rule consumption, Ingest Processors, or Logtail configurations.
By default, the stats instruction returns the first 100 aggregation results. To return more results, use the limit instruction.

Syntax

stats <output>=<aggOperator> by <group>,[<group>...]

Parameters

Parameter	Type	Required	Description
output	String	Yes	Specifies an alias for the statistical result field.
aggOperator	SQLExp	Yes	The following aggregate functions are supported: count count_if min max sum avg skewness kurtosis approx_percentile approx_distinct bool_and bool_or every arbitrary array_agg
group	String	No	Specifies the dimension for aggregation. This is similar to the GROUP BY field in SQL.

Examples

Example 1: Calculate the pv of access logs by ip.

SPL statement
```
* | stats pv=count(*) by ip
```

Input data

ip: 192.168.1.1
latencyMs: 10

ip: 192.168.1.1
latencyMs: 20

ip: 192.168.1.2
latencyMs: 10

Output data

ip: 192.168.1.2
pv: 1

ip: 192.168.1.1
pv: 2

Example 2: Calculate the min/max latency for all ip addresses in the accesslog.

SPL statement

* 
| extend latencyMs=cast(latencyMs as bigint)
| stats minLatencyMs=min(latencyMs), maxLatencyMs=max(latencyMs) by ip

Input data

ip: 192.168.1.1
latencyMs: 10

ip: 192.168.1.1
latencyMs: 20

ip: 192.168.1.2
latencyMs: 10

Output data

ip: 192.168.1.2
minLatencyMs: 10
maxLatencyMs: 20

ip: 192.168.1.1
minLatencyMs: 10
maxLatencyMs: 10

Example 3: Calculate the total pv in an access log.

SPL statement
```
* | stats pv=count(*)
```

Input data

ip: 192.168.1.1
latencyMs: 10

ip: 192.168.1.1
latencyMs: 20

ip: 192.168.1.2
latencyMs: 10

Output data
```
pv: 3
```

sort

The sort instruction orders query results by field values or statistical results in ascending (asc) or descending (desc) order.

Important

This instruction is used only for query and analysis in a LogStore. It does not apply to scenarios such as data transformation, SPL rule consumption, Ingest Processors, or Logtail configurations.

Syntax

sort <field> [asc/desc] ,(<field> [asc/desc])

Parameters

Parameter

Type

Required

Description

field

String

Yes

Specifies the field to sort by. The following field types are supported:

Original log fields, such as status and request_time.
Statistical fields, such as count(*) and avg(response_time).
Time fields, such as @timestamp.

asc/desc

String

No

asc: Sorts in ascending order (default).
desc: Sorts in descending order.

Example

Sort accesslog by latencyMs.

SPL statement

* 
| extend latencyMs=cast(latencyMs as bigint) 
| sort latencyMs desc

Input data

ip: 192.168.1.1
latencyMs: 10

ip: 192.168.1.1
latencyMs: 20

ip: 192.168.1.2
latencyMs: 15

Output data

ip: 192.168.1.1
latencyMs: 20

ip: 192.168.1.2
latencyMs: 15

ip: 192.168.1.1
latencyMs: 10

limit

The limit instruction restricts the number of log rows returned in query results, helping prevent performance issues from excessively large result sets.

Important

This instruction is used only for query and analysis in a LogStore. It does not apply to scenarios such as data transformation, SPL rule consumption, Ingest Processors, or Logtail configurations.
If you do not use the sort instruction to specify a collation, the output of the limit instruction is in a random order. This is because the natural order of logs is not guaranteed during storage.

Syntax

limit (<offset>,) <size>

Parameters

Parameter	Type	Required	Description
offset	Integer	No	Skips the first `offset` rows.
size	Integer	Yes	The row limit.

Example

Sort an access log by the latencyMs field and then retrieve the first row.

SPL statement

* 
| extend latencyMs=cast(latencyMs as bigint) 
| sort latencyMs
| limit 1

Input data

ip: 192.168.1.1
latencyMs: 10

ip: 192.168.1.1
latencyMs: 20

ip: 192.168.1.2
latencyMs: 15

Output data
```
ip: 192.168.1.1
latencyMs: 20
```