windowcomp

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.

Example 120.

| 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

Supported functions

This stage includes the following functions:

Function Type	Function
Numbering functions	rank row_number
Navigation functions	first_value lag last_value
Statistical aggregate functions	stddev_sample stddev_population
Aggregate functions	avg count max median min sum

Optional parameters

The optional parameters available to define in the windowcomp function are explained in the following table:

Optional parameters	Syntax	Description
By clause	[by <fieldA> [,<fieldB>,...]	The by clause is used to break up the input field rows into separate partitions, over which the windowcomp function is independently evaluated. Multiple partition fields are allowed when using a partition by clause. When this optional clause is omitted, all rows in the input table comprise a single partition.
Sort	`[sort [asc	desc] [,[asc
Between window frame clause	`[between 0	null
frame_type	`[frame_type=rows	range]`
Alias clause	[as <alias>]	Use the alias clause to provide a column label (field name) for the windowcomp results. When the new field name already exists in the schema, it's replaced with the new name. **Example 122. **null

Examples

Data table for ips dataset

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

Query 1: Compute the total logins for all IPs

dataset = ips 
| windowcomp sum(logins) as total_logins

Output results table

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

Query 2: Compute a subtotal for each category

dataset = ips 
| windowcomp sum(logins) by category sort asc logins between null and null as total_logins

Output results table

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

Query 3: Compute a cumulative sum for each category

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

Output results table

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

Query 4: Compute a cumulative sum, where only preceding rows are analyzed.

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

Output results table

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

Query 5: Compute a changing average

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

Output results table

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

Query 6: Retrieve the most popular IP in each category

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

Output results table

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

Query 7: Calculate the rank of each IP within the category based on the login

dataset = ips 
| windowcomp rank() by category sort asc logins as rank

Output results table

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

Query 8: Retrieve the most popular IP in a specific window frame by range and not category

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

Output results table

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

Query 9: Retrieve the number of IPs that have similar logins

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

Output results table

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