Aggregate Functions
Available in Seq SQL queries
The following operators can be used in aggregate queries.
Any/All
Given an expression, return true
if the expression is true
for any
or all
events in the stream.
select any(@Level = 'Error') from stream
Bottom
Given an expression, compute the last N values that appear for that expression.
The bottom
function cannot appear with any other aggregate functions.
select bottom(StatusCode, 5) from stream group by RequestPath
The default ordering for select
queries on stream
is time-ascending. See also: top()
, first()
, last()
.
Count
Given a property name, computes the number of events that have a non-null
value for that property.
The special property name *
can be used to count all events.
select count(*) from stream where has(@Exception)
Distinct
Given a property name or expression, computes the set of distinct values for that expression.
The distinct
function cannot appear with any other aggregate functions.
select distinct(ExceptionType) from stream where Application like 'Admissions%'
distinct()
and count()
can be combined to count distinct values without returning them all.
select count(distinct(ExceptionType)) from stream
First/Last
Given an expression, returns the value of that expression applied to the first or last events in the target range.
select first(Elapsed), last(Elapsed) from stream group by time(5m)
Interval
In a query that groups by time, the duration of each time slice.
select count(*) / (interval() / 1d) as per_day
from stream
group by time(30d)
Min/Max
Given a numeric expression or property name, computes the smallest or largest value for that expression.
select min(Elapsed / 1000) as ElapsedSeconds from stream group by time(1h)
Mean
Computes the arithmetic mean (average) of a numeric expression or property, i.e. sum(expr) / count(expr)
.
select mean(ItemCount) from stream group by ItemId
Events where the expression is null or not numeric are ignored and do not contribute to the final result.
Percentile
Given an expression and a percentage p
, calculates the value of the expression at or below which p
percent of the results fall.
select percentile(ResponseTime, 95) from stream group by time(1h), MachineName
This is the nearest rank percentile.
Sum
Given an expression or property name, calculates the sum of that value.
select sum(ItemsOrdered) from stream group by ItemId
Non-numeric results are ignored.
Top
Select the first N values of an expression.
The top
function cannot appear with any other aggregate functions.
select top(StatusCode, 5) from stream group by RequestPath
The default ordering for select
queries on stream
is time-ascending. See also bottom()
, first()
, last()
.
Updated 5 months ago