Learn more about the Cortex Query Language windowcomp
stage that precedes functions calculating statistics.
Syntax
windowcomp <analytic function> (<field>)[by <fieldA> [,<fieldB>,...]] [sort [asc|desc] <field1> [, [asc|desc] <field2>,...]] [between 0|null|<number>|-<number> [and 0|null|<number>|-<number>] [frame_type=range]] [as <alias>]
Note
Defining a field with an analytic function is optional when using a count function. For rank and row_number functions, it's not allowed.
Description
The windowcomp
stage precedes functions calculating statistics. The results compute values over a group of rows and return a single result for each row, for all records that contain matching values for the fields identified using a combination of the by clause, sort, and range. Only one function can be defined per field, while the other parameters are optional. Yet, it's possible to define multiple fields.
| windowcomp sum(field_1) by field_2 sort field_3 as field_4, min(field_5) by field_6 sort field_7 as field_8
This stage includes the following functions:
Function Type | Function |
---|---|
Numbering functions | |
Navigation functions | |
Statistical aggregate functions | |
Aggregate functions |
The optional parameters available to define in the windowcomp
function are explained in the following table:
Optional parameters | Syntax | Description |
---|---|---|
By clause |
| The
|
Sort |
| Defines how field rows are ordered within a partition as either ascending ( |
Between window frame clause |
| Sets the window frame around the current row within a partition, over which the window function is evaluated. Numbering functions and the
If the between null and 0 |
frame_type |
| Defines the option of the frame as either:
|
Alias clause |
| Use the When the new field name already exists in the schema, it's replaced with the new name. Example 83. If the dataset = xdr_data | windowcomp sum(field_a) as existing_field |
Examples
The examples provided are based on the following data table for a dataset called ips
:
ip | category | logins |
---|---|---|
192.168.10.1 | pc | 23 |
192.168.10.2 | server | 2 |
192.168.20.1 | pc | 9 |
192.168.20.4 | server | 8 |
192.168.20.5 | pc | 2 |
192.168.30.1 | pc | 10 |
dataset = ips | windowcomp sum(logins) as total_logins
ip | logins | category | total_logins |
---|---|---|---|
192.168.10.2 | 2 | server | 54 |
192.168.20.5 | 2 | pc | 54 |
192.168.20.4 | 8 | server | 54 |
192.168.20.1 | 9 | pc | 54 |
192.168.30.1 | 10 | pc | 54 |
192.168.10.1 | 23 | pc | 54 |
dataset = ips | windowcomp sum(logins) by category sort asc logins between null and null as total_logins
ip | logins | category | total_logins |
---|---|---|---|
192.168.10.2 | 2 | server | 10 |
192.168.20.4 | 8 | server | 10 |
192.168.20.5 | 2 | pc | 44 |
192.168.20.1 | 9 | pc | 44 |
192.168.30.1 | 10 | pc | 44 |
192.168.10.1 | 23 | pc | 44 |
The sum is computed with respect to the order defined using the sort
clause. These two queries produce the same results:
dataset = ips | windowcomp sum(logins) by category sort asc logins between null and 0 as total_logins
OR
dataset = ips | windowcomp sum(logins) by category sort asc logins between null as total_logins
ip | logins | category | total_logins |
---|---|---|---|
192.168.10.2 | 2 | server | 2 |
192.168.20.4 | 8 | server | 10 |
192.168.20.5 | 2 | pc | 2 |
192.168.20.1 | 9 | pc | 11 |
192.168.30.1 | 10 | pc | 21 |
192.168.10.1 | 23 | pc | 44 |
The analysis starts two rows before the current row in the partition.
dataset = ips | windowcomp sum(logins) sort asc logins between null and -2 as total_logins
ip | logins | category | total_logins |
---|---|---|---|
192.168.10.2 | 2 | server | NULL |
192.168.20.5 | 2 | pc | NULL |
192.168.20.4 | 8 | server | 2 |
192.168.20.1 | 9 | pc | 4 |
192.168.30.1 | 10 | pc | 12 |
192.168.10.1 | 23 | pc | 21 |
The lower boundary is 1 row before the current row. The upper boundary is 1 row after the current row.
dataset = ips | windowcomp avg(logins) sort asc logins between -1 and 1 as avg_logins
ip | logins | category | avg_logins |
---|---|---|---|
192.168.10.2 | 2 | server | 2 |
192.168.20.5 | 2 | pc | 4 |
192.168.20.4 | 8 | server | 6.33333 |
192.168.20.1 | 9 | pc | 9 |
192.168.30.1 | 10 | pc | 14 |
192.168.10.1 | 23 | pc | 16.5 |
Defines how rows in a window are partitioned and ordered in each partition.
dataset = ips | windowcomp last_value(ip) by category sort asc logins between null and null as most_popular
ip | logins | category | most_popular |
---|---|---|---|
192.168.10.2 | 2 | server | 192.168.20.4 |
192.168.20.4 | 8 | server | 192.168.20.4 |
192.168.20.5 | 2 | pc | 192.168.10.1 |
192.168.20.1 | 9 | pc | 192.168.10.1 |
192.168.30.1 | 10 | pc | 192.168.10.1 |
192.168.10.1 | 23 | pc | 192.168.10.1 |
dataset = ips | windowcomp rank() by category sort asc logins as rank
ip | logins | category | rank |
---|---|---|---|
192.168.10.2 | 2 | server | 1 |
192.168.20.4 | 8 | server | 2 |
192.168.20.5 | 2 | pc | 1 |
192.168.20.1 | 9 | pc | 2 |
192.168.30.1 | 10 | pc | 3 |
192.168.10.1 | 23 | pc | 4 |
The window frame analyzes up to three rows at a time.
dataset = ips | windowcomp last_value(ip) by category sort asc logins between -1 and 1 as most_popular
ip | logins | category | most_popular |
---|---|---|---|
192.168.10.2 | 2 | server | 192.168.20.4 |
192.168.20.4 | 8 | server | 192.168.20.4 |
192.168.20.5 | 2 | pc | 192.168.20.1 |
192.168.20.1 | 9 | pc | 192.168.30.1 |
192.168.30.1 | 10 | pc | 192.168.10.1 |
192.168.10.1 | 23 | pc | 192.168.10.1 |
Count in range of -1 and 1 from their login value.
dataset = ips | fields ip, category , logins | windowcomp count() sort asc logins between -1 and 1 frame_type = range as similar_logins
ip | logins | category | similar_logins |
---|---|---|---|
192.168.10.5 | 2 | pc | 2 |
192.168.10.2 | 2 | server | 2 |
192.168.20.4 | 8 | server | 2 |
192.168.20.1 | 9 | pc | 3 |
192.168.30.1 | 10 | pc | 2 |
192.168.10.1 | 23 | pc | 1 |