Downloads 358this week

GitHub stars 47

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;

-- 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;

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;

Year-over-year comparisons with simple syntax:

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

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

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

This extension does not add any settings.