Operators

Quick Overview
Notes
Aggregation Period Notation for Operators
Operator Reference

Quick Overview

Operators are used at the end of SjQL queries to perform various operations to the series being queried.

The basic syntax is series_query@operator:operator_params . You may use several operators in chain by separating them with @. They will be executed in order, with some exceptions:

Aggregation operators are executed last.
@localize will be executed before the first d, w, M, q, or y aggregation
@asof, @reppdate, and @repfdate ignore operator order altogether.

Note: Operators are case insensitive, but their parameters may not be. For example, @A:m is equivalent to @a:m, but @A:M is different from @A:m .

A few sample queries:

Query	Notes
sid=my\series@A:M	Monthly average
sid=my\series@lag:1m	Lag 1 minute
sid=my\series@A:M@S:y	Monthly average, then the yearly sum.

Notes

Operators apply to ALL series in a query. A single query cannot return 2+ series with different operators.
Aggregate Operators can manipulate the date aspect of data during retrieval. Manipulating dates is better suited for operators because Expressions and analytical tools do not have access to the full range of dates for correct manipulation (e.g. doing a daily sum in Excel on the last 24 hours of hourly data will only get the right answer one hour of the day).
Aggregate Operators strip all timestamp and job information. If keeping timestamp and job information is important to your use case, do not use Aggregate Operators.

Aggregation Period Notation for Operators

Aggregation periods in operations are expressed as an optional number followed by the period letter.
By default periods are beginning-based: they include the first time of the period, exclude the last, and aggregate to the current period (eg. @A:d includes 00:00 until 23:59:59.99999 and aggregates to that day at 00:00)
To use ending-based periods, append an e (eg. use he for hour-ending). Resulting periods exclude the first time of the period, include the last, and aggregate to:

the current period for daily+ (ex. @A:de includes 00:00:00.00001 on the current day until 00:00 on the next day and aggregates to the current day at 00:00)
the next period for hourly- (ex. @A:he includes 00:00:00.00001 on the current hour until 00:00 on the next hour and aggregates to the next hour at 00:00)

See Date Input Notation for a reference of the period notation letters

Operator Reference

