Projections and decorators
A projection is one output column: a field, optionally decorated. Decorators change how the column is computed (truncate a date to months, turn a measure into a running sum or a percent of total) without you writing SQL. This page covers the projection keys and every decorator.
ProjectionSpec
{"field": "ws_net_paid", "alias": "Web Net Paid", "order_by": "desc",
"decorators": [{"type": "window", "mode": "running", "function": "sum"}]}
| key | type | default | notes |
|---|---|---|---|
field | string or {"uid": "..."} | required | a uid, a name or a synonym, optionally suffixed @d / @m. Missing: projection N needs a field. See field references. |
field_type | string | none | kind hint, same vocabulary as the @ suffix |
alias | string | derived | the output column name; trimmed, blank means none |
order_by | asc or desc | none | case-insensitive. Anything else: 'x' is not an order_by (asc, desc). There is no top-level sort key. |
axis | row, x, y, series, tip, pivot | row | carried for your client’s layout, not planned. tip on a dimension fails: Axis the tooltip holds measures only. |
hidden | bool | false | honoured: the projection takes part in the query but is not a visible column |
format | any JSON | none | carried back untouched |
decorators | DecoratorSpec[] | [] | see below |
calculation, sql, data_type | make the entry a calculation; see Calculations |
Projections keep their listed order. Entries in the separate top-level calculations list follow them. A date projection with no truncate decorator is the raw column; nothing is truncated for you.
Alias derivation
An explicit alias wins. Otherwise the highest-priority decorator lends a label; otherwise the field’s name is used.
| decorator | lent alias |
|---|---|
| truncate | Month(Date) (capitalized grain); raw lends the field name |
| extract | mon(Date), with the part abbreviated: minute min, hour hr, day_of_month dom, day_of_week dow, day_of_year doy, week_of_year woy, month mon, quarter qtr, year yr, day_name day, month_name month, year_month ym |
| contribute | % Web Net Paid of Total (Web Net Paid of Total if the name already holds %) |
| temporalize | LY(Web Net Paid), LQ(...), LM(...), LW(...), D/D(...); prefixed % when percent_change |
| window | Running Sum(Web Net Paid), Moving Avg(Web Net Paid) |
| customize | Custom(Category) |
Decorator priorities: truncate 0, extract 1, contribute 2, temporalize 3, window 3, customize 10000. The highest wins the alias. Calculations reference other columns by this display alias ([Month(Date)]), so give an explicit alias when the derived one is awkward.
Decorators
A decorator is {"type": "<kind>", ...attributes}. Kinds: truncate, extract, temporalize, contribute, window, customize (alias custom). Anything else: Unknown decorator type: X. Only the attributes listed under each kind may be written; any other key fails with unknown attribute 'x' on the <type> decorator of <owner>.
Kind rules:
truncate,extract,customizeapply to dimensions only:X can only be applied to dimensions.truncateandextractalso need a date or datetime field:only date/datetime types can be date truncated.window,contribute,temporalizeapply to measures only.
Two decorators of the same type on one projection are merged: the attributes of the second extend the first. Different types stack; yoy(month(date)) in the expression language produces a truncate and a temporalize on the same projection (and then fails lowering, since temporalize is measure-only).
All SQL below is the engine’s output for the TPC-DS example model with the filter category in_list "men ,children, \"books,com\"" present, which is why every statement carries the same WHERE.
truncate
Bucket a date dimension to a grain.
{"type": "truncate", "grain": "month"}
Expression: month(date). Grains: raw, millisecond, second, minute, hour, day, week, month, quarter, year. Required: Grain must be set.
{"spec": {"projections": [
{"field": "ws_net_paid"},
{"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]}
], "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]}}
SELECT
sum(T0."ws_net_paid") AS "Web Net Paid",
DATE_TRUNC('month', T1."d_date")::DATE AS "Month(Date)"
FROM
web_sales T0
JOIN date_dim T1
ON T0.ws_sold_date_sk = T1.d_date_sk
JOIN item T2
ON T0.ws_item_sk = T2.i_item_sk
WHERE
LOWER(T2."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
DATE_TRUNC('month', T1."d_date")::DATE
extract
Pull one part out of a date dimension.
{"type": "extract", "extract": "day_of_week"}
Expression: extract(day_of_week, date), extract(month_name, date). Parts: minute, hour, day_of_month, day_of_week, day_of_year, week_of_year, month, quarter, year, day_name, month_name, year_month. Required: Extract must be set. The column is grouped like any dimension; the derived alias is the abbreviation from the table above (dow(Date)).
temporalize
Compare a measure to the same measure one period earlier.
{"type": "temporalize", "transform": "month_over_month"}
{"type": "temporalize", "transform": "year_over_year", "percent_change": true}
Expression: mom(web net paid), yoy_pct(web net paid). Transforms: year_over_year, quarter_over_quarter, month_over_month, week_over_week, day_over_day. Required: Transform invalid transform type. percent_change (bool) returns the relative change instead of the prior-period value.
The planner builds the prior period by shifting the date in its own CTE, then joins it back at the projected grain. Month over month, with an ordered month projection and a measure filter:
{"spec": {"projections": [
{"field": "date", "order_by": "asc", "decorators": [{"type": "truncate", "grain": "month"}]},
{"field": "ws_net_paid", "decorators": [{"type": "temporalize", "transform": "month_over_month"}]}
], "filters": [
{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""},
{"field": "ws_net_paid", "predicate": "greater_than", "value": "100"}
]}}
WITH ag47a9128558e109f9cf0b93c01992e0e7 AS (
SELECT
(DATE_TRUNC('month', T1."d_date")::DATE + '1 month'::interval) AS "dim5b35e57",
sum(T0."ws_net_paid") AS "__msrlm_51898eff8"
FROM
web_sales T0
JOIN date_dim T1
ON T0.ws_sold_date_sk = T1.d_date_sk
JOIN item T2
ON T0.ws_item_sk = T2.i_item_sk
WHERE
LOWER(T2."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
(DATE_TRUNC('month', T1."d_date")::DATE + '1 month'::interval)
HAVING
sum(T0."ws_net_paid") > 100
)
SELECT
A0.dim5b35e57 AS "Month(Date)",
A0.__msrlm_51898eff8 AS "LM(Web Net Paid)"
FROM
ag47a9128558e109f9cf0b93c01992e0e7 A0
ORDER BY
A0.dim5b35e57 asc
Each month’s LM(Web Net Paid) is the previous month’s sum: the CTE adds '1 month'::interval to every sold date so that January’s rows land under February. Because the base measure is not itself projected, the statement is only the shifted side. Project ws_net_paid as well and the plan gains a second CTE for the current period, joined on the month.
contribute
A measure as a share of its total.
{"type": "contribute"}
{"type": "contribute", "partition_refs": ["category"]}
Expression: share(web net paid), share(web net paid by category), share_filtered(web net paid by category). Attributes: partition_refs (dimensions the query projects, which bound the total, or -1 for auto) and ignore_partition_filters (default true; share_filtered sets it to false). With no partition the total is the whole measure:
{"spec": {"projections": [
{"field": "ws_net_paid", "decorators": [{"type": "contribute"}]},
{"field": "category"}
], "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]}}
WITH ag61495e144c0531efb642c4545df2e6c5 AS (
SELECT
sum(T0."ws_net_paid") AS "msr9f2f6d5",
T1."i_category" AS "dim841154e"
FROM
web_sales T0
JOIN item T1
ON T0.ws_item_sk = T1.i_item_sk
WHERE
LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
T1."i_category"
), agbe9574d2ae0d53a3a40c210cf88ff8c4 AS (
SELECT
sum(T0."ws_net_paid") AS "__msrtotal_51898eff8"
FROM
web_sales T0
)
SELECT
A0.msr9f2f6d5 / (A1.__msrtotal_51898eff8*1.00) AS "% Web Net Paid of Total",
A0.dim841154e AS "Category"
FROM
ag61495e144c0531efb642c4545df2e6c5 A0
CROSS JOIN agbe9574d2ae0d53a3a40c210cf88ff8c4 A1
The total CTE has no WHERE: ignore_partition_filters is true, so the share is of all web sales, not of the three filtered categories. The *1.00 forces decimal division.
window
Running and moving aggregates, ranks, lag and lead.
{"type": "window", "mode": "running", "function": "sum"}
{"type": "window", "mode": "moving", "function": "avg", "size": 7}
{"type": "window", "mode": "running", "function": "lag", "offset": 2, "partition_refs": ["date"], "order_by": {"category": "desc"}}
Expression: running_sum(web net paid), moving_avg(web net paid, 7 by category), running_sum(web net paid by category order by date). A moving function’s number is rows in the frame, one more than size.
| attribute | default | rule |
|---|---|---|
mode | required | moving or running: Mode must be moving or running |
function | required | avg, sum, min, max, count, row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value |
size | 7 | moving mode frame, in rows; must be at least 1 |
offset | 0 | lag / lead; must be at least 1 |
buckets | 4 | ntile; must be at least 1 |
partition_refs | none | dimensions the query projects, for PARTITION BY, or -1 for auto. A uid, a name or a display alias; each is resolved to its uid, and one that names no projected dimension fails with window on <owner>: 'x' is not a projected dimension. |
order_by | depends on the function, below | map of projected dimension to asc/desc; -1 means the first projected date. Keys resolve like partition_refs, and a key naming a measure fails with 'x' is a measure; a window partitions and orders only by a projected dimension. |
ignore_partition_filters | true | carried |
What a window orders by when you do not say
Left to itself, the ordering follows the function rather than one blanket default, because the useful answer differs:
-
A rank function —
rank,dense_rank,row_number,percent_rank,cume_dist,ntile— orders by the measure it decorates, descending.rank(web net paid)with nothing else said is the rank by that measure, which is what asking for a rank means.rank() OVER ( ORDER BY count(*) desc) AS "Rank" -
lag,lead,first_value,last_valueand the running and moving aggregates order by the query’s date, which is{"-1": "asc"}— the first projected date, ascending. A running total over time is the thing those are for.lag(count(*), 1) OVER ( ORDER BY T0."fact_utc_date") AS "Prev" -
last_valuealso gets an explicit whole-partition frame. AnORDER BYwith no frame means “up to this row” in standard SQL, solast_valuewould otherwise answer with the row it is sitting on:last_value(count(*)) OVER ( ORDER BY count(*) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) -
ntileemits one argument.NTILE(4), notNTILE(x, 4)— the two-argument form is rejected by every engine.
A moving average of 14 renders as avg(sum(x)) OVER (ORDER BY <date> ROWS BETWEEN 14 PRECEDING AND CURRENT ROW). The lag example, with month and category projected:
{"spec": {"projections": [
{"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]},
{"field": "category"},
{"field": "ws_net_paid", "decorators": [
{"type": "window", "mode": "running", "function": "lag", "offset": 2,
"partition_refs": ["date"], "order_by": {"category": "desc"}}]}
], "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]}}
SELECT
DATE_TRUNC('month', T1."d_date")::DATE AS "Month(Date)",
T2."i_category" AS "Category",
lag(sum(T0."ws_net_paid"), 2) OVER (PARTITION BY DATE_TRUNC('month', T1."d_date")::DATE ORDER BY T2."i_category" desc) AS "Running Lag(Web Net Paid)"
FROM
web_sales T0
JOIN date_dim T1
ON T0.ws_sold_date_sk = T1.d_date_sk
JOIN item T2
ON T0.ws_item_sk = T2.i_item_sk
WHERE
LOWER(T2."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
DATE_TRUNC('month', T1."d_date")::DATE,
T2."i_category"
The window sits over the aggregate in the same SELECT: partition_refs: ["date"] becomes PARTITION BY the truncated date, the order_by map becomes ORDER BY ... desc.
customize
Wrap a dimension’s expression in your own SQL.
{"type": "customize", "custom_sql": "upper(@expression)"}
custom_sql is required, must contain @expression (replaced by the dimension’s column expression) and must not contain avg(, sum(, min(, max( or count(. The derived alias is Custom(Category). Dimensions only. There is no shorthand function for it.
Attribute reference
Every decorator starts from one attribute row; a kind reads the attributes it needs and the rest keep their defaults.
| attribute | default | read by |
|---|---|---|
grain | null | truncate |
extract | null | extract |
mode, function, size, offset, buckets | null, null, 7, 0, 4 | window |
order_by | {"-1": "asc"} for window | window |
partition_refs | null | window, contribute |
ignore_partition_filters | true | window, contribute |
transform, percent_change | null | temporalize |
abbreviate | true | carried |
custom_sql | null | customize |
Keys named id, projection_id, prompt, share_full_result_set_with_ai, type, created_at, updated_at, created_by_id, updated_by_id are accepted and ignored.
Next steps
- Filters
- Calculations for anything a decorator cannot express
- The expression language for the function names behind each decorator