Query operations
Query operations shape the result of a NetScript expression. Use them to group, filter, and calculate moving totals, and to set parameters such as the time grain. Add each operation after the expression it modifies, separated by a pipe (|). Before you start, review the NetScript language reference for formula syntax.
by (PROPERTY)
Use the by operation to break a value down by properties, like a Structured Query Language (SQL) GROUP BY clause. For example, group revenue by country to compare markets. Add by in one of the following places:
- Directly after an aggregate function, such as sum by (Orders.ShipMode).
- After an aggregated result, to slice it further. by (PROPERTY_1, PROPERTY_2) is the same as by (PROPERTY_1) | by (PROPERTY_2).
Analytics joins the dataset of each property for you, based on the relationships in your data model.
Function
INPUT | by (PROPERTY_1, PROPERTY_2)Data type – input type
Example
Orders.Amount | sum by (time_bucket(Orders.OrderDate)) | by (Orders.ShipMode)Result
The total amount for each time bucket and ship modewhere (CONDITION)
Use the where operation to include only the rows that match a condition, such as orders from one region. The filter keyword is an alias and works the same way. The where operation follows these rules:
- The condition must return a boolean.
- Analytics applies the condition to the rows before it aggregates them.
- Inside the condition, $0 refers to the value of the input expression.
- To filter the aggregated results instead of the input rows, apply freeze first.
Function
INPUT | where (CONDITION)Data type – input type
Example
Orders | count | where (Orders.ShipMode == 'priority')
Orders.Amount | sum | where (Orders.OrderDate in last 30 days)Result
The number of priority orders
The total amount of orders from the last 30 daysfreeze(INPUT)
Use the freeze function to lock an aggregated result so that later operations do not recalculate it. Use it to filter on an aggregated value, like a SQL HAVING clause. Also use it to add a property to a result without regrouping the result. Operations after freeze work as follows:
- A where operation after freeze filters the aggregated results instead of the input rows.
- A by operation after freeze adds the property to each existing result without regrouping the aggregation.
Function
INPUT | freeze
freeze(INPUT)Data type – input type
Example
x = Orders.Amount | sum by (Orders.Country) | freeze;
x | where (x > 100)Result
The total amount for each country whose total is greater than 100project(INPUT, EXPRESSION)
Use the project function to return a value for each row of an input. For example, return the order date of each priority order. The result keeps the grouping and filters of the input, but returns the expression value instead of the input value.
Function
INPUT | project(EXPRESSION)
project(INPUT, EXPRESSION)Data type – expression type
Example
Orders | where (Orders.ShipMode == 'priority') | project(Orders.OrderDate)Result
The order date of each priority orderover (WINDOW)
Use the over operation to calculate a moving or cumulative aggregate over time, such as month-to-date revenue.
When the input has no time bucket, Analytics groups it by time_bucket at the grain that TIME_GRAIN sets. The over operation supports the count, count_distinct, sum, and avg aggregate functions.
The following window types are available:
- to date – Calculates a cumulative value from the first time bucket of the result.
- N TIME_UNIT to date – Calculates a cumulative value that restarts every N time units, such as 1 month to date for month-to-date values.
- trailing N TIME_UNIT over TIME_PERIOD – Calculates the aggregate over each time bucket and the buckets before it, covering N time units. Replace TIME_PERIOD with a time range, such as last 30 days.
- following N TIME_UNIT over TIME_PERIOD – Calculates the aggregate over each time bucket and the buckets after it, covering N time units. Replace TIME_PERIOD with a time range, such as last 30 days.
The trailing or following duration must be a literal, such as 7 days. Use a multiple of the time grain. Otherwise, Analytics rounds the window to the nearest whole number of time buckets. For this calculation, a month counts as 30 days.
Function
INPUT | over (to date)
INPUT | over (N TIME_UNIT to date)
INPUT | over (trailing N TIME_UNIT over TIME_PERIOD)
INPUT | over (following N TIME_UNIT over TIME_PERIOD)Data type – number
Example
Events | count | over (trailing 7 days over last 30 days) | bind (TIME_GRAIN: 1 day)
Orders.Amount | sum | over (1 month to date) | bind (TIME_GRAIN: 1 day)Result
For each day: the event count for that day and the six days before it
For each day: the total amount from the start of the month through that daybind (PARAMETER: VALUE)
Use the bind operation to set a parameter value, so one formula works at any time grain or time range. Reference a parameter with $NAME. Use $(NAME: DEFAULT_VALUE) to supply a fallback value when no binding exists. Analytics uses the following standard parameters:
- TIME_GRAIN – The size of each time bucket, such as 1 day.
- TIME_RANGE – The time range of the exploration, such as last 30 days.
When an expression binds the same parameter at several levels, the outermost bind takes effect. To make an inner value take precedence, use bind_final instead. A value in a bind cannot reference another parameter in the same bind.
Function
INPUT | bind (NAME_1: VALUE_1, NAME_2: VALUE_2)
INPUT | bind_final (NAME_1: VALUE_1)Data type – input type
Example
Orders | count by (time_bucket()) | bind (TIME_GRAIN: 1 day)
$x | bind (x: 3) | bind (x: 10)Result
The number of orders for each day
10