OQL Syntax Reference

This page describes how OQL queries are structured. Keywords such as aggregate and where are not case-sensitive.

Query form

Every query uses AGGREGATE. You can optionally define helper queries with WITH, then read from them with FROM.

A file may also contain a standalone macro (a saved snippet used inside other queries).

Top-level structure

[WITH helper1 [(col1, col2, ...)] AS ( <query or macro> ), ...]
<aggregate query>
[UNION ALL <aggregate query>]*
  • WITH defines helper queries used later in the same statement.
  • Only UNION ALL is supported (not plain UNION).
  • Comments start with -- and run to the end of the line.

AGGREGATE query

AGGREGATE <expression> as <name>, ...
[FROM <helper_name>]
[JOIN <helper_name> USING (col, ...)]*
[WHERE <expression> [AND <expression>]*]
[GROUP BY <dimension> as <name>, BUCKET <duration> as <name>, ...]
[WINDOW <name> AS (...)] [, <name> AS (...)]*
[ORDER BY <name> [ASC|DESC]] [, ...]
[HAVING <expression> [, <expression>]*]
[APPLY honk2science <func>(key = value, ...)] [, ...]
[LIMIT <n>]
[OFFSET <n>]
[SETTINGS key = value [, ...]]

What each part does

Clause Required? Plain-language summary
aggregate ... as name Yes What to measure; always name columns with as
where time from ... to ... Usually Which time period to include
group by ... as name No How to split results into rows; name each grouping column
from helper No Read from a with helper instead of raw data
window No Rolling or row-to-row calculations; cannot combine with group by in the same query
having No Filter rows after totals are calculated
limit / offset No Return only some rows
settings No Caching, timezone, etc. — see Settings

Tip: If you omit an aggregate wrapper around metric('uuid') or metric_group('uuid'), OQL assumes avg(...).

GROUP BY and time buckets

  • group by line.name as linename — one row per line
  • group by bucket 1D as day — one row per day
  • Duration units: s, m, h, D, W, M, Q, Y

WITH / helper queries

WITH hourly (linename, total) AS (
    AGGREGATE sum(metric_group('uuid')) AS total
    WHERE time FROM now()-7D TO now()
    GROUP BY line.name AS linename, bucket 1h AS hour
)
AGGREGATE avg(hourly.total) AS avg_total
FROM hourly
GROUP BY linename

Helper bodies can also be parameterized macros. Name helper columns with as so the outer query can reference them.

See FROM Clause and JOIN Clause.

FROM vs JOIN

from — summarize or reshape one helper table:

AGGREGATE avg(hourly.total) AS avg_total
FROM hourly
GROUP BY linename

join — combine a helper with new calculations (for example, benchmark vs today):

WITH benchmark AS ( ... )
AGGREGATE benchmark.p75 AS p75_30d, p75(metric_group('uuid')) AS p75_today
JOIN benchmark USING (linename)
WHERE time FROM now()-24h TO now()
GROUP BY line.name AS linename

WHERE clause

WHERE TIME FROM <start> TO <end>
[AND <filter>]*
-- or
WHERE TIME BETWEEN <start> AND <end>

Every query that does not use from needs a time range in where.

Time values can be fixed dates, now(), now()-30D, watermark(), date_trunc(...), or adjustTimeToInterval(...).

See WHERE Clause.

HAVING, WINDOW, UNION ALL, APPLY

  • having — filter calculated totals; see HAVING Clause
  • window ... over w — rolling sums, lag/lead; see Window Functions. Cannot use group by in the same query.
  • union all — stack two aggregate result sets
  • apply honk2science ... — advanced data-science steps; see Data Science Functions

Expressions and filters

Comparators

=, !=, <, >, <=, >=, IN, NOT IN, IS, IS NOT, CONTAINS

Boolean logic

Use and, or, and not (with a space before the next condition). Write OR with a trailing space so it is not confused with order.

Common entity fields

Topic Examples
Line line.name, line.id, line.attribute.<name>
Factory factory.name, factory.id
Run / batch / state interval.run.name, interval.batch.yield, interval.state.type
Product product.name, product.id
KPIs aggregation.oee, aggregation.output, …
Time breakdown time.hour_of_day, time.day_of_week, bucket 1h

Custom intervals: interval.<type>('uuid').start, .value, etc.

Full list: Expressions Reference.

Built-in KPIs (default meanings)

Reference What it measures (default)
aggregation.utilization Uptime ÷ total time in range
aggregation.availability Uptime ÷ (total time − planned downtime)
aggregation.oee Availability × utilization
aggregation.performance Average of the line’s performance metric
aggregation.quality Yield-based quality ratio
aggregation.output Production output
aggregation.yield / aggregation.scrap Yield and scrap totals

Your organization may override these via macros.

Limitations

Topic Detail
Plain UNION Not supported — use UNION ALL
GROUP BY + WINDOW Not in the same query — use with + from
Nested helpers Some complex nested helpers are not supported
Unknown function or field name Query fails with an error

See Functions Reference for supported function names.