Complex measures

A calculation is a SQL formula over fields and other projections, written in the spec and planned with the query. References go through brackets: [Name]@m is a measure, [Name]@d a dimension, [Alias] another projection or calculation in the same request. Raw column names are refused. This page builds up from a plain ratio to a windowed calculation that references another calculation, shows the SQL the planner returns for two of them, and then contrasts request-side calculations with compound measures defined in YAML. The formula grammar is on Calculations.

Seven formulas

Each entry is a complete projection you can drop into projections (or into the top-level calculations list, where calculation: true is implied). data_type defaults to decimal.

  1. Ratio of two measures from different facts. Each side is aggregated in its own CTE; the division happens in the final SELECT.

    {"alias": "2x WPaid", "sql": "[Net Paid]@m/[Web Net Paid]@m", "data_type": "decimal", "calculation": true}

  2. Aggregated ratio. A formula that calls an aggregate function (sum, max, count, …) is a measure calculation and re-aggregates its inputs.

    {"alias": "Agg Ratio", "sql": "sum([Web Net Paid]@m)/max([Net Paid]@m)", "calculation": true}

  3. Conditional distinct count. CASE is the conditional; there is no if().

    {"alias": "Actives", "data_type": "integer", "sql": "count(distinct case when [Net Paid]@m > 0 or [Web Net Paid]@m > 0 then [Category]@d else null end)", "calculation": true}

  4. A calculation referencing a calculation, with a window. [Actives] is the alias of entry 3 (or of the simpler count(distinct [Category]@d) used in the statement below).

    {"alias": "Retention Rate", "sql": "[Actives] / nullif(first_value([Actives]) over (order by [Category]@d), 0)", "calculation": true}

  5. String dimension calculation. No aggregate in the formula, so it groups like a dimension.

    {"alias": "Cat Concat Item", "data_type": "string", "sql": "[Category] || ' ' || [Product Name]@d", "calculation": true}

  6. Date part. Also a dimension calculation.

    {"alias": "Year", "data_type": "integer", "sql": "EXTRACT(year FROM [Date]@d)", "calculation": true}

  7. Margin percent. Guard the denominator with nullif.

    {"alias": "Margin %", "calculation": true, "sql": "([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)", "data_type": "decimal"}

In the shorthand a calculation is Alias = formula or Alias := formula:

zsql sql --expr "category, net paid, Bucket = [Net Paid] / 100, web net paid, category in (men, children)"
zsql sql --expr "Margin % := ([Net Paid]@m - [Cost]@m) / nullif([Net Paid]@m, 0)"

A calculation on a blended measure

The request is the cross-fact blend plus a calculation over the store measure’s alias.

{"spec": {
  "projections": [
    {"field": "category", "alias": "Category"},
    {"field": "net_paid", "alias": "Net Paid"},
    {"alias": "Bucket", "calculation": true, "sql": "[Net Paid] / 100", "data_type": "decimal"},
    {"field": "ws_net_paid", "alias": "Web Net Paid"}
  ],
  "filters": [{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}]
}}
WITH agc51c5a013f468165df0d33fc12150511 AS (
SELECT
	T1."i_category" AS "dim30d09b7",
	sum(T0."ss_net_paid") AS "msr8a51bb0"
FROM
	store_sales T0
	JOIN item T1
		ON T0.ss_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	T1."i_category"
), ag89dc4ad5879fa4552696fe3de5b10435 AS (
SELECT
	T1."i_category" AS "dim30d09b7",
	sum(T0."ws_net_paid") AS "msr60b3792"
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"
)
SELECT
	COALESCE(A1.dim30d09b7, A0.dim30d09b7) AS "Category",
	A1.msr8a51bb0 AS "Net Paid",
	A1.msr8a51bb0 / 100 AS "Bucket",
	A0.msr60b3792 AS "Web Net Paid"
FROM
	ag89dc4ad5879fa4552696fe3de5b10435 A0
	FULL OUTER JOIN agc51c5a013f468165df0d33fc12150511 A1
		ON A1.dim30d09b7 = A0.dim30d09b7

The calculation stays at the merge: A1.msr8a51bb0 / 100 is computed in the final SELECT from the store CTE’s already aggregated column. Neither aggregation CTE knows the calculation exists. A ratio across the two facts (entry 1) lands in the same place, as A1.msr… / A0.msr….

A calculation over a calculation, with a window

Projections: category, Actives = count(distinct [Category]@d) (integer) and Retention Rate = [Actives] / nullif(first_value([Actives]) over (order by [Category]@d), 0).

{"spec": {
  "projections": [
    {"field": "category", "alias": "Category"},
    {"alias": "Actives", "data_type": "integer", "sql": "count(distinct [Category]@d)", "calculation": true},
    {"alias": "Retention Rate", "sql": "[Actives] / nullif(first_value([Actives]) over (order by [Category]@d), 0)", "calculation": true}
  ]
}}
SELECT
	A0.dim30d09b7 AS "Category",
	count(distinct A0.dim30d09b7) AS "Actives",
	count(distinct A0.dim30d09b7) / nullif(first_value(count(distinct A0.dim30d09b7)) over (order by A0.dim30d09b7), 0) AS "Retention Rate"
FROM
	(
SELECT
	T0."i_category" AS "dim30d09b7"
FROM
	web_sales_sum T0
GROUP BY
	T0."i_category"
) A0
GROUP BY
	A0.dim30d09b7

Two things to read off this statement. The planner resolved Category through a table named web_sales_sum in the test model, grouped it in a subquery, and applied the aggregating calculation in the outer SELECT with its own GROUP BY, so a measure calculation never aggregates raw rows twice. And [Actives] is expanded in place: the window function wraps the same count(distinct …) expression, and the window clause stays out of the GROUP BY.

Model-side compound measures versus request-side calculations

The same bracket grammar works in a measure’s expression in the model. These two are verbatim from the TPC-DS project’s tbl.store_sales.yml:

  - type: measure
    name: Sales Throughput
    description: Rate at which inventory is turned over
    data_type: decimal
    expression: "[Store Quantity] / [Inventory Quantity On Hand] * 1.0"

  - type: measure
    name: Store Net Including Returns
    description: Net amount paid for sales minus return amount
    data_type: decimal
    expression: "[Store Net Paid] - [Store Return Amount]"

A measure made only of bracket references needs no aggregate function; the referenced measures carry their own. In a model expression write [Name]@m and [Name]@d when you want to be explicit about the kind. Once deployed, a compound measure is requested like any other field: {"field": "Store Net Including Returns"}. See Compound measures.

Put a formula in the model when:

  • more than one caller should get the same number (margin, net of returns, throughput);
  • it should be discoverable through GET …/fields and the explore endpoint;
  • a security policy should trigger on it (policies fire on projected fields, never on calculations);
  • it should get a format, description and synonyms.

Put a formula in the request when:

  • it references another projection by [Alias], or a window over the query’s own ordering (entry 4), which only exist at request time;
  • it is specific to one screen or one API call;
  • it is a dimension calculation that shapes the grouping of this query only (entries 5 and 6).

Variations

  • Ratio across facts. Replace the Bucket projection with entry 1. The two aggregation CTEs are unchanged; the final SELECT divides the store column by the web column.
  • Share of a total. Share = sum([Web Net Paid]@m) / sum([Net Paid]@m) as the only projection beside category gives one ratio per category. For percent of the grand total use the contribute decorator instead; see Share and windows.
  • Order by the calculation. Add "order_by": "desc" to the calculation projection. There is no top-level sort key.

Next steps