Operator	Examples	Notes
`@A:<agg-period>`	@A:y	Average Aggregate (see below for agg period formatting reference)
`@S:<agg-period>`	@S:M	Sum Aggregate
`@C:<agg-period>`	@C:q	Count Aggregate
`@WA:<agg-period>:<final_point_period>`	@WA:y:M	Weighted Average Aggregate; weighs based on the duration from one point to the next. For example, @WA:q on monthly data weighs based on number of days in a month. The optional param final_point_period can also be passed when the period only has partial data. For example, @WA:y:M can be used when only a partial year's worth of monthly points are available. The weighted calculation will then only include the period up to the last observed point.
`@H:<agg-period>`	@H:d	High Aggregate
`<@L:agg-period>`	@L:d	Low Aggregate
`@B:<agg-period>`	@B:w	Beginning Aggregate; this excludes the last time of the Aggregation Period, includes the first time, and aggregates to the first time.For Example, if you use @B:d on a series with a from date of 5/1 and end date of 5/31 with hourly underlying data, the value for 5/1 will average the 24 data points from 5/1 00:00 to 5/1 23:00. Note that for data that is not hourly or sub-hourly, users will typically prefer to use Beginning (@B) since Ending (@E) would include 5/2-6/1 in a monthly average of daily data for May, while Interval Beginning would include 5/1-5/31.
`@E:<agg-period>`	@E:15m	Ending Aggregate ; this excludes the first time of the Aggregation Period, includes the last time, and aggregates to the last time.For example, if you use @E:d on a series with a from date of 5/1 and end date of 5/31 with hourly underlying data, the value returned for for 5/1 will average the 24 data points from 5/1 01:00 to 5/2 00:00.
`@T:<operation>:<relative-period>`	@T:A:5d	Trailing Average Aggregate; the operation is one of the above (A,S,C,H,L.B,E); see Relative Period Format in Date Input Notation
`@localize @localize:<timezone>`	@localize @localize:America/Los_Angeles	Localizes to a timezone; assumes Shooju dates are GMT; if timezone is not provided uses the timezone field from the series. Shooju uses the TZ timezone database; see https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
`@forcetz`	@forcetz:UTC @forcetz:America/Chicago	Forces a Timezone onto a series. Use @forcetz:UTC to drop timezone (e.g. for use in comparison to non-timezoned series) Utilized when comparing data from Series in differing regions (e.g weather in Chicago vs weather in Dallas.)
`@lag:<relative-period>`	@lag:3w	Lags by specified relative period; see Relative Period Format in Date Input Notation. This is not an Aggregate Operator.
`@asof:iso` `@asof:iso:timezone` `@asof:relative-period` `@asof:<j-prefixed job_id>`	@asof:2018-01-12 @asof:2018-01-12T07:00:America/New_York @asof:-3w @asof:j3325	Retrieves a snapshot of how the series was stored as a result of a specific job in Shooju (i.e. after that job). Job can be specified with j-prefixed job (e.g. j12 means job #12), or the ISO-formatted UTC date or relative period. The additional param timezone can be optionally included when an iso date is used and should contain the same values as noted in above in @localize. If ISO or relative period is used, the reference job is the first one before that time. See Relative Period Format in Date Input Notation. Note that this operator cannot be chained with another `@asof`. Note that for use with expressions, it usually makes sense to prepend with `@pre` so that the `asof` operator is applied before the expression is executed.
`@reppdate:<iso>`, `@reppdate:<relative period>`, `@repfdate:<iso>`, `@repfdate:<relative period>`	@repdate:2018-01-12T01:01:00 @repdate:-1db +2h @repfdate:iso @repfdate:relative period @reppdate:x@repfdate:x	Retrieves points (@reppdate) or fields (@repfdate) as they were reported by the source as of a specific ISO-formatted UTC datetime or relative period; this must be enabled on a per-source basis; see Relative Period Format in Date Input Notation. The points/fields returned will be from the reported date that matches the iso/relative period or the first reported date that occurs before that time. Note that this operator is not available for all series and can be used together to get both reported points and fields. Note that for use with expressions, it usually makes sense to prepend with `@pre` so that the rep(p/f)date operator is applied before the expression is executed. Note that @repdate means @reppdate and its use is discouraged to avoid ambiguity.
`@filter:<filter-period or value-comparison>`	@filter:7M @filter:2,8M @filter:1-5wd @filter:8M@filter:2wd @filter:>20 @filter:<=20@filter:>10	Filtering for specific dates: the filter-period format uses the Period Notation letters from Date Input Notation and adds a wd period for weekday. Relative dates do not currently work with this operator. Filtering for specific values: the value-comparison format expects a comparison, and then a number. Comparisons include: >, <, <=, >=, =, !=
`@filter:ts<value comparison>`	@filter:ts>-1Y @filter:ts=2000 @filter:ts>2021-01-01 @filter:ts<2020-12-31T23:59:59	Filters for write timestamp. Points have timestamps of when they were written. Request must specify `"include_timestamp" : "y"`.
`@filter:job=<job_id>`	@filter:job=1776844	Filters according to job id, returns the points that were processed in that job. Request must specify `"include_job" : "y"`
`@df:<iso or relative period>`	@df:-10d @df:2017-01-01 @df!:-10d	Specify the beginning period to return points. Value is either specific ISO-formatted date or relative period; see Relative Period Format in Date Input Notation. Note that If both operators and params are used, their intersection (narrowest range) will be returned. This applies to @dt below as well. However, if `!` is applied after the @dt or @dt, it overrides the params, so the operator takes precedent.
`@dt:<iso or relative period>`	@dt:+10w @dt:2030-01-01	Same as @df above, but specifies the ending period.
`@cached:<cache-ttl(,invalidate,resetTtl)>`	@cached @cached:20h @cached:20h,invalidate @cached:20m,resetTtl @cached:20m,invalidate,resetTtl	See Caching for usage.