Share and windows

Two families of measure decorators reshape an aggregated number against its neighbours. contribute divides each row’s measure by a total computed in a separate CTE, which gives percent of total. window wraps the aggregate in a SQL window function (sum, avg, lag, rank, …) over the query’s own rows, for running totals, moving averages and offsets. Both apply to measures only. Reference: Projections and decorators.

Percent of total

Each category’s share of web revenue.

{"spec": {
  "projections": [
    {"field": "ws_net_paid", "decorators": [{"type": "contribute"}]},
    {"field": "category"}
  ],
  "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]
}}
zsql sql --expr "pct(web net paid), category, category in (men, children)"
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 per-row aggregation is the first CTE. ag6149… is web revenue by category, with the category filter.
  • The total is a second CTE with no GROUP BY. agbe95… sums the whole fact. It carries no WHERE: ignore_partition_filters defaults to true, so filters on the dimensions that define the share (here Category, the only projected dimension) are left out of the denominator. The share is relative to all categories, and the three visible rows do not sum to 100%. Set "ignore_partition_filters": false on the decorator to make them.
  • CROSS JOIN, then divide. One total row is cross-joined to every category row and the final SELECT computes msr / (total*1.00). The *1.00 forces decimal division.
  • The alias is derived. % Web Net Paid of Total, unless you set alias.

Share within a category

Project category and product_name, and tell contribute which projected dimensions partition the total.

{"spec": {
  "projections": [
    {"field": "category"},
    {"field": "product_name"},
    {"field": "ws_net_paid", "alias": "Share of Category", "decorators": [{"type": "contribute", "partition_refs": ["category"]}]}
  ]
}}
zsql sql --expr "category, product name, pct(web net paid, category) as Share of Category"

No captured statement to quote for this one. The shape follows from the previous statement: the total CTE now groups by i_category, and the final SELECT joins it on the category instead of cross-joining a single row, so each product is divided by its own category’s total. partition_refs must name dimensions that are projected; a ref that is not projected is dropped.

Running sum and moving average

{"spec": {
  "projections": [
    {"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]},
    {"field": "ws_net_paid", "decorators": [{"type": "window", "mode": "running", "function": "sum"}]},
    {"field": "ws_net_paid", "decorators": [{"type": "window", "mode": "moving", "function": "avg", "size": 7}]}
  ]
}}
zsql sql --expr "month(date), running_sum(web net paid), moving_avg(web net paid, 7)"

The ordering defaults to {"-1": "asc"}, meaning the first projected date, ascending; partition_refs is empty, so the window runs over the whole result. The window function wraps the aggregate, as in the lag statement below: a moving average of size 14 renders in the engine’s tests as avg(sum(x)) OVER ( ORDER BY <date> ROWS BETWEEN 14 PRECEDING AND CURRENT ROW), and a running sum is the same pattern with sum(sum(x)) and no ROWS frame. There is no separate CTE because the window reads the same grouped rows. Aliases: Running Sum(Web Net Paid) and Moving Avg(Web Net Paid).

Lag with an explicit partition and order

Two categories back within each month, categories ordered descending.

{"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"

partition_refs and order_by are maps over projected dimension uids: date partitions, category desc orders. The offset becomes the second argument of lag. The shorthand running_lag(web net paid, date) sets the partition but has no syntax for offset or order_by; use JSON when you need them.

Variations

  • Rank within a partition. {"type": "window", "mode": "running", "function": "rank", "partition_refs": ["date"], "order_by": {"category": "desc"}}; dense_rank, row_number, percent_rank, cume_dist and ntile (with "buckets": 4) work the same way. order_by keys are projected dimension uids; a key that is not a projected dimension is dropped.
  • Running sum per category. zsql sql --expr "month(date), category, running_sum(web net paid, category)" adds partition_refs: ["category"], so each category’s total restarts.
  • Share of a total from the model. When the denominator must survive filters your callers add, define it as an exclusion measure in YAML instead of using contribute; see Level of detail.

Next steps