Measures and Aggregations¶

result = engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": ["*:count", "order_total:sum", "order_total:avg", "order_total:min", "order_total:max"],
    }
)

row = result.data[0]
print(f"Orders:  {row['orders._count']:,}")
print(f"Total:   ${row['orders.order_total_sum']:,.2f}")
print(f"Average: ${row['orders.order_total_avg']:.2f}")
print(f"Min:     ${row['orders.order_total_min']:.2f}")
print(f"Max:     ${row['orders.order_total_max']:.2f}")

result = engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": ["*:count", "order_total:sum", "order_total:avg"],
        "dimensions": ["stores.name"],
        "order": [{"column": "order_total_sum", "direction": "desc"}],
        "limit": 5,
    }
)

print(f"{'Store':<20} {'Orders':>8} {'Total':>14} {'Average':>10}")
print("-" * 54)
for row in result.data:
    print(
        f"{row['orders.stores.name']:<20} {row['orders._count']:>8,} ${row['orders.order_total_sum']:>13,.2f} ${row['orders.order_total_avg']:>9.2f}"
    )

result = engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": [
            "*:count",
            "order_total:sum",
            {"formula": "order_total:sum / *:count", "name": "aov", "label": "Avg Order Value"},
        ],
        "dimensions": ["stores.name"],
        "order": [{"column": "order_total_sum", "direction": "desc"}],
        "limit": 5,
    }
)

for row in result.data:
    print(f"{row['orders.stores.name']}: AOV = ${row['orders.aov']:.2f}")

result = engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": [
            "order_total:sum",
            {"formula": "cumsum(order_total:sum)", "name": "running_total"},
            {"formula": "change(order_total:sum)", "name": "mom_change"},
        ],
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}],
        "order": [{"column": "ordered_at", "direction": "asc"}],
        "limit": 6,
    }
)

print(f"{'Month':<12} {'Revenue':>14} {'Running Total':>14} {'MoM Change':>12}")
print("-" * 54)
for row in result.data:
    month = str(row["orders.ordered_at"])[:7]
    rev = row["orders.order_total_sum"]
    running = row["orders.running_total"]
    change = row["orders.mom_change"]
    change_str = f"${change:>11,.2f}" if change is not None else f"{'N/A':>12}"
    print(f"{month:<12} ${rev:>13,.2f} ${running:>13,.2f} {change_str}")

result = engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": [
            "*:count",
            "order_total:sum",
            "customers.*:count",
        ],
        "time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}],
        "order": [{"column": "ordered_at", "direction": "asc"}],
        "limit": 6,
    }
)

print(f"{'Month':<12} {'Orders':>8} {'Revenue':>14} {'Customers':>10}")
print("-" * 46)
for row in result.data:
    month = str(row["orders.ordered_at"])[:7]
    orders = row["orders._count"]
    rev = row["orders.order_total_sum"]
    custs = row["orders.customers._count"]
    print(f"{month:<12} {orders:>8,} ${rev:>13,.2f} {custs:>10,}")

# In v2 every column carries its data type and (optionally) an explicit
# `allowed_aggregations` whitelist. There's no separate row-level measures
# list — every column can be aggregated at query time via colon syntax.
orders_model = next(m for m in models if m.name == "orders")

print(f"orders_model has {len(orders_model.columns)} columns:")
for col in orders_model.columns:
    sql_str = col.sql or col.name
    print(f"  {col.name:<20} sql: {sql_str:<20} type: {col.type}")

print("\nAny column can be aggregated at query time via colon syntax,")
print("subject to the column's type and allowed_aggregations whitelist.")
print("Primary keys default to count / count_distinct only.")
print("Plus *:count is always available for COUNT(*).")

# Define a custom aggregation alongside built-ins. v2 unifies row-level
# definitions into Column; the custom Aggregation lives on the model.
#
# Each store has a `tax_rate`, but stores differ in size — a simple AVG of
# tax_rate would give every store equal weight. We want the *effective* tax
# rate paid across the business, weighted by order revenue. We define this on
# a custom orders model so both `tax_rate` (via the join to stores) and the
# weight `subtotal` are available row-level.
from slayer.core.enums import DataType
from slayer.core.models import Aggregation, AggregationParam, Column, ModelJoin, SlayerModel

orders_custom = SlayerModel(
    name="orders_custom",
    sql_table="orders",
    data_source="jaffle_shop",
    columns=[
        Column(name="id", sql="id", type=DataType.TEXT, primary_key=True),
        Column(name="store_id", sql="store_id", type=DataType.TEXT),
        Column(name="subtotal", sql="subtotal", type=DataType.DOUBLE),
        Column(name="store_tax_rate", sql="stores.tax_rate", type=DataType.DOUBLE),
    ],
    joins=[ModelJoin(target_model="stores", join_pairs=[["store_id", "id"]])],
    aggregations=[
        Aggregation(
            name="weighted_avg_tax",
            formula="SUM({value} * {weight}) / NULLIF(SUM({weight}), 0)",
            params=[AggregationParam(name="weight", sql="subtotal")],
            description="Tax rate weighted by order subtotal (effective tax rate)",
        ),
    ],
)
print(f"Custom aggregations on orders_custom: {[a.name for a in orders_custom.aggregations]}")

# Restrict per-column aggregation eligibility via `allowed_aggregations`. The
# whitelist overrides the type-default eligibility rule (numeric columns get
# sum/avg/min/max/etc by default; PK columns are always count-only).
from slayer.core.enums import DataType
from slayer.core.models import Column, SlayerModel

restricted_model = SlayerModel(
    name="restricted_orders",
    sql_table="orders",
    data_source="jaffle_shop",
    columns=[
        Column(name="store_id", sql="store_id", type=DataType.TEXT),
        Column(
            name="subtotal", sql="subtotal", type=DataType.DOUBLE,
            allowed_aggregations=["sum", "avg"],
        ),
    ],
)
sub_col = next(c for c in restricted_model.columns if c.name == "subtotal")
print(f"subtotal column allows: {sub_col.allowed_aggregations}")

result = engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": ["*:count", "order_total:sum", "order_total:avg"],
        "dimensions": ["stores.name"],
        "limit": 3,
    }
)

print("Generated SQL:")
print(result.sql)