Aggregation instructions - Simple Log Service - Alibaba Cloud Documentation Center

This topic describes how to use aggregation instructions and provides examples.

stats

Use the stats instruction for the statistical analysis of logs. It is similar to aggregate functions in SQL, such as COUNT, SUM, and AVG. It performs statistical, grouping, and aggregate operations on specific fields in log data.

Important

This instruction is used only for query analysis in Log Service. It does not apply to scenarios such as data transformation, Structured Process Language (SPL) rule consumption, write 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 sorts query results. You can sort field values or statistical results in ascending (asc) or descending (desc) order. This is an important tool for log analysis that helps you quickly locate key data and generate ordered reports.

Important

This instruction is used only for query analysis in Log Service. It does not apply to scenarios such as data transformation, SPL rule consumption, write 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

asc: Sorts in ascending order (default).
desc: Sorts in descending order. This is often used to sort statistical values from high to low.

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 limits the number of log rows returned in the query results. It is a core instruction for controlling data volume. Using limit helps prevent performance issues or resource waste caused by excessively large query results. It is suitable for various scenarios, such as log analysis and real-time monitoring.

Important

This instruction is used only for query analysis in Log Service. It does not apply to scenarios such as data transformation, SPL rule consumption, write 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
```