Quick Guide
- Add @op to the end of a query anywhere queries are expected
- Most operators take parameters; separate them with :
- Operators are case insensitive but the parameters may not be, depending on the parameter
- Chain as many operators as you like, beginning each one with @
- Operators are executed in order, with the below exceptions:
- Aggregation operators are executed last.
- However, @localize will be executed before the first d, w, M, q, or y aggregation
- For @asof, @reppdate, and @repfdate, the order doesn't matter
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.
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 |
@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 |
@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 @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 @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 |
@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 ! 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 |
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 (ex. @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" (ex. 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
Period Notation Examples
Period Notation Example | Meaning |
y | Yearly periods |
10y | Ten year periods |
15m | 15-minute periods |
15me | 15-minute-ending periods |
he | Hour-ending periods |