Level of detail

Some measures must be computed at a grain other than the one the query groups by: a grand total repeated on every row, a total that ignores the category filter the caller applied, or a median of per-ticket sums. 0sql models these as exclusion and inclusion measures in YAML. At request time they are plain fields; the planner notices the rule and adds the extra aggregation. This page shows the model shapes, a spec that uses them, the shape of the statement, and when the request-time contribute decorator is the better tool. Model reference: Exclusions and Inclusions.

Exclusion measures in the model

An exclusion rule removes dimensions from a measure’s grouping and says what to do with filters on them. The all-categories denominator from the Exclusions page, written for TPC-DS web sales:

  - type: measure
    name: Web Net Paid
    data_type: decimal
    expression:
      sql: sum(ws_net_paid)

  - type: measure
    name: Web Net Paid (All Categories)
    data_type: decimal
    exclusion_type: exclude
    exclusions:
      - type: dimension
        filter: ignore          # a filter on Category does not apply to this measure
        entities:
          - Category
    expression:
      sql: sum(ws_net_paid)

Two independent knobs: exclusion_type (exclude, exclude_all_except, exclude_all) controls which dimensions may group the measure; filter (apply, ignore, only) controls what filters on those dimensions do. The running TPC-DS project carries this one, which drops every dimension of the Date table and ignores date filters:

  - type: measure
    name: Store Level Total Sales
    description: Exclude date_dim dimensions and ignore filter
    data_type: decimal
    exclusion_type: exclude
    exclusions:
      - type: table
        filter: ignore
        entities: [Date]
    expression:
      sql: sum(ss_net_paid)

Inclusion measures in the model

An inclusion rule adds dimensions for an inner aggregation and rolls the result up with a second aggregate. From the running project:

  - type: measure
    name: Median Store Order Size
    description: This is the median order total per sale
    data_type: decimal
    inclusions:
      filter: apply
      aggregation: percentile_cont(0.5) WITHIN GROUP (ORDER BY @exp)
      dimensions: [Store Ticket number]
    expression:
      sql: sum(ss_net_paid)

Inner: sum(ss_net_paid) grouped by the query’s dimensions plus Store Ticket number. Outer: the median of those per-ticket sums, grouped by the query’s dimensions only. @exp stands for the inner result.

The request

The exclusion measure beside the plain measure, plus a calculation that divides them.

{"spec": {
  "projections": [
    {"field": "category", "alias": "Category"},
    {"field": "Web Net Paid", "alias": "Web Net Paid"},
    {"field": "Web Net Paid (All Categories)"},
    {"alias": "Share", "calculation": true, "data_type": "decimal",
     "sql": "[Web Net Paid]@m / nullif([Web Net Paid (All Categories)]@m, 0)"}
  ],
  "filters": [{"field": "category", "predicate": "in_list", "value": "men, children"}]
}}

zsql sql --expr "category, [Web Net Paid], [Web Net Paid (All Categories)], Share = [Web Net Paid]@m / nullif([Web Net Paid (All Categories)]@m, 0), category in (men, children)"

Nothing in the spec says “exclusion”. The field name is enough; the rule travels with the measure. Field names with parentheses are fine in brackets.

The shape of the SQL

We have no captured statement to quote for this exact request, so here is the shape rather than the text. The planner runs an exclusion phase (you can see it in POST …/explain under phases) that forks the exclusion measure into its own aggregation: one CTE sums ws_net_paid by i_category with the category WHERE, a second CTE sums ws_net_paid with no GROUP BY on category and, because filter: ignore, no category WHERE. The final SELECT joins the coarser aggregation back to the per-category rows (a single total row joins every row, as the CROSS JOIN does on the contribute page), projects both columns, and computes Share from the two aggregated columns at the merge, exactly where the Bucket calculation sits on Complex measures. Every category row carries the same denominator, so Share is each category’s fraction of the all-category total even though the query is filtered to two categories.

For the inclusion measure the shape is nested instead of parallel: an inner aggregation grouped by the query’s dimensions plus Store Ticket number, wrapped by an outer SELECT that applies percentile_cont(0.5) WITHIN GROUP (ORDER BY …) grouped by the query’s dimensions. Request it with {"field": "Median Store Order Size"} next to Item Category and it joins the result like any other measure column.

Exclusion measure or contribute decorator

Both give a percent of total. They differ in who decides and in what the denominator respects.

Rule of thumb: if the denominator needs an opinion about filters, tables or universes, model it. If the caller just wants each row divided by the sum of the rows it can see, decorate it. See Share and windows for the decorator’s SQL.

Variations

• Grand total on every row. exclusion_type: exclude_all with no exclusions list gives a measure computed as one value regardless of the query’s dimensions; a query for category, Web Net Paid, Grand Total repeats the total on each row.
• Respect the filter, drop the grouping. filter: apply instead of ignore on Web Net Paid (All Categories) keeps the category WHERE in the coarser CTE, so the denominator is the total of the two filtered categories and the shares sum to 100%.
• Median by month. Project {"field": "date", "decorators": [{"type": "truncate", "grain": "month"}]} with Median Store Order Size; the inner aggregation groups by month and ticket, the outer by month.

Next steps

• Exclusions and Inclusions
• Share and windows
• Complex measures