How do I write aggregate queries in Oden Query Language (OQL)

This article discusses how to write custom queries to aggregate data with OQL, including query structure, available functions, and samples.

Table of Contents

1. Overview

2. Query Structure

3. Sample Queries

4. A Note on Backends

5. The Aggregate Clause

6. The Where Clause

7. The Group By Clause

8. The Order By Clause

9. The Having Clause

10. The Settings Clause

11. Change Log

Overview

Whenever data is presented in Oden, whether in Now, Explore, or Dashboards, some query is performed to your dataset with specific filters, aggregations, and groupings. Oden Query Language (OQL) is the language used for these queries behind-the-scenes, but many features are now providing advanced users with more control to customize queries by writing them directly.

An aggregate query explicitly returns one or more pieces of data aggregated over specified data groupings. For example, an OQL query could be written to answer questions such as "Average Speed Grouped By Line" or "Total Downtime by Day."

Query Structure

An aggregate query has three required expressions: aggregation, filter, and grouping.

The aggregation expression determines what data points will be returned and how they are manipulated into a single value for each group. These start the query and are indicated with the keyword aggregate. The aggregate clause can include multiple expressions.

The filter expression determines what data will be fed into the aggregations. These come second in the query using the keyword where, which must include a relative or absolute time filter and can include additional filters.

And the grouping expression defines how to separate the input data before aggregation. These come last in the query and use the keywords group by and bucket.

In addition to these required expressions, optional expressions can be added. These currently include the having expression, which allows filtering on the results of an aggregate, and the order by expression, which allows for specifying data sorting.

Sample Queries

Here are a few example queries to show the general format and syntax. Further explanations of each query component and its supported capabilities will be included below.

General Aggregate Query Structure

Scrap Rate and Uptime Hours (prev. 30 days)

This query computes the scrap rate and hours of uptime over the last 30 days. The results are grouped by line, bucketed by day, and ordered by scrap rate descending. The results are further constrained to show lines on days with high scrap rates (above 20%).

Downtime Categorization Rate (prev. 13 weeks)

This query will return the percentage of categorized downtime. This assumes that your downtime categories have IDs 3, 4, 5, and 6, while 2 is the universal ID for uncategorized downtime. Category 1 is the universal ID for uptime, so this query assumes there are no additional uptime categories for your organization. A complete list of downtime categories and their IDs can be found in the Manage Organization portal under the State Categories tab.

A Note on Backends

As Oden upgrades to a more performant backend, OQL has support for directing the underlying query to one of two backends:

• v0: default backend used in Now, Explore, and legacy dashboard modules

• v1: new backend

The Aggregate Clause

The first piece of an OQL query is the aggregate clause. The aggregate clause should be followed by one or more expressions that define an aggregation to be computed. Each expression comprises a series of 1 or more aggregate functions, with the option to include an alias for that expression.

If more than one expression is defined, they must be separated by a comma. New lines are not required but are highly recommended for readability. This section covers the following:

• Math Operands

• Logical Comparisons

• Interval Functions

• Metric Functions
  
  

  • Metric Reference Functions

  • Metric Functions

• Standard KPIs

• Metadata Functions

• Aliasing

Math Operands

The following math operands are available and follow the standard order of operations. Parenthesis can be used to control order precisely:

Examples:

Logical Comparisons

Comparators will provide a binary result for numeric values or expressions on either side. They can be utilized for generating intervals. Please contact your Oden representative for further details on this functionality.

Examples:

Interval Functions

Interval functions provide methods for summarizing interval events according to standard groupings such as line, factory, or day.

Examples:

Metric Reference Functions

To perform aggregations on metric data, metric functions require a reference to a metric as an input. Metrics can be identified either by their raw metric or metric group (which can fetch multiple metrics across lines):

Metric Functions

Metric aggregate functions will perform aggregations on a time series of metric data, as identified through a metric reference function.

Examples:

Standard KPI Aggregates

The following standard KPIs are available in the aggregate expression and are performed on lines specified in the where clause. Detailed definitions of Oden KPIs can be found here.

Metadata Functions

Metadata functions return metadata associated with metric groups or interval events. They are comprised of a specifying the entity type and property, separated by a period:

Examples:

Aggregate Aliases

Aliases assign display names (utilized by visualizations) to aggregate clauses, allowing them to be easily referenced in having or order by statements. Aliases are specified by the as keyword, as shown in the examples below:

or

Examples:

Display Formatting with Aliases

Some characters or keywords can be used in the alias to affect how that aggregate will be displayed in dashboard visualizations.

Formatting as Percent - Add a % character anywhere in the alias string, and the % will be appended to data when displayed. Note that this does not multiply by 100, so you will still have to do that with math operands if the value still needs to be converted to a percentage.

Hiding Columns - use the alias keyword hide to hide a given column. This can be used if columns are needed for grouping, ordering, or filtering with a having clause but aren’t adding much information.

The Where Clause

The where clause applies filters to specified aggregations.

It is required that all where clauses specify a time range with the keyword time. The time range may be absolute or relative, and additional filters can be added as a list separated by and.

Additional filters may select from a list of comparison operators to define the filter conditions.

1. Time Range Functions

2. Filtering Entities

3. Comparison Operators

Time Range Functions

Absolute times can be specified with the format yyyy-mm-dd hh:mm:ss with hours on a 0-23 scale. Relative time ranges are used for dynamic time range filters relative to the current time when the query is executed.

Examples:

Filtering Entities

Filters can also be applied to most entities available in Oden. Below is a list of entities available for filtering in the where clause:

Comparison Operators

Comparison operators are used in the where clause to build conditional statements for filtering. The following comparison operators are supported:

The Group By Clause

The group by clause is optional and is used to group aggregations by specific entity properties or time buckets. Grouping allows results with the same value to be summarized into one group or bucket. A group by clause can contain one or more comma-separated grouping entities, and the most basic/common grouping is used to bucket time for summarized time-series data.

1. Grouping Entities

2. Bucketing Durations

Example:

Grouping Entities

The following grouping entities are supported:

Bucketing Durations

To group by a time bucket, use the bucket keyword followed by a value that is an unsigned integer preceding a duration. The following durations are supported:

The Order By Clause

The order by clause allows you to order the result set by one or more columns. The columns can either be grouping columns or columns in the target list of the aggregate query. By default, the order by clause sorts resulting data in ascending order (asc) but can be modified to sort in descending order (desc).

Examples:

The Having Clause

The having clause allows you to filter query results. This operation differs from the where clause, which filters the underlying data before computing aggregates. The having clause is often used alongside an order by clause, which allows you to order the results.

Example:

Unlike the order by clause, which can order query results by grouping columns or aggregate columns, the having clause can only operate on aggregate columns (i.e., one of the expressions in the target list of an aggregate query) and takes one or more numeric arguments.

The Settings Clause

The settings keyword can be used at the end of a query to specify options for executing a query.

Examples:

Change Log

This log indicates additions and edits to the documentation on this page.