Downloads 492this week

GitHub stars 11

Extension repository on GitHub

Extension descriptor (YAML)

Measure-aware SQL implementing Julian Hyde's 'Measures in SQL' paper

Installing and Loading

INSTALL yardstick FROM community;
LOAD yardstick;

Example

-- Create a view with measures
CREATE VIEW sales_v AS
SELECT
    year,
    region,
    SUM(amount) AS MEASURE revenue,
    COUNT(*) AS MEASURE order_count
FROM sales
GROUP BY year, region;

-- Query with AGGREGATE() and AT modifiers
SEMANTIC SELECT
    year,
    region,
    AGGREGATE(revenue) AS revenue,
    AGGREGATE(revenue) AT (ALL region) AS year_total,
    AGGREGATE(revenue) / AGGREGATE(revenue) AT (ALL region) AS pct_of_year
FROM sales_v
GROUP BY year, region;

About yardstick

yardstick implements Julian Hyde's "Measures in SQL" paper (arXiv:2406.00251), adding measure-aware SQL to DuckDB.

Measures are aggregations that know how to re-aggregate themselves when the query context changes. This enables:

Percent of total calculations without CTEs or window functions:

SEMANTIC SELECT region, AGGREGATE(revenue) / AGGREGATE(revenue) AT (ALL) AS pct
FROM sales_v GROUP BY region;

Year-over-year comparisons with simple syntax:

SEMANTIC SELECT year, AGGREGATE(revenue) AT (SET year = year - 1) AS prior_year
FROM sales_v GROUP BY year;

AT Modifiers:

• AT (ALL) - Grand total across all dimensions
• AT (ALL dim) - Total excluding specific dimension
• AT (SET dim = val) - Fix dimension to specific value
• AT (SET dim = expr) - Fix dimension to expression
• AT (WHERE cond) - Pre-aggregation filter
• AT (VISIBLE) - Use query's WHERE clause

For more details, visit the extension repository.

Added Functions